Re: Getting the name of currently executing sql script in sql*plus
From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Sat, 05 Dec 2009 08:42:12 +0100
Message-ID: <4B1A0ED4.1030909_at_roughsea.com>
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.
set serveroutput on
declare
module_name varchar2(100);
action_name varchar2(100);
BEGIN
END;
/
_at_four
_at_five
declare
module_name varchar2(100);
action_name varchar2(100);
BEGIN
END;
/
select 'one' from dual;
Date: Sat, 05 Dec 2009 08:42:12 +0100
Message-ID: <4B1A0ED4.1030909_at_roughsea.com>
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:Received on Sat Dec 05 2009 - 01:42:12 CST
> 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> -- http://www.freelists.org/webpage/oracle-l