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

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: chet.justice@xxxxxxxxx
  • Date: Sat, 05 Dec 2009 08:42:12 +0100

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
@one
@two
@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;
/
@four
@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> @driver_script
module name: 02@ one.sql
script: one.sql

PL/SQL procedure successfully completed.


'ON
---
one

module name: 02@ two.sql
script: two.sql

PL/SQL procedure successfully completed.


'TW
---
two

module name: 02@ three.sql
script: three.sql

PL/SQL procedure successfully completed.

module name: 03@ four.sql
script: four.sql

PL/SQL procedure successfully completed.


'FOU
----
four

module name: 03@ 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;
> /
>
> *@script 1*
>
> BEGIN
>   dbms_application_info.set_action( action_name => 'script 2' );
> END;
> /
>
> *@script 2*
>
> BEGIN
>   dbms_application_info.set_action( action_name => 'script 3' );
> END;
> /
>
> *@script 3*
>
> etc...
>
> chet
>
> On Fri, Dec 4, 2009 at 10:39 PM, Srinivas Chintamani
> <srinivas.chintamani@xxxxxxxxx <mailto:srinivas.chintamani@xxxxxxxxx>>
> 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


Other related posts: