Re: Getting the name of currently executing sql script in sql*plus
From: Srinivas Chintamani <srinivas.chintamani_at_gmail.com>
Date: Sat, 5 Dec 2009 20:01:51 -0500
Message-ID: <98c5e2a20912051701l23a225c3yf142c6dc418210b7_at_mail.gmail.com>
Hi Stephane,
Date: Sat, 5 Dec 2009 20:01:51 -0500
Message-ID: <98c5e2a20912051701l23a225c3yf142c6dc418210b7_at_mail.gmail.com>
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:Received on Sat Dec 05 2009 - 19:01:51 CST
> 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 -- http://www.freelists.org/webpage/oracle-l