CODEWITHSUNDEEP
oracle-database
Moudiz
Moudiz

Reputation: 7377

A way to log sql text when inserted

I Have the below trigger, the job of this trigger is to track the SQL Query of a developer when inserting on tab1, lets say if the dev executed this query :

update tab1 set col1 = 1;

the triger should insert that query in the TAB_LOGS.
however I am facing an error 'Exact fetch returns more than requested number of rows' on this block 

select a.SQL_TEXT into V_SQL    
from   v$session s
join   v$sqlarea a on ( a.ADDRESS = s.SQL_ADDRESS )
where  s.OSUSER = V_USERNAME;

this is the trigger

create or replace trigger TRG_test
after INSERT or update or delete
   ON tab1
   FOR EACH ROW

DECLARE
V_USERNAME VARCHAR2(100);
V_SQL      varchar2(4000);
begin

SELECT SYS_CONTEXT('USERENV','OS_USER') into V_USERNAME  FROM dual;

select a.SQL_TEXT into V_SQL    
from   v$session s
join   v$sqlarea a on ( a.ADDRESS = s.SQL_ADDRESS )
where  s.OSUSER = V_USERNAME;

insert into tab_logs (V_USERNAME,V_SQL);
end;
/

Upvotes: 0

Views: 677

Answers (1)

gpeche
gpeche

Reputation: 22504

where  s.OSUSER = V_USERNAME;

That would give you every SQL for every session with osuser V_USERNAME. There might be more than one such session (in fact, it is pretty common). What you actually want is to audit the SQL issued by the current session.

select a.SQL_TEXT into V_SQL    
from   v$session s
join   v$sqlarea a on ( a.ADDRESS = s.SQL_ADDRESS )
where  s.AUDSID = userenv('SESSIONID');

Upvotes: 1

Related Questions