Wednesday, April 09, 2008

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 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> SELECT '' "1", '' "2", '' "3", '' "4"
  2    FROM dual
  3   WHERE ROWNUM = 0
  4  /

no rows selected

DEFINE 1               = "1" (CHAR)
DEFINE 2               = "2" (CHAR)
DEFINE 3               = "" (CHAR)
DEFINE 4               = "" (CHAR)
SQL> REM ...but...
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).


Osymad said...

Thanks, but only works if you have an active connection. What if parameter is used to pass user@connection to another sql script?

Anonymous said...

This is a great technique, but it is not clear from your example why sub_var1 got a value of 1 and sub_var2 got a value of 2 as shown with your DEFINE statements. Based on your select from dual, all four should initally be NULL ( "" )

Toleg said...

Thank you very much for this cool trick!
I used it already tons of times and it's very very helpful

p/s initially I read this from you blog. 2nd time found reference to this post from another site

Vladimir Begun said...

you are welcome

Mustafa DOĞANAY said...

Hi, Pavel Luzanov blog ( is not reachable. Are there any other link for those old sqlplus posts?

Vladimir Begun said...

Unfortunately, I don't have it handy. I will ask Pavel when I have a chance.