Power to Build

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

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</pre>
<strong></strong>

and the script exits.

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


1 Comment

  1. I use another technique:

    — How to set default value to input parameter 1
    SET VERIFY OFF
    SET FEEDBACK OFF
    SPOOL temp.params
    DEFINE 1
    SPOOL OFF
    GET temp.params NOLIST
    LIST 1
    CHANGE /SP2-0135…/DEFINE 1 = “”
    SAVE params.sql
    CLEAR SQL
    @params.sql

    It’s better to turn off term while doing that to hide all intermediate results.

Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: