RE: calling program

From: Ron Thomas <rthomas_at_hypercom.com>
Date: Fri, 15 Mar 2002 13:45:10 -0800
Message-ID: <F001.0042B07B.20020315134510@fatcity.com>

• -------------------------------------------------------------+
• Procedure: Who_Called_Me
• -------------------------------------------------------------+
• Purpose
• This procedure determines who the caller and line number was -- PROCEDURE Who_Called_Me ( cname OUT VARCHAR2, nline OUT NUMBER, noffset IN NUMBER DEFAULT 0 ) AS l_newline CONSTANT CHAR(1) := CHR(10) ; l_call_stack VARCHAR2(4096) ; l_name VARCHAR2(80) ; l_idx PLS_INTEGER; l_idx2 PLS_INTEGER;
  --
  BEGIN
  --
  l_call_stack := DBMS_Utility.Format_Call_Stack ;
  --
  
  • call stack has the following format:
    --
    
  • ----- PL/SQL Call Stack -----
  • object line object
  • handle number name
  • 8c42ca88 11 package body RTHOMAS.RON -- me
  • 8c42ca88 18 package body RTHOMAS.RON -- my caller
  • 8c42ca88 21 package body RTHOMAS.RON -- my callers caller
  • 81da5830 1 anonymous block
    --
    
  • get past the header and the first object in the stack and its id
    --
    --
    l_idx := INSTR( l_call_stack, l_newline, 1, noffset+4 ) + 10 ; l_idx2 := INSTR( l_call_stack, l_newline, 1, noffset+5 ) ;
    --
    IF l_idx2 = 0 THEN l_idx2 := LENGTH(l_call_stack) + 1 ; END IF ;
    --
    --dbms_output.put_line( SUBSTR(l_call_stack,1,255) ) ;
    --dbms_output.put_line( 's ' || l_idx || ' e ' || l_idx2 ) ;
    --dbms_output.put_line( '.'||substr(l_call_stack,l_idx,l_idx2-l_idx)||'.' ) ;
    --
    --dbms_output.put_line( 'line ' || substr(l_call_stack, l_idx, 9) ) ;
    --dbms_output.put_line( 'name ' || substr(l_call_stack, l_idx+11, l_idx2-l_idx-11) ) ;
    --
    nline := TO_NUMBER( LTRIM(SUBSTR( l_call_stack, l_idx, 9 ) ) ) ; l_name := SUBSTR( l_call_stack, l_idx+11, l_idx2-l_idx-11 ) ;
    --
    IF l_name LIKE 'procedure %' THEN cname := SUBSTR( l_name, 11 ) ; ELSIF l_name LIKE 'function %' THEN cname := SUBSTR( l_name, 10 ) ; ELSIF l_name LIKE 'package body %' THEN cname := SUBSTR( l_name, 14 ) ; ELSIF l_name LIKE 'package %' THEN cname := SUBSTR( l_name, 9 ) ; ELSE cname := l_name ; END IF ;
    --
    END ;

Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
"Hit any PHB to continue..."

                                                                                                                       
                    japplewhite_at_inet                                                                                   
                    profit.com              To:     ORACLE-L_at_fatcity.com                                               
                    Sent by:                cc:                                                                        
                    root_at_fatcity.com        Subject:     RE: calling program                                           
                                                                                                                       
                                                                                                                       
                    03/15/02 02:23                                                                                     
                    PM                                                                                                 
                    Please respond                                                                                     
                    to ORACLE-L                                                                                        
                                                                                                                       
                                                                                                                       

Big Planet,

Whoa, now there's a can of worms!

In a word - no. Not unless you pass that info. in as an argument.

Think about what you're asking. Since PL/SQL procedures can be called from, not only other PL/SQL procedures and functions, but the SQL*Plus command line, ODBC calls, Java programs, etc. etc., what would be the identification mechanism?

Also, since PL/SQL functions can be embedded in SQL statements, there's yet another problem - how does a SQL statement know/report what program is using it?

The overhead to give you what you're asking would, IMHO, be too high if provided by the PL/SQL engine.

Jack

---

Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
japplewhite_at_inetprofit.com
(512)327-9068

     -----Original Message-----
     From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Big Planet
     Sent: Friday, March 15, 2002 2:18 PM
     To: Multiple recipients of list ORACLE-L
     Subject: calling program

     Hi LIst ,
     Is there a way a pl/sql procedureor function  can know the calling procedure or calling program
     .
     -ak

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ron Thomas
  INET: rthomas_at_hypercom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Mar 15 2002 - 15:45:10 CST