Re: Getting the name of currently executing sql script in sql*plus

From: Srinivas Chintamani <srinivas.chintamani_at_gmail.com>
Date: Sun, 6 Dec 2009 15:04:06 -0500
Message-ID: <98c5e2a20912061204u11b5d537x9c7a411cbd851e21_at_mail.gmail.com>

Hi Tanel,
As usual, I got the exact answer (From Oracle-l) that I was looking for !  Thanks a bunch to everyone on the list and to Tanel for this tip to make my life easier.

-- 
Regards,
Srinivas.

On Sat, Dec 5, 2009 at 9:09 PM, Tanel Poder <tanel_at_poderc.com> wrote:



> SET APPINFO ON

>

> or read this:

>

>

> http://blog.tanelpoder.com/2007/12/26/sqlplus-is-my-second-home-part-5-reading-the-name-of-currently-executing-script/

>

> --

> Tanel Poder

> http://blog.tanelpoder.com

>

>

>

> On Sat, Dec 5, 2009 at 7:01 PM, Srinivas Chintamani <

> srinivas.chintamani_at_gmail.com> wrote:

>

>> Hi Stephane,

>>

>> Thank you very much for the detailed example.  However, I was wondering if

>> there were a way to "AUTOMATICALLY" let sql*plus emit out the name of the

>> script that is is executing.  Kind of like '$0' equivalent in shell

>> scripting.  I guess I should have been more explicit in stating my need.

>>

>> --

>> Regards,

>> Srinivas.

>> On Sat, Dec 5, 2009 at 2:42 AM, Stephane Faroult <sfaroult_at_roughsea.com>wrote:

>>

>>> Actually, using dbms_application_info is exactly what SQL*Plus already

>>> does for you (which just shows that it was indeed a good idea!) -

>>>  all you have to do is read the information.

>>>

>>> I've created driver_script.sql as follows:

>>>

>>> --------------------------

>>> set serveroutput on

>>> _at_one

>>> _at_two

>>> _at_three

>>> --------------------------

>>>

>>> three.sql as follows:

>>>

>>>

>>> ----------------------------------------------------------------------------------

>>> declare

>>>  module_name varchar2(100);

>>>  action_name varchar2(100);

>>> BEGIN

>>>  dbms_application_info.read_module(module_name, action_name);

>>>  dbms_output.put_line('module name: ' || module_name);

>>>  dbms_output.put_line('script: ' || substr(module_name, 1 +

>>> instr(module_name, ' ')));

>>> END;

>>> /

>>> _at_four

>>> _at_five

>>>

>>> ----------------------------------------------------------------------------------

>>>

>>> and all the others on this pattern (here, one.sql):

>>>

>>>

>>> ----------------------------------------------------------------------------------

>>> declare

>>>  module_name varchar2(100);

>>>  action_name varchar2(100);

>>> BEGIN

>>>  dbms_application_info.read_module(module_name, action_name);

>>>  dbms_output.put_line('module name: ' || module_name);

>>>  dbms_output.put_line('script: ' || substr(module_name, 1 +

>>> instr(module_name, ' ')));

>>> END;

>>> /

>>> select 'one' from dual;

>>>

>>> ----------------------------------------------------------------------------------

>>>

>>> Here it goes:

>>>

>>> SQL> _at_driver_script

>>> module name: 02_at_ one.sql

>>> script: one.sql

>>>

>>> PL/SQL procedure successfully completed.

>>>

>>>

>>> 'ON

>>> ---

>>> one

>>>

>>> module name: 02_at_ two.sql

>>> script: two.sql

>>>

>>> PL/SQL procedure successfully completed.

>>>

>>>

>>> 'TW

>>> ---

>>> two

>>>

>>> module name: 02_at_ three.sql

>>> script: three.sql

>>>

>>> PL/SQL procedure successfully completed.

>>>

>>> module name: 03_at_ four.sql

>>> script: four.sql

>>>

>>> PL/SQL procedure successfully completed.

>>>

>>>

>>> 'FOU

>>> ----

>>> four

>>>

>>> module name: 03_at_ five.sql

>>> script: five.sql

>>>

>>> PL/SQL procedure successfully completed.

>>>

>>>

>>> 'FIV

>>> ----

>>> five

>>>

>>>

>>> You'll notice that you have the nesting depth before the script name -

>>> something that can also be useful.

>>>

>>> HTH

>>>

>>>

>>> Stéphane Faroult

>>>

>>>

>>>

>>>

>>> chet justice wrote:

>>> > You could use DBMS_APPLICATION_INFO

>>> > <

>>> http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10577/d_appinf.htm

>>> >

>>> > to do that, something like this:

>>> >

>>> > BEGIN

>>> >   dbms_application_info.set_module

>>> >     ( module_name => 'running scripts',

>>> >       action_name => 'script 1' );

>>> > END;

>>> > /

>>> >

>>> > *_at_script 1*

>>> >

>>> > BEGIN

>>> >   dbms_application_info.set_action( action_name => 'script 2' );

>>> > END;

>>> > /

>>> >

>>> > *_at_script 2*

>>> >

>>> > BEGIN

>>> >   dbms_application_info.set_action( action_name => 'script 3' );

>>> > END;

>>> > /

>>> >

>>> > *_at_script 3*

>>> >

>>> > etc...

>>> >

>>> > chet

>>> >

>>> > On Fri, Dec 4, 2009 at 10:39 PM, Srinivas Chintamani

>>> > <srinivas.chintamani_at_gmail.com <mailto:srinivas.chintamani_at_gmail.com>>

>>>  > wrote:

>>> >

>>> >     Hi Listers,

>>> >     Is there any way to get the name of the current script being

>>> >     executed by sql*plus?

>>> >

>>> >     For example..

>>> >     I have the following levels of scripts ...

>>> >

>>> >     1. Driver_Script.sql calls

>>> >     2.....one.sql and

>>> >     3.....two.sql

>>> >     4.....three.sql which calls

>>> >     5............four.sql and

>>> >     6............five.sql.

>>> >

>>> >     What I want to be able to do is within each one of those scripts,

>>> >     "Get" the name of the script being executed.  Is it possible to do

>>> >     this?

>>> >

>>> >     --

>>> >     Regards,

>>> >     Srinivas Chintamani

>>> >

>>> >

>>>

>>>

>>> --

>>> Stephane Faroult

>>> RoughSea Ltd <http://www.roughsea.com>

>>>

>>>

>>>

>>

>>

>> --

>> Regards,

>> Srinivas Chintamani

>>

>

>

>

> --

> Tanel Poder

> http://blog.tanelpoder.com

>

>



-- 
Regards,
Srinivas Chintamani

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Dec 06 2009 - 14:04:06 CST