Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: 10053 trace for sql fired from pl/sql (stored code)

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

From: <>
Date: 2005-12-27 21:17:25
Message-id: 04DDF147ED3A0D42B48A48A18D574C4503D3FF86@NT15.oneneck.corp

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

-----Original Message-----
From: Allen, Brandon
Sent: Tuesday, December 27, 2005 10:28 AM To: ''; Cc:; 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.


-----Original Message-----
[]On Behalf Of Wolfgang Breitling Sent: Thursday, December 22, 2005 3:43 PM To:
Cc:; 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 and 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


Wolfgang Breitling
Centrex Consulting Corporation

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.

Received on Tue Dec 27 2005 - 21:17:25 CST

Original text of this message