Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to trace what is happening inside the stored procedure

Re: How to trace what is happening inside the stored procedure

From: Tim Gorman <>
Date: Sat, 05 Feb 2005 11:51:13 -0700
Message-ID: <>

That is exactly what the DBMS_APPLICATION_INFO package is intended for.

The SET_MODULE procedure permits you to set the value of the MODULE and ACTION columns within the V$SESSION view with any string value. The SET_ACTION procedure changes the ACTION column only, leaving the MODULE.

The SET_CLIENT_INFO procedure is also available, but many applications (i.e. Oracle E-Biz) tend to use that, so be careful. Of course, the SET_MODULE, SET_ACTION, and SET_CLIENT_INFO procedures all come with corresponding READ_MODULE, READ_ACTION, and READ_CLIENT_INFO procedures, to see what is currently in those columns.

Conventionally, the MODULE column should have the name of the program module (i.e. package or procedure/function name) while the ACTION column should have something descriptive to indicate a "step" within the module.

Typically, I tie the use of DBMS_APPLICATION_INFO in with my error handling. I populate a string variable called "v_errcontext" before any operation that is likely to throw an exception, such as a SQL statement, a type conversion, etc. If it seems appropriate, I'll use the contents of "v_errcontext" in the SET_ACTION procedure, as seen in the package EXCHPART which is posted online at ""...

Hope this helps...


on 2/5/05 7:30 AM, Sami Seerangan at wrote:

> Hi:
> I am running a stored procedure(say P1 with 5000 lines)  using a
> particular session (say sid=135).
> By joining gv$session and gv$sql, I can identify whether the stored
> procedure is running or not.
> But  is there a way to indentify, what is the current operation
> (within the stored procedure, what particular statement being
> executed) OR where we are in the stored procedure
> Thanks in advance,

-- Received on Sat Feb 05 2005 - 22:16:44 CST

Original text of this message