which PL/SQL Procedure is running in a session (using call stack)

24660

Hi,

I would like to know how you can see which proc is currently running in an active session.

DBMS_UTILITY.FORMAT_CALL_STACK let you view the stack for the current session, but I need to view another active session.

Example :

I call a PL/SQL Proc called PA from Oracle Forms

PA runs PB, PC and finally PD.

How can I view which proc is currently running for this session ?

I mean if PB is currently running, how can I view that PB is running ?

I can only view current statement of the "frmweb" session, which is a query, but if I have 5 procs to run, I don't want to look for this query in all procs.

Thx

Billy Verreynne

npragout wrote:

How can I view which proc is currently running for this session ?

Doubt that you can from one session, query another session's PL/SQL stack. Reason: the PL/SQL stack resides in the PGA (Private Global Area) memory of a session. As the name indicates, this is private memory of that session - and is not visible and cannot be queried by another session.

Other sessions can only see that which a session makes sharable - i.e. that which resides in the SGA or UGA.

I mean if PB is currently running, how can I view that PB is running ?

You can have the PL/SQL code use [DBMS_APPLICACTION_INFO|http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_appinf.htm] - and have it record the current active PL/SQL module/package.

24660

Thanks a lot !

With DBMS_APPLICATION_INFO.SET_CLIENT_INFO, I can easily see which procedure is running ...

21205

if you have you answer, mark the question as such... then everybody knows that this question is answered and can move on answering other questions