Re: Getting the name of currently executing sql script in sql*plus
From: Tanel Poder <tanel_at_poderc.com>
Date: Sat, 5 Dec 2009 20:09:06 -0600
Message-ID: <4602f23c0912051809n75c6bd15g249ee4dd2c5619ca_at_mail.gmail.com>
SET APPINFO ON or read this:
Date: Sat, 5 Dec 2009 20:09:06 -0600
Message-ID: <4602f23c0912051809n75c6bd15g249ee4dd2c5619ca_at_mail.gmail.com>
SET APPINFO ON or read this:
-- Tanel Poder http://blog.tanelpoder.com On Sat, Dec 5, 2009 at 7:01 PM, Srinivas Chintamani < srinivas.chintamani_at_gmail.com> wrote:Received on Sat Dec 05 2009 - 20:09:06 CST
> 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 -- http://www.freelists.org/webpage/oracle-l