Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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: Leonard, George <GLeonard_at_wesbank.co.za>
Date: Mon, 7 Feb 2005 10:24:09 +0200
Message-ID: <1831A554E8800049B6B970790D2513C001C361BB@fnbkrkmx01.fnb.co.za>


Ok

This surely does not mean

The module name can only be 48 chars and=20 action 32, :(((

George
=20________________________________________________
George Leonard
Oracle Database Administrator
New Dawn Technologies @ Wesbank
E-mail:gleonard_at_wesbank.co.za
=20

You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
=20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman Sent: 05 February 2005 20:51 PM
To: Oracle L (E-mail)
Subject: 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"...

=20*/

v_errcontext :=3D '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');

--
/*

=20* Retrieve table subpartitioning information from USER_PART_TABLES...
=20*/
v_errcontext :=3D 'query all_part_tables'; dbms_application_info.set_action(v_errcontext); select subpartitioning_type,
=20 def_subpartition_count
into v_subpartitioning_type,
=20 v_def_subpartition_count
from all_part_tables ... -- v_errcontext :=3D 'restore previous MODULE and ACTION values'; dbms_application_info.set_module(v_save_module, v_save_action); -- exception
=20 when others then
=20 v_errmsg :=3D substr(sqlerrm,1,500);
=20 dbms_application_info.set_module(v_save_module,
=20 v_save_action);
=20 raise_application_error(-20000,
=20 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: >=20 > I am running a stored procedure(say P1 with 5000 lines) using a > particular session (say sid=3D135). >=20 > 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 >=20 > Thanks in advance, -- http://www.freelists.org/webpage/oracle-l _________________________________________________________________________= __________________________ The views expressed in this email are, unless otherwise stated, those of = the author and not those of the FirstRand Banking Group an Authorised Financial Service Provider o= r its management. The information in this e-mail is confidential and is intended solely for=
=20the addressee.
Access to this e-mail by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribut= ion or any action taken or=20 omitted in reliance on this, is prohibited and may be unlawful. Whilst all reasonable steps are taken to ensure the accuracy and integrit= y of information and data=20 transmitted electronically and to preserve the confidentiality thereof, n= o liability or=20 responsibility whatsoever is accepted if information or data is, for what= ever reason, corrupted=20 or does not reach its intended destination.
=20 ________________________________
-- http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 07 2005 - 03:27:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US