On SQL*Plus Defines

Some time back Pavel Luzanov and me had an idea of writing a series of articles on SQL*Plus. We spent some time and came up with two (1, 2 those are in Russian, so use http://translate.google.com/ to get them translated into your language) of them, but then it all somehow stopped. Lack of time and desire I believe...

Anyway, there are questions popping up here and there on some basic SQL*Plus functionality and this post below is an answer to one of them.

This brief example illustrates how one can avoid SQL*Plus asking a user to enter a value of a define variable if its value was not provided by the user. I.e. it helps one to implement the NVL-like behavior and set the value of a define variable to the default unless the user entered a specific value.

So, here it goes...

SQL> COLUMN 1 NEW_VALUE 1
SQL> COLUMN 2 NEW_VALUE 2
SQL> COLUMN 3 NEW_VALUE 3
SQL> COLUMN 4 NEW_VALUE 4
SQL> SELECT '' "1", '' "2", '' "3", '' "4"
  2    FROM dual
  3   WHERE ROWNUM = 0
  4  /

no rows selected

SQL> DEFINE 1
DEFINE 1               = "1" (CHAR)
SQL> DEFINE 2
DEFINE 2               = "2" (CHAR)
SQL> DEFINE 3
DEFINE 3               = "" (CHAR)
SQL> DEFINE 4
DEFINE 4               = "" (CHAR)
SQL> 
SQL> REM ...but...
SQL> 
SQL> DEFINE 5
SP2-0135: symbol 5 is UNDEFINED

Copy it into a sql file and execute like @sql_file.sql first second and you'll see that value of &1 is first, value of &2 is second, but both &3 and &4 are set to NULL (default).