Sqlplus is my second home, part 5: Reading the name of currently executing script

If you need to run and manage loads of sqlplus scripts which call other scripts, which call other scripts etc, then you are probably interested in the sqlplus APPINFO parameter shown below.

When you issue SET APPINFO ON in sqlplus, this makes sqlplus to automatically call DBMS_APPLICATION_INFO.SET_MODULE and set the MODULE value to sql script name which is currently being executed.

This allows you to easily pass the current script name info to Oracle, without the need to have a manual call to SET_MODULE in beginning and end of every script (along with some mechanism for storing the previous module).

A simple example is below. I used two scripts blah.sql and blah2.sql for my test:

C:\tmp>type c:\tmp\blah.sql

select sys_context('USERENV', 'MODULE') from dual;

@@blah2

select sys_context('USERENV', 'MODULE') from dual;

 C:\tmp>type c:\tmp\blah2.sql

select sys_context('USERENV', 'MODULE') from dual;

So, blah.sql reports the current module, then calls blah2.sql which reports current module and then returns back to blah.sql which returns the current module again.

SQL> set appinfo on
SQL>
SQL> @blah

SYS_CONTEXT('USERENV','MODULE')
------------------------------------------------
01@ blah.sql

SYS_CONTEXT('USERENV','MODULE')
------------------------------------------------
02@ blah2.sql

SYS_CONTEXT('USERENV','MODULE')
------------------------------------------------
01@ blah.sql

SQL>

Looks cool!

From output above we can see the following things:

 

  1. The current script name is set as current MODULE for the session
  2. The 01, 02 and 01 numbers in the prefix show nesting level of currently executing script. This could be useful for understanding where from was this script called (e.g. from top level or from some deeper nested level of the script hierarchy.
  3. After the nested script returns, the previous nesting level and script name are restored. Looks like sqlplus uses some kind of a stack mechanism (max depth 20 levels) for storing old script names and nesting levels.

If I run the script using full pathname, then the pathnames are reported in module too:

SQL>
SQL> @c:\tmp\blah

SYS_CONTEXT('USERENV','MODULE')
------------------------------------------------
01@ c:\tmp\blah.sql

SYS_CONTEXT('USERENV','MODULE')
------------------------------------------------
02@ c:\tmp\blah2.sql

SYS_CONTEXT('USERENV','MODULE')
------------------------------------------------
01@ c:\tmp\blah.sql

Using the full pathnames may not be a good idea though as the MODULE variable can accommodate only 48 bytes (with the 4 character prefix). If you exceed 48 bytes then the beginning of script path is truncated and a "<" character indicates truncation:

SYS_CONTEXT('USERENV','MODULE')
--------------------------------------------------
02@<ah2_abcdefghijklmnopqrstuvwxyz1234567890.sql

Note that you can also use SET APPINFO <text> to set the default top level module name when not executing a script (and being in interactive mode instead):

SQL> set appinfo main
SQL> set appinfo on
SQL>
SQL> select SYS_CONTEXT('USERENV','MODULE') from dual; -- this is typed in interactively

SYS_CONTEXT('USERENV','MODULE')
-----------------------------------------------------------
main

SQL>
SQL> @c:\tmp\blah  -- lets run the script

SYS_CONTEXT('USERENV','MODULE')
-----------------------------------------------------------
01@ c:\tmp\blah.sql

SYS_CONTEXT('USERENV','MODULE')
-----------------------------------------------------------
02@<ah2_abcdefghijklmnopqrstuvwxyz1234567890.sql

SYS_CONTEXT('USERENV','MODULE')
-----------------------------------------------------------
01@ c:\tmp\blah.sql

SQL>
SQL> select SYS_CONTEXT('USERENV','MODULE') from dual; -- this is typed in interactively

SYS_CONTEXT('USERENV','MODULE')
-----------------------------------------------------------
main

SQL>

This APPINFO parameter has apparently been available at least since Oracle 9.0, maybe even in 8i (haven't had a chance to test below version 9.2 though).

As sqlplus issues an OCI call to execute DBMS_APPLICATION_INFO every time another script is called or returns, this may impact your sqlplus script running performance somewhat. This may not be noticeable in some cases, but I would probably not use this instrumentation if both of the conditions below are met

  1. Need to run a lot of small sqlplus scripts
  2. The roundtrip latency between sqlplus client and database is high

As usual, it's your own responsibility (and benefit) to test the effects out yourself :)

This entry was posted in Cool stuff, Oracle and tagged , . Bookmark the permalink.

8 Responses to Sqlplus is my second home, part 5: Reading the name of currently executing script

  1. sudhir says:

    some other choice in 10g

    desc v$session

    ..
    CLIENT_IDENTIFIER VARCHAR2(64)

    SQL> exec dbms_session.SET_IDENTIFIER(’01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789′);

    SQL> select CLIENT_IDENTIFIER,length(CLIENT_IDENTIFIER) from gv$session where sid = (select sid from v$mystat where rownum=1);

    CLIENT_IDENTIFIER LENGTH(CLIENT_IDENTIFIER)
    —————————————————————- ————————-
    0123456789012345678901234567890123456789012345678901234567890123 64

    I found in the past using bind variables in call to dbms_application_info helped reduce the burdon on parsing at least to some extent

    btw, fantastic site

  2. Taimo says:

    parameter APPINFO existed in at least since 7.2.3 but was OFF by default
    In 8.0 and 8i APPINFO was ON by default
    Since 9i APPINFO is OFF by default again

    just my 2 cents worth

  3. Bill hancock says:

    I am executing a series of sql scripts and keep running into the following

    SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.

    I have a menu script that I need to keep coming back to get input from the user how do I reset the stack mechanism so this !@#$%^ error goes away and the user can execute the series of scripts till their fingers fall off?

    Any assistance you can provide will be of great help. I have tryed SET APPINFO MAIN as the first line in my menu script but this did not seem to help any. I use the SET APPINFO ON immediately after issueing the SET APPINFO MAIN and used the “select SYS_CONTEXT(‘USERENV’,’MODULE’) from dual;” to see the leveling, so I am at my wits end.

  4. Tanel Poder says:

    Hi Bill,

    Does this script call itself in the end, to get back to the menu again? sqlplus doesn’t support more than 20 levels of such recursion, thus the error.

    When I had a similar situation (I wanted to rerun a script again and again) I created another, wrapper script which just had thousands of lines in it which called my script, simply something like this:

    @myscript
    @myscript
    @myscript
    @myscript
    @myscript
    ….

    That’s not ideal solution, but I don’t know any better sqlplpus-only solutions. Another option would be to use a shell script which constantly calls your script (which needs to log on again) or write something more sophisticated like a bash/ksh shell script which calls sqlplus in a separate child process and pipes any commands to that child for execution (if you are on unix)

  5. Bill hancock says:

    The answer to your first question is yes, kinda sorta. The menu script is named Select_Table, one of the selections in this menu is to exit and the Select_Table script is executed after each table load with some house cleaning scripts in the middle to prepare the selected table for the SQL*LOADER and error notification, but it comes back to the menu script for the user to either select another table to load or exit.

    The first menu select is a script that prepares all the tables in the menu for loading this is done because the business unit wanted to be able to do just that load all the tables which means that they would be going down the menu selections loading all the tables. I have a start script that asks the user which server they want to work on and their answer sets some substitution and user variables and then calls the menu script, but when I used SET APPINFO ON and used the

    SELECT SYS_CONTEXT(‘USERENV’, ‘MODULE’) FROM DUAL;

    This starter script and the set variables script were in the stack thereby causing the menu script to be third in line. This series of scripts is my 1st attempt at writing SQL scripts that act as a package, I have taken the number of scripts used from around 14 to 10 but my inexperience in the use of substitution variables and combining scripts is a problem I know.

    In the second sentence you said you created a wrapper script that called your script. If I understand correctly, it called your script when the user wanted to use your script so if I had a script that called say the first script which would set a substitution variable to the name of the next script to execute and then called the wrapper script which would issue the @substitution variable and with each script the wrapper calls that script set the substitution variable to the name of the next script to execute could I get around the recursion restriction issue?

    Here is an example of what I am talking about:

    I have a script that is named “Start” which has only two lines a DEFINE next_script statement and a call to a script named “WrapperScript” that calls “My1stScript” in “My1stScript” I do some stuff a set next_script to “My2ndScript” and call the “WrapperScript” that has @next_script.

    Is this what you were talking about? If not I guess I am a bit on the slow side so could you be a bit more detailed.

  6. Tanel Poder says:

    long story short – sqlplus does not allow script calling depth of more than 20, so you need to find a way to not call scripts recursively so much. If you’re building something complex, I’d look into other tools (shell, perl or python) than just sqlplus scripts

  7. Bill hancock says:

    What I think I need to solve my problem is that if I could have the Select_Table script the top level and each time it comes back to the Select_Table script it will be at the top and the recursion doesn’t go higher then the 10 scripts I am calling.

    The reason I say this is because in you example above you call blah.sql which the SELECT statement indicates it is the 1st level and blah calls blah2 which is the 2nd level, blah2 calls blah and you are back at the 1st level. I takes this to say that if I can have the Select_Table script as the 1st level each time I come back to this script I will be at the 1st level.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>