question about sql_trace on connection pooling application

[jlakhani]

I have an application which connects from 3 applications servers. I wanted to enable sql tracing on a user connecting from machine 'bart' as user "admin". I have written a snippet of this trigger but it's not working. When I create the following trigger on the db and attempt to connect as user1 from machine 'bart' i get an error message "data not found on line 4. What am I doing wrong?

thanks


CREATE OR REPLACE TRIGGER LOG_TRIGGER2
AFTER LOGON ON DATABASE
DECLARE
progname varchar2(100);
BEGIN
select terminal into progname from v$session where terminal = 'bart' and username = 'admin';
if (progname = 'bart') then
execute immediate 'alter session set sql_trace = true';
end if;
END;

[tomcat]

Your select... into is expecting one and only one value to be returned into your variable. It's getting no values except if you happen to be the one special user. Try:

select terminal into progname from v$session where audsid=userenv('sessionid');

if you want to trace sessions, look at dbms_monitor procs, may provide more flexibility and can grab wait/bind data

[jlakhani]

Your select... into is expecting one and only one value to be returned into your variable. It's getting no values except if you happen to be the one special user. Try:

select terminal into progname from v$session where audsid=userenv('sessionid');

if you want to trace sessions, look at dbms_monitor procs, may provide more flexibility and can grab wait/bind data

tomcat,

Can you modify my trigger and give me an example based on my trigger?

cheers

[tomcat]

Can you modify my trigger and give me an example based on my trigger?

No, but you can... just call dbms_monitor instead of the alter session. I'm assuming you are on 10g

dbms_monitor.session_trace_enable(sid, serial, waits, binds)

if you pass nulls for the 1st two parms - then it's your current session that gets traced - so (null,null,true,true) will get you current session with waits and bind info.

I could be wrong, but I believe the plain old alter session set sql_trace=true doesn't set those...