Bind Variables

Back in Chapter 8, you learned about substitution variables. SQL*Plus supports another type of variable called a bind variable . Unlike substitution variables, bind variables are real variables, having a datatype and a size .

Bind variables were created to support the use of PL/SQL in a SQL*Plus script. They provide a mechanism for returning data from a PL/SQL block back to SQL*Plus, where that data can be used in subsequent queries or by other PL/SQL blocks. Example 11-1 provides a simple script showing how a bind variable can be used.

Example 11-1. Bind variables can be used to transfer data among PL/SQL blocks and SQL queries

--Bind variables can be declared in your SQL*Plus script. VARIABLE s_table_name varchar2(30) --Preface a bind variable with a colon to reference it --in a PL/SQL block. BEGIN :s_table_name := 'EMPLOYEE'; END; / --Bind variables can even be referenced by SQL queries. SELECT index_name FROM user_indexes WHERE table_name = :s_table_name; --Bind variables persist until you exit SQL*Plus, so --they can be referenced by more than one PL/SQL block. SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.PUT_LINE(:s_table_name); END; /

The scope of a bind variable is the SQL*Plus session in which it is defined. Variables defined within a PL/SQL block, on the other hand, cease to exist once that block has finished executing. Bind variables are defined one level higher (at the SQL*Plus level), so they can be referenced by many PL/SQL blocks and queries.

11.1.1 Declaring Bind Variables

You use the SQL*Plus VARIABLE command to declare bind variables. The syntax looks like this:

VAR[IABLE] var_name data_type

Bind variable datatypes correspond to database datatypes. Not all datatypes are supported, but the most commonly used ones are. Example 11-1 has shown one way to declare character variables:

VARIABLE s_table_name VARCHAR2(30)

You can declare numeric variables using the NUMBER datatype:

VAR billing_rate NUMBER

When declaring NUMBER bind variables, you can't specify precision and scale. However, a variable of type NUMBER can accommodate values of any precision and scale, so the inability to specify a specific precision and scale presents no problem in practice.

Other datatypes are supported. REFCURSOR is a useful bind variable type that you'll read more about later in this chapter. The new, numeric types (BINARY_FLOAT and BINARY_DOUBLE) are supported if you are running a version of SQL*Plus corresponding to a release of the database software that supports those types.

In addition to declaring variables, you can use the VARIABLE command to list all of the variables you have defined. To do that, issue the command VARIABLE (which may be abbreviated VAR), with no arguments, as shown in the following example:

SQL> VAR variable s_table_name datatype VARCHAR2(30) variable billing_rate datatype NUMBER

If you are interested in one specific variable, you can specify that variable's name as an argument to the VARIABLE command:

SQL> VAR billing_rate variable billing_rate datatype NUMBER

There is no way to get rid of a variable once you have defined it.

11.1.2 Using Bind Variables and Substitution Variables Together

Bind variables and substitution variables don't mesh together well in SQL*Plus. Each was created for a different purpose, and the two types can't be used interchangeably. For example, bind variables can't be used with the ACCEPT command, but substitution variables can. Substitution variables can be used with the TTITLE and BTITLE commands that set up page headers and footers, but bind variables cannot. Bind variables are true variables and can be passed as arguments to PL/SQL functions and procedures, but substitution variables cannot. Table 11-1 summarizes the best uses and capabilities of each type of variable.

Table 11-1. Bind variables versus substitution variables

Each variable type, bind and substitution, exists in its own world, separate from the other. In fact, you can't even directly assign values from a bind variable to a substitution variable, or vice versa. The lines of script shown in Example 11-2, although appearing perfectly reasonable on the surface, will not work.

Example 11-2. You cannot directly place a bind variable value into a substitution variable

DEFINE my_sub_var = ' ' VARIABLE my_bind_var VARCHAR2(30) EXECUTE :my_bind_var := 'Donna Gennick' my_sub_var = my_bind_var

This lack of interoperability between variable types can be a source of frustration when writing scripts. As Table 11-1 shows, you can only use a bind variable for some tasks ; for others, you can only use a substitution variable. Yet SQL*Plus doesn't let you move values between the two types. Fortunately, some relatively straightforward incantations let you work around this problem.

11.1.2.1 From substitution to bind

Putting the value of a substitution variable into a bind variable is the easier of the two tasks. Remember that as SQL*Plus executes your script, any substitution variables are simply replaced by their contents as each line of code is executed. You can take advantage of this to place a value into a bind variable. Take a look at the short script in Example 11-3.

Example 11-3. Assigning a substitution variable value to a bind variable

DEFINE my_sub_var = 'Mykola Leontovych' VARIABLE my_bind_var VARCHAR2(30) EXECUTE :my_bind_var := '&my_sub_var';

EXECUTE is a command that executes one line of PL/SQL code. When SQL*Plus encounters the EXECUTE command in Example 11-3, it replaces the reference to the substitution variable with the value of that variable. The command after substitution, the one that is executed, looks like this:

EXECUTE :my_bind_var := 'Mykola Leontovych';

The EXECUTE command is a SQL*Plus command. What gets sent to the database is a PL/SQL block:

BEGIN :my_bind_var := 'Mykola Leontovych'; END;

Because the assignment involves a character string, the substitution variable must be contained in quotes; otherwise , you would not have a valid string. If you are working with numeric values, you shouldn't quote them. Example 11-4 declares a variable of type NUMBER and assigns a value to it.

Example 11-4. Assigning a "numeric" value from a substitution variable to a bind variable

DEFINE my_sub_num = 9 VARIABLE my_bind_num NUMBER EXECUTE :my_bind_num := &my_sub_num;

The EXECUTE command that SQL*Plus executes from Example 11-4 looks like this:

EXECUTE :my_bind_num := 9;

So, quote your strings, don't quote your numbers , and remember that substitution is occurring.

11.1.2.2 From bind to substitution

Taking a value from a bind variable and placing it into a substitution variable is a more difficult task. What you need to do is take advantage of SQL*Plus's ability to store the result of a SELECT statement into a substitution variable. Let's say you have the following in your script:

DEFINE my_sub_var = ' ' VARIABLE my_bind_var VARCHAR2(35) EXECUTE :my_bind_var := 'Brighten the corner where you are';

To get the value of the bind variable into the substitution variable, you need to follow these steps:

1. Think up a column name.
2. Execute a COLUMN command for the column name you thought up. Use the NEW_VALUE clause and specify the substitution variable as the target.
3. Turn off terminal output by executing a SET TERMOUT OFF command. This is optional.
4. Issue a SELECT statement that selects the bind variable from Oracle's dual table. Use the column name you thought up in step 1 as the column alias.
5. Turn terminal output back on.

The SELECT statement will return only one value, but that value will be a new value for the column in question. The COLUMN command, with its NEW_VALUE clause, causes this value to be stored in the specified substitution variable. It's a roundabout solution to the problem, but when it's all over, the substitution variable will contain the value from the bind variable. The important thing is to be sure that the column alias matches the column name used in the COLUMN command. Example 11-5 demonstrates the technique.

Example 11-5. Assigning a bind variable value to a substitution variable

--Declare one bind variable and one substitution variable. --Initialize the bind variable to a value. DEFINE my_sub_var = ' ' VARIABLE my_bind_var VARCHAR2(35) EXECUTE :my_bind_var := 'Brighten the corner where you are'; --Store the new value of the my_alias column in my_sub_var. COLUMN my_alias NEW_VALUE my_sub_var --SELECT the value of the bind variable. SQL*Plus --will store that value in my_sub_var because of the --previous COLUMN command. SET TERMOUT OFF SELECT :my_bind_var my_alias FROM dual; SET TERMOUT ON --Display the new value of the substitution variable DEFINE my_sub_var

Notice in Example 11-5 that a column alias is used in the SELECT statement to give the column a name. This same name must be used in the COLUMN command issued prior to the SELECT. If these two don't match, then the assignment won't be made and my_sub_var will remain blank.

Strictly speaking, it's not necessary to turn the terminal output off for the SELECT statement. The variable assignment will still be made, even with the output on. However, if you are writing a script, you probably won't want the results of this SELECT to clutter up the display.

11.1.3 Displaying the Contents of a Bind Variable

