Using Variables

In this section, you ll see how to create variables that may be used in place of actual values in SQL statements. These variables are known as substitution variables because they are used as substitutes for values. When you run your SQL statement, you enter values for your variables and those values are then substituted into your SQL statement.

There are two basic types of variables you can use in SQL*Plus:

Temporary variables A temporary variable is only valid for the SQL statement in which it is used and doesn t persist.
Defined variables A defined variable persists until you explicitly remove it, redefine it, or exit SQL*Plus.

You ll learn how to use these types of variables in this section.

Why Are Variables Useful?

Variables are useful because you can create scripts that a user who doesn t know SQL can run. Your script would prompt the user to enter the value for a variable and use that value in a SQL statement. Let s take a look at an example.

Suppose you wanted to create a script for a user who doesn t know SQL, but who wants to see the details of a single specified product in the store. To do this, you could hard code the product_id value in the WHERE clause of a SELECT statement and place that SELECT statement in a SQL*Plus script. For example, the following SELECT statement retrieves product #1:

 SELECT product_id, name, price FROM products WHERE product_id = 1;

This query works, but it only retrieves that one product. What if you wanted to change the product_id value to retrieve a different row? You could modify the script, but this would be tedious . Wouldn t it be great if you could supply a variable for the product_id column in the WHERE clause when the query is actually run? A variable would enable you to write a general SQL statement that would work for any product, and the user would simply enter the value for that variable.

Temporary Variables

You define a temporary variable using the ampersand character (&) in a SQL statement, followed by the name you want to call your variable. For example, & product_id_var defines a variable named product_id_var .

When you run the following SELECT statement, SQL*Plus prompts you to enter a value for product_id_var and then uses that variable s value in the WHERE clause of the SELECT statement. If you enter the value 2 for product_id_var , the details for product #2 will be displayed.

 SQL>  SELECT product_id, name, price  2  FROM products  3  WHERE product_id =  &  product_id_var;  Enter value for product_id_var:  2  old 3: WHERE product_id = &product_id_var new 3: WHERE product_id = 2 PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  2 Chemistry 30

Notice SQL*Plus does the following:

Prompts you to enter a value for product_id_var .
Substitutes the value you entered for product_id_var in the WHERE clause.

SQL*Plus shows you the substitution in the old and new lines in the output, along with the line number in the query where the substitution was performed. In the previous example, you can see that the old and new lines indicate that product_id_var is set to 2 in the WHERE clause of the SELECT statement.

If you rerun the query using the slash character ( /) , SQL*Plus will ask you to enter a new value for product_id_var . For example:

 SQL>  /  Enter value for product_id_var:  3  old 3: WHERE product_id = &product_id_var new 3: WHERE product_id = 3 PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  3 Supernova 25.99

Once again, SQL*Plus echoes the old line of the SQL statement ( old 3: WHERE product_id = & product_id_var ) followed by the new line containing the variable value you entered ( new 3: WHERE product_id = 3 ).

Controlling Output Lines

You may control the output of the old and new lines using the SET VERIFY command. If you enter SET VERIFY OFF , the old and new lines are suppressed. For example:

 SQL>  SET VERIFY OFF  SQL>  /  Enter value for product_id_var:  4  PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  4 Tank War 13.95

To turn the echoing of the lines back on, you enter SET VERIFY ON . For example:

 SQL>  SET VERIFY ON

Changing the Variable Definition Character

You can use the SET DEFINE command to specify a character other than ampersand (&) for defining a variable. The following example shows how to set the variable character to the pound character (#) using SET DEFINE and shows a new SELECT statement:

 SQL>  SET DEFINE '#'  SQL>  SELECT product_id, name, price  2  FROM products  3  WHERE product_id = #product_id_var;  Enter value for product_id_var:  5  old 3: WHERE product_id = #product_id_var new 3: WHERE product_id = 5 PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  5 Z Files 49.99

The next example uses SET DEFINE to change the character back to an ampersand:

 SQL>  SET DEFINE '  &  '

Substituting Table and Column Names Using Variables

You re not limited to using variables to substitute column values: you can also use variables to substitute the names of tables and columns . For example, the following query defines variables for you to enter a column name ( col_var ) or table name ( table_var ), as well as a column value ( col_val_var ):

 SQL>  SELECT name,  &  col_var  2  FROM  &  table_var  3  WHERE  &  col_var =  &  col_val;  Enter value for col_var:  product_type_id  old 1: SELECT name, &col_var new 1: SELECT name, product_type_id Enter value for table_var:  products  old 2: FROM &table_var new 2: FROM products Enter value for col_var:  product_type_id  Enter value for col_val:  1  old 3: WHERE &col_var = &col_val new 3: WHERE product_type_id = 1 NAME PRODUCT_TYPE_ID ------------------------------ --------------- Modern Science 1 Chemistry 1

You can avoid having to repeatedly enter a variable by using &&. For example:

 SELECT name, &&col_var FROM &table_name WHERE &&col_var = &col_val;

Being able to supply column and table names, as well as variable values, gives you a lot of flexibility in writing interactive queries that another user may run. That user doesn t need to write the SQL: you can simply give them a script and have them enter the variable values for the query.

Defined Variables

You can define a variable prior to using that variable in a SQL statement. You may use these variables multiple times within a SQL statement. A defined variable persists until you explicitly remove it, redefine it, or exit SQL*Plus.

You define a variable using the DEFINE command. When you re finished with your variable, you remove it using UNDEFINE . You ll learn about each of these commands in this section. You ll also learn about the ACCEPT command, which allows you to define a variable and specify a data type for that variable.

You can also define variables in a SQL*Plus script and pass in values to those variables when you run the script. This enables you to write generic reports that any user can run ”even if they re unfamiliar with SQL. You ll learn how to create simple reports in the section Creating Simple Reports .

Defining and Listing Variables Using the DEFINE Command

You use the DEFINE command to both define a new variable and list the currently defined variables. The following example defines a variable named product_id_var and sets its value to 7:

 SQL>  DEFINE product_id_var = 7

You can view the definition of a variable using the DEFINE command followed by the name of the variable. The following example displays the definition of product_id_var :

 SQL>  DEFINE product_id_var  DEFINE PRODUCT_ID_VAR = "7" (CHAR)

Notice that product_id_var is defined as a CHAR variable.

You can see all your session variables by entering DEFINE on its own line. For example:

 SQL>  DEFINE  ... DEFINE PRODUCT_ID_VAR = "7" (CHAR)

You can use a defined variable to specify an element such as a column value in a SQL statement. For example, the following query uses the variable product_id_var defined earlier and references its value in the WHERE clause:

 SQL>  SELECT product_id, name, price  2  FROM products  3  WHERE product_id =  &  product_id_var;  old 3: WHERE product_id = &product_id_var new 3: WHERE product_id = 7 PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  7 Space Force 9 13.49

Notice that you re not prompted to the value of product_id_var; that s because product_id_var was set to 7 when the variable was defined earlier.

Defining and Setting Variables Using the ACCEPT Command

The ACCEPT command waits for a user to enter a value for a variable. You can use the ACCEPT command to set an existing variable to a new value, or to define a new variable and initialize it with a value. The ACCEPT command also allows you to specify the data type for your variable. The simplified syntax for the ACCEPT command is as follows :

 ACCEPT  variable_name  [  type  ] [FORMAT  format  ] [PROMPT  prompt  ] [HIDE]

where

variable_name specifies the name assigned to your variable.
type specifies the data type for your variable. You can use the CHAR , NUMBER , and DATE types. By default, variables are defined using the CHAR type. DATE variables are actually stored as CHAR variables.
format specifies the format used for your variable. Some examples include A15 (15 characters ), 9999 (a four-digit number), and DD-MON-YYYY (a date). You can view the number formats in Table 3-4 of Chapter 3; you can view the date formats in Table 4-2 of Chapter 4.
prompt specifies the text displayed by SQL*Plus as a prompt to the user to enter the variable s value.
HIDE indicates the value entered for the variable is to be hidden. For example, you might want to hide passwords or other sensitive information. Hidden values are displayed using asterisks (*) as you enter the characters.

Let s take a look at some examples of the ACCEPT command. The first example defines a variable named customer_id_var as a two-digit number:

 SQL>  ACCEPT customer_id_var NUMBER FORMAT 99 PROMPT 'Customer id: '  Customer id:  5

The next example defines a DATE variable named date_var; the format for this DATE is DD-MON-YYYY :

 SQL>  ACCEPT date_var DATE FORMAT 'DD-MON-YYYY' PROMPT 'Date: '  Date:  12-DEC-2006

The next example defines a CHAR variable named password_var; the value entered is hidden using the HIDE option:

 SQL>  ACCEPT password_var CHAR PROMPT 'Password: ' HIDE  Password:  *******

In Oracle9 i and below, the value entered appears as a string of asterisk characters (*) to hide the value as you enter it. In Oracle10 i , nothing is displayed as you type the value.

You can view your variables using the DEFINE command. For example:

 SQL>  DEFINE  ... DEFINE CUSTOMER_ID_VAR = 5 (NUMBER) DEFINE DATE_VAR = "12-DEC-2006" (CHAR) DEFINE PASSWORD_VAR = "1234567" (CHAR)

Notice that date_var is stored as a CHAR .

Removing Variables Using the UNDEFINE Command

You remove variables using the UNDEFINE command. The following example uses UNDEFINE to remove customer_id_var , date_var , and password_var :

 SQL>  UNDEFINE customer_id_var  SQL>  UNDEFINE date_var  SQL>  UNDEFINE password_var

Note	All your variables are removed when you exit SQL*Plus, even if you don t explicitly remove them using the UNDEFINE command.