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

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 "http://www.EvDBT.com/tools.htm"...

• begin PL/SQL code fragment ------------------- Procedure prepare (...) As ... -- v_errcontext varchar2(300); v_errmsg varchar2(600); v_save_module varchar2(48); v_save_action varchar2(32); -- begin
  --
  /*
  • Register the program's MODULE and ACTION using DBMS_APPLICATION_INFO */ v_errcontext := 'register the procedure with DBMS_APPLICATION_INFO'; dbms_application_info.read_module(v_save_module, v_save_action); dbms_application_info.set_module('EXCHPART.PREPARE', 'Starting');
    --
    /*
  • Retrieve table subpartitioning information from USER_PART_TABLES... */ v_errcontext := 'query all_part_tables'; dbms_application_info.set_action(v_errcontext); select subpartitioning_type, def_subpartition_count into v_subpartitioning_type, v_def_subpartition_count from all_part_tables ...
    --
    v_errcontext := 'restore previous MODULE and ACTION values'; dbms_application_info.set_module(v_save_module, v_save_action);
    --
    exception when others then v_errmsg := substr(sqlerrm,1,500); dbms_application_info.set_module(v_save_module, v_save_action); raise_application_error(-20000, v_errcontext || ': ' || v_errmsg); end prepare;
• end PL/SQL code fragment -------------------

Hope this helps...

-Tim

on 2/5/05 7:30 AM, Sami Seerangan at dba.orcl_at_gmail.com 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,

--

http://www.freelists.org/webpage/oracle-l Received on Sat Feb 05 2005 - 22:16:44 CST