You can display the contents of a bind variable to a user by using the PRINT command, or by listing the variable in a SELECT statement.

11.1.3.1 Using the PRINT command

The PRINT command takes a bind variable name as a parameter and displays the value of that variable. The results look much like the results you get from a SELECT. Example 11-6 demonstrates.

Example 11-6. PRINTing the value of a bind variable

SQL> VAR my_bind_var VARCHAR2(35) SQL> EXECUTE :my_bind_var := 'Brighten the corner where you are'; PL/SQL procedure successfully completed. SQL> PRINT my_bind_var MY_BIND_VAR ----------------------------------------------------------------- Brighten the corner where you are

The bind variable is treated like a database column, with the variable name being the default column heading. If you have page titles defined, they will print as well. You can even use the COLUMN command to format the output. Example 11-7 shows how this works.

Example 11-7. Using the COLUMN command to format bind variable output

SQL> VAR my_bind_var VARCHAR2(35) SQL> EXECUTE :my_bind_var := 'Brighten the corner where you are'; PL/SQL procedure successfully completed. SQL> TTITLE LEFT '**********************' SKIP 1 - > 'A Song by Ina D. Ogdon' SKIP 1 - > '**********************' SKIP 2 SQL> COLUMN my_bind_var FORMAT A35 HEADING 'My Motto' SQL> PRINT my_bind_var ********************** A Song by Ina D. Ogdon ********************** My Motto ----------------------------------- Brighten the corner where you are

All other formatting options, such as PAGESIZE and LINESIZE, apply when printing bind variables. You can use the COLUMN command's NEW_VALUE clause to store the value of a bind variable into a substitution variable, as Example 11-8 shows.

Example 11-8. Using PRINT and NEW_VALUE to store a bind variable value into a substitution variable

SQL> TTITLE OFF SQL> DEFINE my_sub_var = ' ' SQL> VAR my_bind_var VARCHAR2(35) SQL> EXECUTE :my_bind_var := 'Brighten the corner where you are'; PL/SQL procedure successfully completed. SQL> COLUMN my_bind_var NEW_VALUE my_sub_var SQL> PRINT my_bind_var MY_BIND_VAR ---------------------------------------------------------------------- Brighten the corner where you are SQL> PROMPT &my_sub_var Brighten the corner where you are

Issuing the PRINT command by itself causes the contents of all bind variables to be displayed. Here's an example:

SQL> PRINT S_TABLE_NAME ---------------------------------------------------------------------- EMPLOYEE My Motto ----------------------------------- Brighten the corner where you are MY_BIND_NUM ----------- 9

Some special considerations apply when printing bind variables of type CLOB and of type REFCURSOR. These are described in the following sections.

11.1.3.2 PRINTing CLOB variables

CLOB stands for character large object, and variables of this type can hold up to two gigabytes of tex t data (even more in Oracle Database 10 g ). When printing variables of type CLOB or NCLOB, you can use three SQL*Plus settings to control what you see and how the retrieval of the CLOB data is done, as in Table 11-2.

Table 11-2. Settings that affect the printing of CLOBs

By default, SQL*Plus displays only the first 80 characters of a CLOB value. This is rarely enough characters. After all, if you needed only 80 characters you wouldn't have used a CLOB datatype in the first place. On the other hand, you may not want to risk printing two gigabytes of data either.

Example 11-9 shows the result of displaying a CLOB value using the default settings for the values in Table 11-2.

Example 11-9. PRINTing a CLOB bind variable using default settings

SQL> VARIABLE clob_bind CLOB SQL> SQL> BEGIN 2 SELECT clob_value INTO :clob_bind 3 FROM clob_example; 4 END; 5 / PL/SQL procedure successfully completed. SQL> SET LINESIZE 60 SQL> PRINT clob_bind CLOB_BIND ------------------------------------------------------------ By default, SQL*Plus will only display the first 80 characte rs of a CLOB value.

As you can see, only 80 characters of the value were displayed. Annoyingly, although you can use the COLUMN command to set the heading over a bind variable's value, any attempt to use WORD_WRAPPED to enable word wrapping is ignored. You can change the LONG setting to see more of the value, as Example 11-10 shows.

