RE: 10053 trace for sql fired from pl/sql (stored code)

I tried setting a 10053 trace for another session via oradebug and it worked fine for sql \ statements, but not pl/sql. (Oracle 9.2.0.6 on HPUX)

-----Original Message-----
From: Allen, Brandon
Sent: Tuesday, December 27, 2005 10:28 AM To: 'breitliw_at_centrexcc.com'; cichomitiko_at_gmail.com Cc: oracle-l_at_freelists.org; Boris Dali
Subject: RE: 10053 trace for sql fired from pl/sql (stored code)

Is there a way to set a 10053 trace instance wide (e.g. in the init.ora) , or for another \ user's session, like you can for the 10046 trace (e.g. with oradebug, dbms_support or \ dbms_system)? If so, that might be a good work around since you can't set it from within \ the pl/sql.

Regards,
Brandon

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling Sent: Thursday, December 22, 2005 3:43 PM To: cichomitiko_at_gmail.com
Cc: oracle-l_at_freelists.org; Boris Dali
Subject: Re: 10053 trace for sql fired from pl/sql (stored code)

Dimitre and all,

now that you mention it, I recall that I had problems getting an 10053 trace out of a (in my case anonymous) PL/SQL block. I'll have to look how I eventually got what I was looking for (the 10053 trace). It is quite possible that I went the path of least resistance and used perl. In my case the tool didn't matter since all I was after was the 10053 trace and a way to run the - more or less same - sql with slightly changed parameters.

Radoulov, Dimitre wrote:
> > It works with 9i.
>
>
> I tested it on 9.2.0.4 and 9.2.0.7 on Solaris. The execute immediate
> 'alter session set events ''10053 trace name context forever, level 1'''
> within a plsql block doesn't seem to provide the expected
> information. The same code works with event 10046.
>
> You could try to trace with 10046 to get the sql and the values of the
> bind variables and then trace with 10053 in sqlplus, but in this way you
> could miss an important session environment information (if the
> application sets some parameters before calling the plsql code for example).
>
> So you can create a logon trigger that executes "alter session set
> events '10046 ... " for that particular user to see the entire
> application code.
>
>
>
> Regards,
> Dimitre Radoulov
>
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l



Privileged/Confidential Information may be contained in this message or attachments \ 
hereto. Please advise immediately if you or your employer do not consent to Internet email \ 
for messages of this kind. Opinions, conclusions and other information in this message \ 
that do not relate to the official business of this company shall be understood as neither \ 
given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l