RE: calling program
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 statementknow/report what program is using it? The overhead to give you what you're asking would, IMHO, be toohighif provided by the PL/SQL engine. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Big PlanetSent: Friday, March 15, 2002 2:18 PMTo: Multiple recipients of list ORACLE-LSubject: calling program Hi LIst , Is there a way a pl/sql procedureor function can know the calling procedure or calling program . -ak
RE: calling program
Actually you can, dbms_utility.format_call_stack, but you'll have to parse the information. See http://osi.oracle.com/~tkyte/who_called_me/index.html Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: calling program
-- -+ -- 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, noffsetIN NUMBER DEFAULT 0 ) AS l_newline CONSTANT CHAR(1) := CHR(10) ; l_call_stack VARCHAR2(4096) ; l_nameVARCHAR2(80) ; l_idx PLS_INTEGER; l_idx2PLS_INTEGER; -- BEGIN -- l_call_stack := DBMS_Utility.Format_Call_Stack ; -- -- call stack has the following format: -- -- - PL/SQL Call Stack - -- object line object -- handlenumber name -- 8c42ca8811 package body RTHOMAS.RON -- me -- 8c42ca8818 package body RTHOMAS.RON -- my caller -- 8c42ca8821 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 ) ; -- IFl_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 [EMAIL PROTECTED] Hit any PHB to continue... japplewhite@inet profit.com To: [EMAIL PROTECTED] Sent by:cc: [EMAIL PROTECTED]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 [EMAIL PROTECTED] (512)327-9068 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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
Re: calling program
Not exactly true... Well, since original question was, if it's possible to know calling procedure (or program), I think DBMS_UTILITY..FORMAT_CALL_STACK function will return as a VARCHAR2 call stack, which will provide info at leastabout calling PL/SQL units (procedures, functions, or just anonymous blocks). Check the docs on DBMS_UTILITY..FORMAT_CALL_STACK. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Jack C. Applewhite To: Multiple recipients of list ORACLE-L Sent: Friday, March 15, 2002 4:23 PM Subject: RE: calling program 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 statementknow/report what program is using it? The overhead to give you what you're asking would, IMHO, be toohighif provided by the PL/SQL engine. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Big PlanetSent: Friday, March 15, 2002 2:18 PMTo: Multiple recipients of list ORACLE-LSubject: calling program Hi LIst , Is there a way a pl/sql procedureor function can know the calling procedure or calling program . -ak
RE: calling program
Ouch! Looks like I need to RTFM, not only before asking a question, but before answering one - shoot from the hip = shot in the foot. 8-( Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Rajendra Sent: Friday, March 15, 2002 3:39 PM To: Multiple recipients of list ORACLE-L Actually you can, dbms_utility.format_call_stack, but you'll have to parse the information. See http://osi.oracle.com/~tkyte/who_called_me/index.html Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: calling program
Thanks all for your help. I got the answer . Big P - Original Message - From: Igor Neyman To: Multiple recipients of list ORACLE-L Sent: Friday, March 15, 2002 1:49 PM Subject: Re: calling program Not exactly true... Well, since original question was, if it's possible to know calling procedure (or program), I think DBMS_UTILITY..FORMAT_CALL_STACK function will return as a VARCHAR2 call stack, which will provide info at leastabout calling PL/SQL units (procedures, functions, or just anonymous blocks). Check the docs on DBMS_UTILITY..FORMAT_CALL_STACK. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Jack C. Applewhite To: Multiple recipients of list ORACLE-L Sent: Friday, March 15, 2002 4:23 PM Subject: RE: calling program 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 statementknow/report what program is using it? The overhead to give you what you're asking would, IMHO, be toohighif provided by the PL/SQL engine. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Big PlanetSent: Friday, March 15, 2002 2:18 PMTo: Multiple recipients of list ORACLE-LSubject: calling program Hi LIst , Is there a way a pl/sql procedureor function can know the calling procedure or calling program . -ak