Example 11-10. Example 11-9 rerun with a longer LONG setting

SQL> SET LONG 500 SQL> @ex11-9 SQL> SET ECHO ON SQL> SQL> VARIABLE clob_bind CLOB SQL> SQL> BEGIN 2 SELECT clob_value INTO :clob_bind 3 FROM clob_example; 4 END; 5 / PL/SQL procedure successfully completed. SQL> SET LINESIZE 60 SQL> PRINT clob_bind CLOB_BIND ------------------------------------------------------------ By default, SQL*Plus will only display the first 80 characte rs of a CLOB value. This is rarely enough. After all, if you only needed 80 characters, you wouldn't have used a CLOB da tatype in the first place. On the other hand, you may not wa nt to risk printing 2 gigabytes of data either.

By combining the LOBOFFSET and LONG settings, you can print any arbitrary substring of a CLOB variable. Example 11-11 displays characters 81 through 102, which make up the second sentence of the CLOB value shown in the previous two examples.

Example 11-11. Using SET LONG and SET LOBOFFSET to display a substring of a CLOB value

SQL> SET LONG 22 SQL> SET LOBOFFSET 81 SQL> @ex11-9 SQL> SET ECHO ON SQL> SQL> VARIABLE clob_bind CLOB SQL> SQL> BEGIN 2 SELECT clob_value INTO :clob_bind 3 FROM clob_example; 4 END; 5 / PL/SQL procedure successfully completed. SQL> SET LINESIZE 60 SQL> PRINT clob_bind CLOB_BIND ---------------------- This is rarely enough.

Finally, the LONGCHUNKSIZE setting controls the amount of the CLOB fetched from the database at one time. If you have the memory available, you may want to set this to match the LONG setting. That way, SQL*Plus retrieves all that you wish to display with one fetch from the database, possibly improving performance.

11.1.3.3 PRINTing REFCURSOR variables

SQL*Plus allows you to create bind variables of the type REFCURSOR. A REFCURSOR variable is a pointer to a cursor that returns a result set. Using PL/SQL, you can assign any SELECT query to a variable of this type and then use the SQL*Plus PRINT command to format and display the results of that query. The script shown in Example 11-12 makes use of this capability by using a REFCURSOR to display a list of tables owned by the current user.

Example 11-12. PRINTing a REFCURSOR variable

SET ECHO OFF VARIABLE l_table_list REFCURSOR -- Set the REFCURSOR variable to the results of -- a SELECT statement returning a list of tables -- owned by the user. BEGIN OPEN :l_table_list FOR SELECT table_name FROM user_tables; END; / --Print the list of tables the user wants to see. PRINT l_table_list

The script in Example 11-12 defines a SQL*Plus REFCURSOR variable. The cursor is opened and a query is assigned by code within a PL/SQL block. Then the SQL*Plus PRINT command is used to display the results of that query. Following is an example run of the script:

SQL> @ex11-12 PL/SQL procedure successfully completed. TABLE_NAME ------------------------------ CLOB_EXAMPLE SUBTEST NBR_CC BILL_OF_MATERIALS PART ...

The output you get when PRINTing a REFCURSOR variable is identical to the output you would get if you executed the same query directly from SQL*Plus.

11.1.3.4 SELECTing a bind variable

The SQL*Plus manual, at least the one for Versions 8.0.3 and before, tells you that bind variables can't be used in SQL statements. Don't believe it! Bind variables can be used in SELECT statements, in the column list and in the WHERE clause. You will see this done in scripts where getting the contents of a bind variable into a substitution variable is important. (See the Section 11.1.2.) Example 11-13 shows a SELECT statement being used to display the contents of a bind variable.

Example 11-13. SELECTing the contents of a bind variable

SQL> VARIABLE employee_name VARCHAR2(30) SQL> EXECUTE :employee_name := 'Mykola Leontovych'; PL/SQL procedure successfully completed. SQL> SELECT :employee_name FROM dual; :EMPLOYEE_NAME ------------------------------------------------------------ Mykola Leontovych

