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:
