QuickTip: Oracle SQLPlus – (IF) Check for parameters to script

I was making a generic SQL script. To make it generic, I decided to use command line arguments to the script.

To do this, I created a defined variable that is set to the 1st parameter (&1). Then, throughout the script, I can use &ACCT_NBR, instead of hard coding the value.

DEFINE ACCT_NBR=’&1′
SELECT * from account WHERE acct_nbr = &ACCT_NBR.

This worked fine. You just have to SET DEFINE ON before using & in your script.

(Of course, the actual script has multiple SQLs using this variable. I just included a simple one above to show the usage.)

But then, I was worried about future users of the script forgot to add command line argument. The whole script may go crazy. I wanted to have sort of an IF check and exit before any other script was executed, in case the parameter was missing. But, plain SQL does not offer this in Oracle. (PL/SQL does, but that’s a whole different language with it’s own quirks! For my task, PL/SQL might have been an overkill).

After trying several things, I came up with the below lines:

WHENEVER SQLERROR EXIT SQL.SQLCODE
DEFINE ACCT_NBR=’&1′
SELECT &ACCT_NBR FROM dual; 

This prompts for the value of (parameter) 1, if the parameter was missing from command line.

12:07:13 SQL> DEFINE ACCT_NBR=’&1′
Enter value for 1: 

Here I pressed enter, thus the parameter is not set. It errors out with the below message:

12:07:13 SQL> SELECT &ACCT_NBR FROM dual;
old 1: SELECT &ACCT_NBR FROM dual
new 1: SELECT FROM dual
SELECT FROM dual
*
ERROR at line 1:
ORA-00936: missing expression

and the script exits.

So, there you have it. an IF check without the IF in SQL!