Re: Getting the name of currently executing sql script in sql*plus
- From: Srinivas Chintamani <srinivas.chintamani@xxxxxxxxx>
- To: Tanel Poder <tanel@xxxxxxxxxx>
- Date: Sun, 6 Dec 2009 15:04:06 -0500
Hi Tanel,
As usual, I got the exact answer (From Oracle-l) that I was looking for !
Thanks a bunch to everyone on the list and to Tanel for this tip to make my
life easier.
--
Regards,
Srinivas.
On Sat, Dec 5, 2009 at 9:09 PM, Tanel Poder <tanel@xxxxxxxxxx> wrote:
> 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
>
>
--
Regards,
Srinivas Chintamani
Other related posts:
