REM
/*
 * DefaultParametersWhenNotSpecified.Sql (c) Tim Onions 2008
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */

COLUMN 1 NEW_VALUE 1
COLUMN 2 NEW_VALUE 2
COLUMN 3 NEW_VALUE 3
COLUMN 4 NEW_VALUE 4
COLUMN 5 NEW_VALUE 5

SET FEEDBACK off
SELECT NULL "1", NULL "2", NULL "3", NULL "4", NULL "5" 
  FROM dual
 WHERE rownum=0;
SET FEEDBACK on

PROMPT Command line parameter #1 has a value of "&&1"
PROMPT Command line parameter #2 has a value of "&&2"
PROMPT Command line parameter #3 has a value of "&&3"
PROMPT Command line parameter #4 has a value of "&&4"
PROMPT Command line parameter #5 has a value of "&&5"

DEFINE

SET DEF #
/* When you write a SQL*Plus script often as not you need to pass data into it. You can include ACCEPT statements to prompt the user
   to enter data as required but if you want the script to run unattended (maybe to monitor something) then you need to pass the parameters
   in on the command line. This is easily done as SQL*Plus uses variables 1, 2, ... nn for each command line argument which can then be reference
   like &1, &&2 etc.

  However, what if you wish to right a general purpose script which takes command line parameters if supplied otherwise applies a default [or
  conceivably asks for user input]? Getting user input is easy as this is the default situation - when SQL*Plus sees an &&1 and variable 1 has yet to
  be defined it will prompt for a value to be entered - effectively halting the script until user action has been taken. But what if no command line argument
  should mean take a default (even if the default is NULL)?

  Well, the answer is relatively easy if somewhat obscure. Simple define columns for as many command arguments as the script uses using the NEW_VALUE feature eg:

COLUMN 1 NEW_VALUE 1

  Then SELECT from dual NULL for each column using an alias for each of the command line argumen position (1, 2, .. nn) eg:

SELECT NULL "1"
  FROM dual
 WHERE rownum=0;

  By some magic even though the query returns no rows column 1 will now exist and contain NULL. Next time your script references it, via &&1 etc, SQL*Plus
  will not need to prompt you, the user, to enter a value.

@DefaultParametersWhenNotSpecified 1 2 3 4 5
UNDEF 1
UNDEF 2
UNDEF 3
UNDEF 4
UNDEF 5

@DefaultParametersWhenNotSpecified
UNDEF 1
UNDEF 2
UNDEF 3
UNDEF 4
UNDEF 5

@DefaultParametersWhenNotSpecified 1
UNDEF 1
UNDEF 2
UNDEF 3
UNDEF 4
UNDEF 5

*/
SET DEF on
REM