Tanel Poder’s blog: Core IT for Geeks and Pros

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 :)