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

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@xxxxxxxxx> 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@xxxxxxxxxxxx>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
>> @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>
>>
>>
>>
>
>
> --
> Regards,
> Srinivas Chintamani
>



-- 
Tanel Poder
http://blog.tanelpoder.com

Other related posts: