Saturday, September 14, 2013

Using sqlplus input parameters to call specific scripts IF - THEN - ELSE style

Quite a while ago, I described using "NVL" for SQL*plus commandline parameters. Using this technique, you can create scripts that can cope with commandline parameters that are not always supplied on the commandline. So you don't always know whether a parameter is supplied or not and you don't want the script to go asking questions like "Enter the value for 4:" if you didn't supply a value for the fourth parameter.

A modified script made sure that all parameters were properly initialized and could be used in further statements without any prompts from sqlplus:

  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


Recently, I had a question if the user could be prompted to enter the value for the fourth parameter if this had been omitted from the command line. So, you supply parameters 1, 2 and 3 and leave out parameter 4, which the script actually needs (for whatever reasons).
This can be accomplished using extra scripts ("helper" scripts).

I create the first helper script, that asks for a parameter and I call it "ask_parameter.sql". The contents are very simple:

  accept &1 prompt "What is the value for parameter &2 : "

A second script "dummy_script.sql" is created and has no content. This script will be used when a value is actually provided for the fourth parameter.

After these two scripts are created, we modify the original script to:

  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
  --
  -- Check and set the fourth parameter 
  -- (NEWLY ADDED FUNCTIONALITY)
  --
  COLUMN ask_parameter NEW_VALUE ask_command NOPRINT;
  --
  SELECT nvl2( '&4'
             , 'dummy_script.sql'
             , 'ask_parameter.sql 4 my_name_for_par4'
             ) ask_parameter
  FROM dual;
  --
  set verify on
  set feedback on
  set termout on
  --
  -- Run the script determined in the previous step
  --
  @@&ask_command.
  --
  PROMPT value for parameter 4 = &4

The new part does the following:

  • define a new parameter "ask_command" that will contain the name of the script to call
  • based on the value of parameter 4, either select the value "dummy_script.sql" if it already contains a value, or select the value "ask_parameter.sql" (along with two new parameters for that script) if it contains no value
  • run the script selected in the previous step. This is why we need the dummy script: if you just leave this NULL, sqlplus will error on the @@ command.
  • if this is "ask_parameter.sql", then within that script &1 and &2 represent the parameters for that script and not the parameters for the main script. However, if you set "4" using the accept, it will set the fourth parameter for the main script!

In this case, when you call the main script using "testuser testpassword testdatabase"as parameters, you will be prompted:

  What is the value for parameter my_name_for_par4 :

This sets the value for parameter 4 of the main script, after which you can use it for any purpose (like using it as a parameter for another script).

So by using (generic) helper scripts, you can check, replace or prompt for any parameters. Just make sure that the scripts are in the same directory, or add a (relative) path to the helper scripts, should you decide to place them in a subdirectory (which is recommended for simplicity).

No comments:

Post a Comment