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 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).

8 comments:

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 (http://www.geocities.com/luzanovp/) 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.

dan said...

I was ultimately lead here trying to figure out how to have a default for a substitution parameter.

With Vladimir's help I ended up with this:

/*
Allows a default value to be used for a substitution parameter
*/

SET VERIFY OFF
SET TERMOUT ON
SET SERVEROUTPUT ON;

-- < 1 prevents sqlplus from prompting for a value when it's not provided
column 1 new_value 1
SELECT '' "1" FROM dual WHERE ROWNUM = 0;
-- </ 1

-- define a bind parameter, we'll ultimately use this for any logic
variable some_input varchar2(20)

-- assign the bind parameter to be either an input (if not null) or a default (if null)
execute :some_input := nvl('&1', 'default');

-- test
select concat('input : ', :some_input) from dual;

Joel Ericson said...

Hm, "where rownum = 0" is a strange beast indeed. It seems to always insert null into the undefined variables regardless of which values I provide for the columns.
Anyone knows why?

Anyhow, my take on the thing:
Since scope is something that apparently simply does not exist, I simply created a
defaults.sql:
--------------
set echo off
set verify off
set feedback off

-- Assign null to undefined parameters
column a new_v 1
column b new_v 2
column c new_v 3
column d new_v 4
column e new_v 5
column f new_v 6
column g new_v 7
column h new_v 8
select '' a,'' b, '' c, '' d, '' e, '' f, '' g, '' h from dual where rownum=0;
--------------

And then I simply call it in the beginning of my other files.
foo.sql
--------------
@defaults
-- now &1-&8 either have a value or is null.
...
--------------