Salient Soft

Update 29/3/2012 – SP2-0310 error due to backslash “\” in parameter

SQLplus has a bug whereby if call to a subscript in the same directory has a command line parameter with a backslash in it, SQLplus messes up the default directory processing and cannot find the called script. It seems to think that the backslash in the parameter is part of the called script file path.

To get around this and avoid hard coding the subdirectory in this script, I pass the subdirectory in from the caller.

Note that this issue may well be the same issue listed below in connection with the use of forward slash “/” causing default directory issues.

A second bug is also present, whereby &1 only works the first time. When used a second time, it incorrectly replaces it with the &1 parameter for the last called script, rather than using the one passed in from our caller. It may be that these &1-&n parameters are global in nature, rather than per level. To get around this, I use an explicit named define for the &1 and use that instead.

The following illustrates the problems:-

–This works ok assuming AddDomain.sql is present in the same directory as this script
@@AddDomain ‘1’  ‘!’

–This will fail with an SP2-0310 error
@@AddDomain ‘1’ ‘\’

The following fix resolves the problem:-

Calling script Main.sql

–We pass the script its own subfolder to get around an SqlPlus SP2-0310 bug, see Lookups.sql
@@Lookups\Lookups ‘Lookups’

Called Script Lookups.sql

/*
* SQLplus has a bug whereby if a parameter has a backslash in it,
* SQLplus messes up the default directory processing and cannot find the called script
* It seems to think that the backslash in the parameter is part of the called script file path
* To get around this and avoid hard coding the subdirectory in this script,
* we pass it in from the caller.
*
* A second bug is also present, whereby &1 only works the first time.
* When used a second time, it incorrectly replaces it with the &1 parameter
* for the last called script, rather than using the one passed in from our caller
* It may be that these &1-&n parameters are global in nature, rather than per level.
* To get around this, we use an explicit named define for the &1 and use that instead.
*
* These 2 bugs are awful – I’m not impressed!
*/

define dir=&1
 
@@&dir\AddDomain ‘1’  ‘\’
@@&dir\AddDomain ‘2’  ‘\reports’

These fixes resolve the problem.

Update – Parameter passing, use of “/”  and default directory issues

These issues came about when switching to the command line sqlplus utility rather than using Toad as I had before.

1. If a parameter passed to a called script (in this case via @@) contains a slash “/”, Sql*Plus gives an SP2-0310 file open error. The error is nothing to do with opening the file, but is due to the slash. Escaping it was no help. I was using the slash as a date delimiter for a constant date string passed to the called script, so I just switched this to a dash “-“ instead and changed the to_date string in the called script to ‘YYYY-MM-DD HH24:MI:SS’. This solved the problem.
2. If a script called via “’@@” is followed by a “/”, then the script will be called twice. Whilst it is normal to follow an SQL command with the slash, this must not be done when calling a script.
3. When a relative path is used to call a script via the “@@” command, the default directory for the relative path is not the directory of the calling script, as you might expect, but rather the top level default directory. This is rather nasty and inconsistent in my view, but is simple to fix once you know.

Update – Return Codes from Sql*Plus

Sql*Plus can be configured to return an exit code to the calling operating system, to indicate either an OS error (such as a file open error), or an SQL error. This is extremely useful for detecting the success or failure of a script run – for example, I use it during JUnit testing when running scripts to set up a test, as there is then no need to scan log files and you can then just look at the test outcome (which is a central goal of JUnit).

To do this, just add the following at the head of your top level script. This will cause the script or any of its called scripts to abort with an exit code when it fails for any reason:-

WHENEVER OSERROR  EXIT 1
WHENEVER SQLERROR EXIT SQL.SQLCODE

The OSERROR unsurprisingly deals with OS related errors. Unfortunately, it does not appear to be possible to return the actual OS error, so in this case I have returned value of 1 which is a Windows ‘illegal function’ error, which is also what is returned when you copy or rename an invalid filename for example. The SQLERROR returns the actual Oracle error code. These codes are returned to the operating system, and for example under Windows may be detected by testing the %ERRORLEVEL% value as per normal with Windows batch files. I have yet to try this under Linux but the mechanism is similar.

Update – Verify and echo

When debugging a script, it is often useful to log each statement in the output (echo), and also to check how parameter substitution was performed (verify). To do this, just add the following to the head of your top level script. To disable them, just use off instead of on:-

set verify on
set echo on

Original Post

This post details some common pointers and gotchas when using nested scripts in Sql*Plus, and passing paramters to a nested script.
The examples were all executed with Toad, which is subset compatible with Sql*Plus, so the points here should work with either.

1. Use @filepath to call a nested script with a full file path. The default file type is .sql.
2. A common technique is to place all related scripts and sub scripts in the same directory. If you do this, use @@filename instead with just the filename, and SQL*Plus/Toad will assume that the called script is in the same directory as the executing one. This allows a collection of scripts to be moved to any desired directory and still call each other correctly with no changes.
3. Parameters may be defined with the define  command as in the example below. Defined  parameters are global and visible to a called scripted even if defined in the calling one.
4. Parameters are referred to using &name for a defined parameter, or &1, &2 for the first or second parameter passed to a script.
5. When passing parameters to a script you must delimit them with spaces and must have single quotes around the parameter values, which will allow the parameter value to contain spaces. Note however that you cannot pass an empty string using two quotes ‘’ as this gives a missing parameter error. Oracle treats an empty string as a null anyway, so you should pass the null value differently, e.g. using null.
6. If you use &name for a parameter you have not previously defined, you will be prompted for the value each time you refer to &name. If you use &&name you will only be prompted for the value the first time, and it will stick from then on and you will not be prompted. If a variable has been previously defined you just use &name all the time and the defined value is used – you will not be prompted.
7. When defining a parameter or passing one in a call to a sub script,  put single quotes around the value. These will be stripped off when you refer to the parameter, so you will need to add them back when referring to the parameter if you are using a quoted string, for example ‘&1’ or ‘&param’. Double quotes did not work correctly with Toad (not sure about Sql*Plus) so are best avoided.
8. Due to the above quote handling/stripping therefore, it is not straightforward/posible to add your own secondary quotes to pass a quoted string with the quotes already present. You must add the quotes in the called script, which means that you must decide whether you are passing a constant string (which will have quotes added by the called script), or an expression (which will not have quotes added). There is not a way I could find to pass a value which can either be a quoted string or expression. In practice, this tends not to be an issue and you can work within the limitation, unless you are trying to make scripts fly to the moon – don’t – they are not programs!
9. If you want to append alphanumerics immediately after a parameter expansion, place a dot after the parameter name, and the dot will be removed, for example &param.xxxx will substitute the definition of param and concatenate it directly with xxxx. Set Concat lets you define the concatenation character to be used, period (dot) is the default.

The documentation for Sql*Plus may be found here. In addition, this is a useful site detailing a number of the features (although it gets the meaning of & and && the wrong way around!)

Simple Example of Nested Scripts with Parameter passing

Test1.sql

define Table = ‘dual’
@@test2 ‘dummy’ ‘X’

Test2.sql

select * from &Table where &1 like ‘&2’
/

No Comments »