How to create a trigger to store all the queries run on the database into a table?

Question

I am using the below query to achieve this. On executing the query for creating a trigger its working fine, but its throwing an error when I insert something into the table on which the trigger is created.

create or replace trigger TESTTRIG
    after insert
        or update
        or delete
    on sourcetable
    declare
        v_text varchar2(1000);
        v_mask varchar2(100);
        cursor c1
          is
            select  sql_text
              from  v$open_cursor
              where sid = sys_context('USERENV','SID')
              order by last_sql_active_time desc;
    begin
        v_mask := case
                    when inserting then 'INSERT +INTO +<TABLE_NAME>( |\()'
                    when updating  then 'UPDATE +<TABLE_NAME> '
                    else 'DELETE( +FROM)? +<TABLE_NAME>( |$)'
                  end;
        open c1;
        loop
          fetch c1
            into v_text;
          exit when c1%notfound;
          if regexp_like(v_text,v_mask,'i')
            then
              insert into targettable('TIME_EXEC','SQL_TEXT') values (SYSDATE,v_text);
              exit;
          end if;
        end loop;
        close c1;
    end TESTTRIG;
/.

I am getting a ORA-04098 on inserting anything into the "sourcetable" table.Please help.

the ORA message is clear: the trigger can't be compiled/is invalid. Fix the trigger and the message goes away. — jwenting
can you please tell me whats the error in the trigger. I am a complete newbie to triggers and would very much appreciate any help you can provide. — Sai Krishna

StephaneM StephaneM · Accepted Answer · 2015-06-23T13:26:38

Why reinventing the wheel? Oracle has a built-in functionality that does exactly what you want: AUDIT.

Use the AUDIT statement to:

Track operations on a specific schema object. Auditing operations on schema objects apply to current sessions as well as to subsequent sessions.

See there: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4007.htm#SQLRF01107 at the bottom of the page.

How to create a trigger to store all the queries run on the database into a table?

1 Answers