SQL*Plus Basics – automate logfile name based upon script name

tl;dr

-- enable sql*plus to track the name of any currently running script as module name in application_info
set appinfo on

-- define sql*plus substitution variables SCRIPTNAME and LOGFILENAME
column script_name new_value scriptname
column logfile_name new_value logfilename

-- fetch name of the currently running SQL*plus script 
select regexp_replace(sys_context('userenv','module'),'^\d*@ *') as script_name from dual;

-- change suffix from .sql to .log and use as name for the log
select replace('&scriptname.','.sql$','.log') as logfile_name from dual;

-- start the log
spool &logfilename.

Purpose

Typical for an sql*plus script is that we spool the output to another file. Sometimes to export data and even more often to capture what happend in case of an error. The latter case is a typical example where the presented code will help to reduce hard coding the name of the script file into the file itself. So if the script name changes we don’t need to remember to change the hardcoded values inside the script.

Problem demonstration

$ sqlplus /nolog @myScript.sql
connect myUser/myPwd@myTnsAlias

spool myScript.log;

declare
  v_script varchar2(128);
begin
  v_script := 'myScript.sql';
  doSomething;
exception
   when others then 
     logger.log_error ('Error in Script '||v_script);
     raise;
end;
/

spool off;

The example shows that the name of the script “myScript” is hardcoded two times inside the script itself.

So whenever the name of the script changes, there is a good chance we need to change those two hardcoded occurences too. This includes cases where we copy this script as the basis for another script,

Here is how we can avoid the hard coding.

Solution

$ sqlplus /nolog @myScript.sql

-- enable sql*plus to track the name of any currently running script as module name in application_info
set appinfo on

-- define sql*plus substitution variables SCRIPTNAME and LOGFILENAME
column script_name new_value scriptname
column logfile_name new_value logfilename

-- fetch name of the currently running SQL*plus script 
select regexp_replace(sys_context('userenv','module'),'^\d*@ *') as script_name from dual;

-- change suffix from .sql to .log and use as log name
select replace('&scriptname.','.sql$','.log') as logfile_name from dual;

-- start logging
spool &logfilename.

declare
  v_script varchar2(128);
begin
  -- move sql*plus substitution variable into plsql variable
  v_script := '&scriptname.';
  doSomething;
exception
   when others then 
     -- add name of currently running script to the error log
     logger.log_error ('Error in Script '||v_script);
     raise;
end;
/
spool off;

Detailed code explanation

Some words what exactly is happening in the script.

Step 1) enable appinfo

First we need to enable SQL*plus to track the name of any script that is running as the module name in the application information of the session.

-- enable sql*plus to track the name of any currently running script 
-- as module name in application_info
set appinfo on

There are several ways to retrieve this information. We can see it in v$session or v$sqlarea, we can use dbms_application_info.read_module or we can check the userenv context module .

Step 2) define sql*plus variables

The name of the script should be stored in a sql*plus substituition variable scriptname which is not a bind variable.

Typically a substitution variable is created using the DEFINE command. But here we use the lesser known way of matching a column name from a select to such a variable.
column <column> new_value <variable>

-- define sql*plus substitution variables SCRIPTNAME and LOGFILENAME
column script_name new_value scriptname
column logfile_name new_value logfilename

To use such a variable the syntax is “&variable.” (without the “). The value of a substitution variable replaces (substitutes) the name of the variable anywhere inside the script. Ambersand “&” marks always the beginning and dot “.” can mark the end of the variable name, but the dot is optional. If a normal character (or even a dot) follows the substitution, then the additional dot is important to specify, where the name of the substition variable ends.

Because it is a sql*plus variable, this can replace values inside of strings.

Step 3) get the script name

Assumption is that a script was started, either by calling start scriptname (or short @scriptname) or by providing the name of the script when calling sqlplus from the shell. Both ways work.

We read the name of the script from the userenv: sys_context('userenv','module')

The value that is delivered seems to have some additional stuff at the beginning. This is something you should check in your system, how the file name exactly looks like. In my case it was the string
“01@ myScript.sql“

Some string manipulation is needed to get rid of the first part “01@ ” of the returned string. I used a regular expression to do so. Choose whatever you prefer.

-- fetch name of the currently running SQL*plus script 
select regexp_replace(sys_context('userenv','module'),'^\d*@ *') as script_name from dual;

The regexp replace command removes any number of digits “\d*” followed by “@” followed by a blank ” ” from the beginning “^” of the string and keeps the remainder intact.

Step 4) Get the logfilename from the scriptname and spool the log

Remember because of the column mapping earlier, the column logfile_name provides the value for our substitution variable logfilename.

'&scriptname.' is resolved at runtime into 'myScript.sql'.

Assuming that the script has a suffix .sql (default), we replace that suffix with .log.

-- change suffix to .log and use as log name
select replace('&scriptname.','.sql','.log') as logfile_name from dual;

-- start the log
spool &logfilename.

Step 5) Track the script name for error logging

  -- move sql*plus substitution variable into plsql variable
  v_script := '&scriptname.';

Recommendation is to use a plsql variable and put the SQL*plus substitution variable into the plsql variable. Then use this plsql variable wherever you need it, including the error handler. In general this should only be done for code directly inside your script, not inside some plsql packaged procedure for example. Reason is, that there is no guarantee, that the sql*plus variable is set during all executions of the packaged procedure.

criticism

• A working connection to the database is needed. So the log does not store every command that runs inside the script. Especially it can not include any potential connect commands nor the commands to find out about the name of the script.
• We can not generalize this code (step 3) into another script, because there the script name would be different.
• Because substitution variables need the ambersand “&” it is not easy to make sql*plus avoid interpreting ambersands that are part of normal strings as the beginning of a substitution variable.
  Or to say it in another way. Careful with set define off. A workaround sometimes can be to set define off at a later time in the script, when our variables are not used anymore or even to move the value from a substitution to a bind variable.