Solved

find the stored procedure name in which we are in - Oracle

Posted on 2007-03-28
5
424 Views
Last Modified: 2013-12-07
From inside a stored procedure in Oracle 9 , how do we find the procedure name in which we are in.
0
Comment
Question by:aarch1
5 Comments
 
LVL 9

Expert Comment

by:konektor
ID: 18808859
i think there is no way how to detect it, probably few joins of v$session and v$sql you night detect what is executing now, but theese views cannot be queried by anyone, only by users having SELECT_CATALOG_ROLE
0
 

Author Comment

by:aarch1
ID: 18808948
Thanks  konektor.
But we may not be able to give that  SELECT_CATALOG_ROLE to the users.
--
This is a stored procedure using "htp" and "owa_util" packages being run in web uing Oracle 9ias.
I did find a owa_util.get_procedure which is able to get me a procedure name which is being run
from the web.
--
The problem is the web procedure (say proc1) calls proc2 inside which is being shown in the
web browser.
--
owa_util.get_procedure  returns me the name proc1. Is there away to get the name proc2
which is acually shown in the browser by proc1?

Thanks
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 500 total points
ID: 18814488
You can use DBMS_UTILITY package to find the name of the stored procedure from within it. All you have to do is format the output in the way you want. See the usage:

create or replace procedure test_proc
as
 l_str varchar2(4000);
begin
 l_str := dbms_utility.format_call_stack;

 dbms_output.put_line(substr(l_str,1, 256));
end;
/
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now