Using SELECT in this way offers no real advantage over the use of the PRINT command. If you need to display one variable, you might as well PRINT it. Being able to use bind variables in a SELECT statement becomes more of an advantage when you need to display information from more than one column, when you want to use the bind variable in an expression for a computed column, or when you want to use it in the WHERE clause. Example 11-14 combines all three of these situations.

Example 11-14. A more extensive use of bind variables in a SQL statement

UNDEFINE user_name VARIABLE l_user VARCHAR2(30) EXECUTE :l_user := '&user_name'; SELECT 'User ' :l_user ' has ' TO_CHAR(COUNT(*)) ' tables.' FROM all_tables WHERE owner = UPPER(:l_user);

You run Example 11-14 as follows :

SQL> @ex11-14 Enter value for user_name: gennick PL/SQL procedure successfully completed. 'USER':L_USER'HAS'TO_CHAR(COUNT(*))'TABLES.' ------------------------------------------------------------ User gennick has 60 tables.

Two types of bind variables can't be used in a SQL statement: REFCURSOR and CLOB types. You must use the PRINT command with these bind variables.

11.1.4 When and How to Use Bind Variables

You have three primary reasons for using bind variables in SQL*Plus:

• You need to call PL/SQL procedures or functions that return a value or that use IN OUT parameters.
• You need to execute one of several possible SELECT statements conditionally depending on user input or other circumstances.
• You want to test a query for use in an application, and that query uses colons to mark parameters.

The next few sections briefly describe each of these uses.

11.1.4.1 Calling PL/SQL procedures and functions from SQL*Plus

Oracle provides a number of useful, built-in PL/SQL packages. Some of the procedures and functions in these packages return values that you may wish to capture and return to SQL*Plus. Capturing the return value from a function is usually easy because you can SELECT that function from the dual table. Example 11-15 generates a random string of alphanumerics (the "X" option) by making a call to DBMS_RANDOM.STRING from within a SELECT statement. The string is returned to SQL*Plus via the COLUMN command's NEW_VALUE mechanism.

Example 11-15. Capturing the return value of a PL/SQL function

COLUMN mixed_up NEW_VALUE mixed_up_sub_var SELECT dbms_random.string('X',30) mixed_up FROM dual; DEFINE mixed_up_sub_var

Life becomes more difficult, though, if you wish to invoke a procedure or function with an OUT or an IN OUT parameter. DBMS_UTILITY.CANONICALIZE is one such procedure:

DBMS_UTILITY.CANONICALIZE( name IN VARCHAR2, canon_name OUT VARCHAR2, canon_len IN BINARY_INTEGER);

This procedure takes an identifier, such as a table reference, and returns that identifier in canonical form. For example, pass the table name gennick."Mixed_Case_Table " and you'll get back GENNICK."mixed_case_table ". What's interesting about this procedure, and it is a procedure, is that it returns the canonical name via an OUT variable. To execute this procedure, you must send that OUT variable somewhere, and a bind variable provides a convenient destination. Example 11-16 presents a brief SQL*Plus script to invoke DBMS_UTILITY.CANONICALIZE on a string that you supply and then display the result.

Example 11-16. Capturing an OUT value into a bind variable

ACCEPT not_can CHAR PROMPT 'Enter a table reference >' VARIABLE can VARCHAR2(60) EXECUTE DBMS_UTILITY.CANONICALIZE('¬_can',:can, 60); COLUMN can HEADING 'Canonicalized Reference IS:' PRINT can

The following is a run of Example 11-16:

SQL> @ex11-16 Enter a table reference > gennick."Mixed-Case-Table" PL/SQL procedure successfully completed. Canonicalized Reference IS: ------------------------------------------------------------ "GENNICK"."Mixed-Case-Table"

Were it not for the SQL*Plus bind variable (named :can in Example 11-16), you wouldn't be able to capture the canonicalized string for later use in your SQL*Plus script.

11.1.4.2 Using REFCURSOR variables

As mentioned earlier, the REFCURSOR datatype holds a pointer to a cursor. Using REFCURSOR variables, you can open a cursor for a SELECT statement in PL/SQL and print the results from SQL*Plus. One practical use for this is to write PL/SQL code that selects one query from many possibilities, based on user input or some other factor.

