Spice up your SQL Scripts with Variables

Everyone knows the basic features of sql*plus, but one underused feature that can make your scripts an order of magnitude more useful is the ability to store and reuse values, including values read from the database, in variables. This lets you use user-defined and database values not just in subsequent queries, but also in calls to other scripts and SQL*Plus's other functionality.

The basics: & and &&

Just about everyone has defined a variable in SQL*Plus using &. The basics:

• & and && indicate substitution variables in SQL*Plus scripts or commands.
• &variablename is used to refer to the variable variablename in SQL*Plus, much the same way $variablename is used in shell scripts. If variablename is undefined, then SQL*Plus prompts the user for a value.
• Everything from the & to the following space is considered the variable name. For example, in select &foo from dual;, &foo refers to the variable foo.

A basic example:

SQL> select owner from dba_tables where table_name='&FRED'
SQL> /
Enter value for fred: DUAL
old   1: select owner from dba_tables where table_name='&FRED'
new   1: select owner from dba_tables where table_name='DUAL'
OWNER
------------------------------
SYS
1 row selected.

The first difference you'll notice between & and && is that when SQL*Plus encounters a variable defined with &&, it prompts you for the value and then uses this value for every subsequent occurrence of that variable it encounters. The variable and its value are stored. When you define a variable with &, however, SQL*Plus discards the variable and its value immediately after use, so that repeated use of &variablename results in repeated prompts for the value of variablename. An example of each:

SQL> select count(*) from dba_tables where owner='&OWN';   
Enter value for own: PHYSICS
old   1: select count(*) from dba_tables where owner='&OWN'
new   1: select count(*) from dba_tables where owner='PHYSICS'

  COUNT(*)
----------
        47

1 row selected.

SQL> select count(*) from dba_indexes where owner='&OWN';
Enter value for own: MAGNETS
old   1: select count(*) from dba_indexes where owner='&OWN'
new   1: select count(*) from dba_indexes where owner='MAGNETS'

  COUNT(*)
----------
       208

1 row selected.

SQL> select count(*) from dba_segments where owner='&&OWN';
Enter value for own:  WIRE  
old   1: select count(*) from dba_segments where owner='&&OWN'
new   1: select count(*) from dba_segments where owner='WIRE'

  COUNT(*)
----------
       328

1 row selected.

SQL> select count(*) from dba_extents where owner='&OWN';
old   1: select count(*) from dba_extents where owner='&OWN'
new   1: select count(*) from dba_extents where owner='WIRE'

  COUNT(*)
----------
      2600

1 row selected.

In the above example, note that after the variable owner has been defined by using &&owner in a SQL statement, &owner is used to refer to that variable. If a variable variablename is already defined, using &variablename doesn't cause SQL*Plus to prompt for a value; SQL*Plus only prompts for a value when &variablename is undefined. And if &variablename was undefined, then after SQL*Plus has used the value you enter in the SQL statement or PL/SQL block that used &variablename, &variablename is discarded.

Also, note that if you use a previously undefined variable inside a loop, you'll only be prompted for the value once: SQL*Plus does the variable substitution before sending the PL/SQL block to the database engine for parsing and execution.

Power user tips for & and &&

Most DBAs have used & and && more times than they can count, but there are a couple of features that aren't widely known.

Passing arguments to your scripts with &n

In DOS scripts, %1, %2 and so on refer to the first, second, etc. values you list on the command line after invoking the script. Likewise, &1, &2, etc. refer to the first, second, etc. values you list on the command line after invoking a SQL*Plus script. As a quick example, consider this listing for a script named tablst.sql :

select table_name from user_tables 
where table_name like upper('&1%')
/

You could then get a list of all the tables you own starting with, for example, REF by typing @tablst ref at the SQL prompt:

SQL> @tablst ref
SQL> select table_name from user_tables
  2  where table_name like  upper ('&1%')
  3  /
old   2: where table_name like  upper ('&1%')
new   2: where table_name like  upper ('ref%')

TABLE_NAME
------------------------------
REF_BANJO_TYPES
REF_FREE_PIPE_CODES
REF_GUITAR_STATUS
REF_GUITAR_TYPE

4 rows selected.

Note that &1, &2 and so on are not discarded after your script has run. You can refer to &1, &2 and so on in subsequent queries until they are either redefined by running another script with arguments, or undefined (see below).

Ending the variable name

Have you ever wanted to embed an & variable in a string? For example, you might want to set the SQL prompt to a combination of a variable plus some fixed characters like SQL>. But if you try to append your characters to the variable name, SQL*Plus will simply interpret it as a new variable name.

This example shows how to set the SQL prompt:

SQL> set sqlp 'MyPrompt> '
MyPrompt> 

Now, what if I want to prompt for the user's name, like Fred, and set the prompt to FredPrompt> ? Simply stuffing the variable in there won't do:

MyPrompt> set sqlp '&EnterNamePrompt> '
Enter value for enternameprompt: oops
oops> 

And neither will concatenating, since SQL*Plus doesn't accept concatenation for the values passed to its SET commands:

oops> set sqlp '&EnterName' || 'Prompt> '
Enter value for entername: fred
SP2-0158: unknown SET option "||"
fred

The power user tip to remember here is that variable names can be terminated by a period. Simply tuck a period between your variable name and the succeeding text:

SQL> set sqlp '&EnterName.Prompt> '
Enter value for entername: Fred
FredPrompt>

Getting rid of 'em

If you've defined a variable using &&, recall that SQL*Plus will not prompt you for the value of this variable again -- even if it's defined in a script, and you run the script a second time. Consider this listing for a script called tabcounter.sql:

set echo off
prompt TABLE COUNT
select count(*) from all_tables where owner='&&owner'
/
prompt INDEX COUNT
select count(*) from all_indexes where owner='&owner'
/

The first time we run it, all is well.

SQL> @tabcounter
TABLE COUNT
Enter value for owner: SCOTT
old   1: select count(*) from all_tables where owner='&&owner'
new   1: select count(*) from all_tables where owner='SCOTT'

  COUNT(*)
----------
       441

1 row selected.

INDEX COUNT
old   1: select count(*) from all_indexes where owner='&owner'
new   1: select count(*) from all_indexes where owner='SCOTT'

  COUNT(*)
----------
       142

1 row selected.

But what if we want to run it again for a different user? Notice that when we run the script a second time, we're not prompted for the value of owner, because the variable is already defined. owner is "stuck" with the first value we passed in!

SQL> @tabcounter
TABLE COUNT
old   1: select count(*) from all_tables where owner='&&owner'
new   1: select count(*) from all_tables where owner='SCOTT'

  COUNT(*)
----------
       441

1 row selected.

INDEX COUNT
old   1: select count(*) from all_indexes where owner='&owner'
new   1: select count(*) from all_indexes where owner='BULKLOAD'

  COUNT(*)
----------
       142


1 row selected.

The solution is to undefine the variable with the UNDEFINE (or UNDEF) command:

SQL> undef owner
SQL> @tabcounter
TABLE COUNT
Enter value for owner: BULKLOAD
old   1: select count(*) from all_tables where owner='&&owner'
new   1: select count(*) from all_tables where owner='BULKLOAD'

  COUNT(*)
----------
       441

1 row selected.

INDEX COUNT
old   1: select count(*) from all_indexes where owner='&owner'
new   1: select count(*) from all_indexes where owner='BULKLOAD'

  COUNT(*)
----------
       142

1 row selected.

It's good practice to UNDEF any variables you've declared at the end of the script you declared them in. If you're running someone else's script and they haven't undefined their variables, you can list all the currently defined variables with the command DEFINE (or DEF):

SQL> define
DEFINE _CONNECT_IDENTIFIER = "test" (CHAR)
DEFINE _SQLPLUS_RELEASE = "902000100" (CHAR)
DEFINE _EDITOR         = "pico" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE      = "1002000100" (CHAR)
DEFINE _RC             = "0" (CHAR)
DEFINE OWNER           = "SCOTT" (CHAR)
SQL> 

(Note that this also lists sql*plus environment variables, which start with an underscore.)

Summary of & and &&

To summarize what we've seen so far:

&	Refer to a defined variable. If variable is undefined: define variable, prompt for its value, and undefine it.
&&	Define variable and prompt for its value at first occurrence; keep variable and its value
&n	Value of nth string on the command line following the call to the script
.	Terminate a variable name
UNDEF	Un-define variable

(DEFINE will be treated more fully below.)

Advanced variable use

&&-defined variables that you're automatically prompted to populate are fine for simple scripts, but for anything more complex, we need to be able to populate variables based on what happens in the script -- for example, to take a value from the database and put it in a variable.

Defining your variables

Variables don't have to be defined with & or &&, which prompt for input at least once. There are several other ways to define variables in sql*plus:

• Explicitly using the DEFINE command
• Explicitly using the ACCEPT command
• Implicitly as a COLUMN value

Define with DEFINE

DEFINE, which we saw above as a means to list the values of variables, can also be used to define variables and assign CHAR values to them.

SQL> DEF owner=SPARKY
SQL> select '&owner' from dual;
old   1: select '&owner' from dual
new   1: select 'SPARKY' from dual

'SPARK
------
SPARKY

1 row selected.

And in addition to listing all variables, you can use DEFINE to show the value and type of individual variables:

SQL> def owner
DEFINE OWNER           = "SPARKY" (CHAR)

Define with ACCEPT

This command is like the big brother to &&. If "Enter value for Owner:" isn't elegant enough, or explanatory enough, for your script, you can bypass && in favor of the ACCEPT command, which lets you specify a custom prompt:

SQL> accept userid number prompt 'Enter Oracle user#:'
Enter Oracle user#:1

You can also set defaults using ACCEPT:

SQL> undef username     
SQL> accept username default SCOTT prompt 'Enter the Oracle username of the index owner:'
Enter the Oracle username of the index owner:
SQL> def username
DEFINE USERNAME        = "SCOTT" (CHAR)
SQL> 

You can even validate the input using the FORMAT clause. You can supply a NUMBER, CHAR or DATE format model and sql*plus will check the user's input against it:

SQL> accept birthday date for 'DD-MON-RR' prompt 'When''s your birthday?'
When's your birthday?09/11/1969
SP2-0685: The date "09/11/1969" is invalid or format mismatched "DD-MON-RR"
When's your birthday?09-NOV-69
SQL> def birthday
DEFINE BIRTHDAY        = "09-NOV-69" (CHAR)
SQL> select to_date('&birthday','DD-MON-RR') from dual;
old   1: select to_date('&birthday','DD-MON-RR') from dual
new   1: select to_date('09-NOV-69','DD-MON-RR') from dual

