Solved

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

Posted on 2007-03-28
5
477 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

623 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