Earlier, in the section on "Printing REFCURSOR variables," Example 11-12 showed the use of a REFCURSOR variable to display a list of tables owned by the current user. Example 11-17 is an enhanced version of that script that allows you to enter a pattern match string to narrow the list of table names to be displayed. The script executes one of two possible queries depending on whether or not a string was supplied.

Example 11-17. Setting a REFCURSOR to return results from one of two possible SELECT statements

SET ECHO OFF SET VERIFY OFF --Find out what tables the user wants to see. --A null response results in seeing all the tables. ACCEPT s_table_like PROMPT 'List tables LIKE > ' VARIABLE l_table_list REFCURSOR --This PL/SQL block sets the l_table_list variable --to the correct query, depending on whether or --not the user specified all or part of a table_name. BEGIN IF '&s_table_like' IS NULL THEN OPEN :l_table_list FOR SELECT table_name FROM user_tables; ELSE OPEN :l_table_list FOR SELECT table_name FROM user_tables WHERE table_name LIKE UPPER('&s_table_like'); END IF; END; / --Print the list of tables the user wants to see. PRINT l_table_list

This script first asks the user for a search string to be used with the LIKE operator. Entering this is optional. If a pattern match string is specified, then only table names that match that string are displayed; otherwise, all table names are listed. This conditional logic is implemented by the PL/SQL block, which checks the value of the substitution variable and opens the REFCURSOR variable using the appropriate SELECT statement. Here's how it looks to run the script:

SQL> @ex11-17 List tables LIKE > %emp% PL/SQL procedure successfully completed. TABLE_NAME ------------------------------ EMPLOYEE EMPLOYEES EMPLOYEE_COMMENT EMPLOYEE_COPY EMPLOYEE_EXPENSE EMPLOYEE_COMMA EMPLOYEE_FIXED

Using REFCURSOR variables is one way to add conditional logic to your SQL*Plus scripts. You'll see another example of this in Section 11.2.2.

11.1.4.3 Testing application queries

Bind variables can make it more convenient to take a query from an application development environment and debug it using SQL*Plus. Such queries often contain parameters to be supplied at runtime, and those parameters are preceded by colons, which is the syntax SQL*Plus uses for bind variables. Example 11-18 shows a query containing a bind variable in the WHERE clause.

Example 11-18. A query using a bind variable

SELECT employee.employee_id, employee.employee_name, employee.employee_hire_date, employee.employee_termination_date, employee.employee_billing_rate FROM employee WHERE employee.employee_id = :emp_id

If you want to test the query in Example 11-18 and you run it as it is, you will get the following results:

SQL> @ex11-18 SQL> SET ECHO ON SQL> SELECT employee.employee_id, 2 employee.employee_name, 3 employee.employee_hire_date, 4 employee.employee_termination_date, 5 employee.employee_billing_rate 6 FROM employee 7 WHERE employee.employee_id = :emp_id; SP2-0552: Bind variable "EMP_ID" not declared.

At this point, you have two choices. You can change the query and replace the parameter :emp_id with an employee number you know exists. You can test the query, and when you are satisfied the query works, you can replace the hardcoded value with the parameter reference. Woe be unto you, however, if there are several parameters and you forget to change one back. A second and safer approach is to declare bind variables to match the parameters in the query. In this case, there is just one to declare:

SQL> VARIABLE emp_id NUMBER

Once the variable has been declared, it is a simple matter to initialize it to a known good value:

SQL> EXECUTE :emp_id := 101; PL/SQL procedure successfully completed.

Now that you have declared and initialized the variable, it's easy to execute the query, bind variable and all:

SQL> @ex11-18 SQL> SET ECHO ON SQL> SELECT employee.employee_id, 2 employee.employee_name, 3 employee.employee_hire_date, 4 employee.employee_termination_date, 5 employee.employee_billing_rate 6 FROM employee 7 WHERE employee.employee_id = :emp_id; EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_ EMPLOYEE_ EMPLOYEE_BILLING_RATE ----------- -------------------- --------- --------- --------------------- 101 Marusia Churai 15-NOV-61 169

Once you are satisfied that everything is correct, you can paste the query directly back into your application without the risk that you might forget to change a hardcoded value back into a bind variable.