TO_DATE('0
----------
11/09/1969

1 row selected.

Note that we needed to tell the ACCEPT command the type of format clause we were using, "date":

SQL> accept birthday date for 'DD-MON-RR' prompt 'When''s your birthday?'

The default is CHAR, and if you pass a non-CHAR format clause without specifying the type, you'll get an error:

SQL> accept birthday for 'DD-MON-RR' prompt 'What''s your birthday?'
SP2-0597: "DD-MON-RR" is not a valid CHAR format

Implicitly define with COLUMN

The COLUMN command is sql*plus's convoluted way of letting you read values from the database into your variables. If you use a previously undefined variable in the COLUMN command, it's implicitly defined. We'll cover the COLUMN command below.

Reading values from the database: COLUMN

You may know the COLUMN command as a way to format the screen display of resultset columns:

SQL> col uname for a30 
SQL> select user as uname from dual;

UNAME
------------------------------
SCOTT

1 row selected.

But in addition to allowing you to specify a column's display attributes, the COLUMN command has two extremely useful parameters, NEW_VALUE and OLD_VALUE. Both take a variable. When a query using the COLUMN named in the column command is executed, the variable you specified for NEW_VALUE or OLD_VALUE is defined (if it was previously undefined) and filled with the value in that column.

In the following example, we'll specify a format for the column label "uname" and specify that its NEW_VALUE should go to the variable myusername. Then, when we issue a query that has a column called "uname" in the resultset, the variable myusername will be populated with its value.

SQL> col uname for a30 new_value myusername
SQL> def myusername
SP2-0135: symbol myusername is UNDEFINED
SQL> select user as uname from dual;

UNAME
------------------------------
SCOTT

1 row selected.

SQL> def myusername
DEFINE MYUSERNAME         = "SCOTT" (CHAR)

If there's more than one row in your resultset, then the value will be taken from the last row in the resultset.

OLD_VALUE vs. NEW_VALUE

OLD_VALUE acts very similarly to NEW_VALUE:

SQL> col uname for a30 old_value myusername
SQL> select user as uname from dual;

UNAME
------------------------------
SCOTT

1 row selected.

SQL> def myusername
DEFINE MYUSERNAME         = "SCOTT" (CHAR)

The difference between the two only really comes up when you're using them for their intended purpose, to help you put page headers and footers on sql*plus reports. The current value of NEW_VALUE and OLD_VALUE can be displayed in report headers (TTITLE, REPHEADER) and footers (BTITLE, REPFOOTER). NEW_VALUE variables hold data from the new row about to be printed on the page; OLD_VALUE variables hold data from the old row that was most recently printed on the page. Thus, NEW_VALUE is useful for the report header, OLD_VALUE for the report footer. When you're just using NEW_VALUE and OLD_VALUE to get values from the database into script variables, either NEW_VALUE or OLD_VALUE will do.

Clearing it out

Like variables, all the formatting, NEW_VALUEs and OLD_VALUEs that you've put on column aliases will stick around after your query has executed. If you later run a query whose resultset has a column name that you'd previously defined, all the old formatting you previously specified will apply and your variables will be set to new values. So it's a good idea to clear out your columns:

SQL> clear columns
columns cleared

Note that this doesn't undefine the variables you used for NEW_VALUE or OLD_VALUE. They are still there, but are no longer populated by subsequent queries using the column_name:

SQL> clear columns
columns cleared
SQL> def myusername
DEFINE MYUSERNAME      = "SCOTT" (CHAR)
SQL> select count(*) as uname from dual;

     UNAME
----------
         1

1 row selected.

SQL> def myusername
DEFINE MYUSERNAME      = "SCOTT" (CHAR)
SQL> 

Summary of advanced commands

`

DEF variablename=value	Define variable variablename, if not already defined, and set its value to value
DEF variablename	Show the value and type of variablename
DEF	Show the value and type of all currently defined variables
UNDEF variablename	Undefine variablename
ACCEPT variablename [type] [FORMAT format] [DEFAULT defaultvalue] [PROMPT prompt_text | NOPROMPT]	Define variablename, if not already defined, and read a line of input into it. Prompt user for that input with prompt_text, if supplied, and check the user's input againt FORMAT if supplied.
COLUMN columnname [ NEW_VALUE variablename | OLD_VALUE variablename ]	When a query with a column named columnname in its resultset is run, defines variablename and puts the column value in variablename
CLEAR COLUMNS	Clears formatting and NEW_VALUE / OLD_VALUE on columns. Does not undefine any variables.

Manipulating variables

sql*plus variables don't have to be CHAR. Setting the TYPE in an ACCEPT command to NUMBER, or selecting a NUMBER into a column with NEW_VALUE set, allows you to set a number variable. You can then perform any arithmetic or numerical function that SQL can handle on that number by selecting that function from DUAL. In the following example, we'll get the count of the current user's sequences and add 12 to that number:

SQL> col mynum new_value mynum
SQL> select count(*) as mynum from user_sequences ;

     MYNUM
----------
        11

11 rows selected.

SQL> def mynum
DEFINE MYNUM           =         11 (NUMBER)
SQL> select &mynum+12 as mynum from dual;
old   1: select &mynum+12 as mynum from dual
new   1: select         11+12 as mynum from dual

     MYNUM
----------
        23

1 row selected.

SQL> def mynum
DEFINE MYNUM           =         23 (NUMBER)
SQL>

Script: Improve your SQL prompt

When sql*plus opens, it runs the script $ORACLE_HOME/sqlplus/admin/glogin.sql . You can modify this script to run your own additional commands on start-up. The following script should be added into glogin.sql before the Oracle-supplied commands in glogin.sql, because it issues CLEAR COLUMNS to clear the columsn it uses, and the Oracle-supplied glogin.sql sets column defaults for sql*plus features. The following script also sets echo, feedback, verify and heading off; if you want any of these on in your sql*plus session, add code to turn them on after the Oracle-supplied commands, as shown below.

--
-- Copyright (c) Oracle Corporation 1988, 2000.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login startup file.
--
--   Add any sqlplus commands here that are to be executed when a user
--   starts SQL*Plus on your system
--
-- USAGE
--   This script is automatically run when SQL*Plus starts
--

--Added by NROSHAK

set echo off
set feedback off
set verify off
set head off
accept promptchar for a1 default '>' prompt 'What character would you like to prompt with today? ' 
col user new_value uname
set termout off
select user from dual;
col global_name new_value gname
select global_name from global_name;
set termout on
clear columns
def promptstr=&uname.@&gname.&promptchar
set sqlp '&promptstr '
undef promptchar
undef promptstr
undef uname
undef gname

--End added by NROSHAK

[... oracle supplied glogin.sql commands ...]

--Added by NROSHAK

set feedback on
set verify on
set head on
set echo on

--End added by NROSHAK

When sql*plus starts up, it prompts for the prompt character and then sets the SQL prompt to user@database:

sandbox1:~ oracle$ sqlplus

SQL*Plus: Release 9.2.0.1.0 - Developer's Release on Tue Nov 1 14:56:55 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: scott@test
Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

What character would you like to prompt with today? $
$ 

In the foregoing, we used:

• ACCEPT to get the prompt character (supplied as $ above),
• COLUMN NEW_VALUE to read the username and global_name from the database (&uname and &gname),
• DEFINE and '.' to create the prompt string (&promptstr), and
• CLEAR COLUMNS and UNDEFINE to clean up our columns and variables.

Note that this prompt is not dynamic; if you connect as a different user, your prompt will not change. In 10g, you can set the SQL prompt to follow the value of a variable dynamically, rather than statically to the current value as we have here. See the documentation
here for an example.

Conclusion

SQL*Plus scripts can be much more than just strings of SQL statements with the occasional substitution. Using sql*plus variables, you can accept values from the user, prompt for and check those values, set variables based on the value of other variables, and store values from the database in variables. These are the building blocks of scripts an order of magnitude more powerful and complex than simple sequences of statements.

Further reading

• Displaying column values in titles
• sql*plus SET command (10g)
• sql*plus SET command (9i)

Natalka Roshak is an Oracle database administrator, architect, and analyst based in Ontario, Canada.