before select trigger

The whole idea to create a 'before select trigger' is based on a comment Martin Jensen dropped during his Seminar about Advanced Materialized Views in Vienna this week. So this all is not my idea, I just wanted to test the suggestion to get used to it.
The whole functionality utilizes FGAC.
As some others have also noticed, the function is called twice per every execution, so I have to reduce the effective execution of my action.

So let's start with - a log_table:

create table log_table
(datum date,
c varchar2(80)) ;

Now something which can write into this table.

The package is necessary to hold my package variable tot_calls and eliminate every 2nd execution of the function (otherwise it would fire twice, not recommended in most cases, I guess).

The autonomous_transaction might be necessary in most cases, but if someone find business-cases where it's recommended not to be autonomous, just drop this line and have the trigger under full transaction control.

CREATE OR REPLACE PACKAGE my_select_package AS
FUNCTION my_select_trigger (p_schema in varchar2,
                      p_object in varchar2 ) return varchar2;
END my_select_package;
/

CREATE OR REPLACE PACKAGE BODY my_select_package AS
tot_calls NUMBER := 0;
function my_select_trigger( p_schema in varchar2,
                                p_object in varchar2 ) return varchar2
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
tot_calls := tot_calls + 1;
if mod(tot_calls, 2) > 0
then
insert into log_table values (sysdate, 'function was here: ' ||p_schema ||'.' || p_object || ' tot_calls: ' ||tot_calls);
commit;
end if;
return NULL;
end;
END my_select_package;
/

What are we still missing? A table to connect the trigger to, with some data:

create table my_table
(  data        varchar2(30)
);

insert into my_table ( data ) values ( 'Some Data' );

insert into my_table ( data ) values ( 'Some other Data' );

commit;

Now let's tight these pieces together:

begin
dbms_rls.add_policy
( object_schema   => 'SR3',
object_name     => 'MY_TABLE',
policy_name     => 'MY_SELECT_TRIGGER',
function_schema => 'SR3',
policy_function => 'MY_SELECT_PACKAGE.MY_SELECT_TRIGGER',
statement_types => 'select' ,
update_check    => FALSE );
end;
/

and some trivial tests:

select * from my_table;

DATA                           OWNER
------------------------------ ------------------------------
Some Data                      SR3
Some Data Owned by SCOTT       SCOTT

select * from log_table;

DATUM               C
------------------- --------------------------------------------------
2009-05-18 13:19:09 function was here: SR3.MY_TABLE tot_calls: 1

select * from my_table;
-- ...

select * from log_table;

DATUM               C
------------------- --------------------------------------------------
2009-05-18 13:19:09 function was here: SR3.MY_TABLE tot_calls: 1
2009-05-18 13:19:15 function was here: SR3.MY_TABLE tot_calls: 3

works like a charm.

Anything more to say?
Of course, as allways, this is only a proove of concept.
As allways, most parts are borrowed from several sites.
Don't use this blindly anywhere. I totally left everything out, which could confuse me. Like exception handling, hard testing, theoretical and practical security checks, performance baselines, etc.
...
and at the end, the most global citation from Martin Jensen: 'If it's not tested, it does not work'