"NVL" for SQL*plus commandline parameters

Recently, I had to create an installer SQL*Plus script which could accept input parameters. "So, big deal?" I hear you think. And at first, the task seemed simple enough. Until I started testing it (which is always a good idea in my opinion…)

I created a script "installer.sql" and referred the input parameter as &1, &2 and &3. I needed these for the username, password and database (TNSNames entry to be precise). For the sake of simplicity, let’s say this was the entire script:

  PROMPT connecting as &1
  CONNECT &1/&2@&3
  SELECT username from user_users;

and then run the script:

  $ sqlplus /nolog @installer.sql test test test

Simple enough, right? The script does exactly what you'd except: It prompts that it will connect as user "test", then connects (provided you actually have a user test with password test on database test...) and shows the current user (hopefully "test").

I then tried the use other parameters (&4) to do some more then only connecting. Of course, that will work fine, but I ran into trouble when trying to leave the fourth parameter empty. I wanted this parameter to be optional and give it some value based on the contents of the database.

What happens is that the fourth parameter is not set to NULL, but the parameter is not recognised at all. First thing SQL*Plus will do, is ask you for this parameter:

  Enter value for 4:

So, when parameters are not entered on the commandline, SQL*plus will not recognise them. After a lot of experimenting, I found that you can define your own "parameter 4" using the COLUMN syntax. Using this, you can use a sort of NVL function for the inputparameters. I haven't exactly fugured out why thuis works, but so far, all tests are positive:

  COLUMN inputpar01 NEW_VALUE 1 NOPRINT
  select 1 inputpar01
    from dual
   where 1=2;

This fools SQL*Plus creating the explicit parameter 1 (instead of an implicit parameter), but the nice part of it is that it actually retains the value for an implicit parameter, if defined.
[Edit]: Please note that for sqlplus 11.x and above, you must be connected to a database for this part to work. So you will have to replace the "/nolog" with a valid login. This can be for any database, though.[/Edit]

I then modified the script to, basically, the following:

  COLUMN inputpar01 NEW_VALUE 1 NOPRINT
  COLUMN inputpar02 NEW_VALUE 2 NOPRINT
  COLUMN inputpar03 NEW_VALUE 3 NOPRINT
  COLUMN inputpar04 NEW_VALUE 4 NOPRINT
  select 1 inputpar01
       , 2 inputpar02
       , 3 inputpar03
       , 4 inputpar04
    from dual
   where 1=2;
  --
  PROMPT connecting as &1
  CONNECT &1/&2@&3
  SELECT username from user_users;
  PROMPT value for parameter 4 = &4

You can modify this with extra checks for mandatory parameters, allowed values, etc, etc. This way, you actually have an option to use NVL-like functionality on your input parameters.