Putting the value of a substitution variable into a bind variable is actually 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 easily take advantage of this in order to place a value into a bind variable. Take a look at the following short script:

DEFINE my_sub_var = 'Raymond'
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, it replaces the reference to the substitution variable with the value of that variable. The command after substitution, the one that is actually executed, looks like this:

EXECUTE :my_bind_var := 'Raymond';

Since 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. The following example declares a variable of type NUMBER and assigns a value to it:

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

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

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 results 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(30)
EXECUTE :my_bind_var := 'This is a test.';

In order to get the value of the bind variable into the substitution variable, you need to go through the following steps:

The SELECT statement will, of course, only return 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. Here’s a code sample that demonstrates this technique:

--Declare one bind variable and one substitution variable.
--Initialize the bind variable to a value.
DEFINE my_sub_var = ' '
VARIABLE my_bind_var VARCHAR2(30)
EXECUTE :my_bind_var := 'This is a test.';

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

Notice 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 still be 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.

The format for the PRINT command looks like this:

PRI[NT] [bind_variable_name]

where:

The results from the PRINT command look very much like the results you get from a SELECT. Here’s an example:

SQL> PRINT my_string

MY_STRING
------------------------------------------------
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 commands to format the output. The following example shows how this works:

SQL> COLUMN my_string FORMAT A40 HEADING 'My Motto'
SQL> PRINT my_string

My Motto
----------------------------------------
Brighten the corner where you are

SQL> 
SQL> TTITLE LEFT 'This is a Page Title' SKIP 2
SQL> COLUMN my_number FORMAT 99.99 HEADING 'My Age'
SQL> PRINT my_number

This is a Page Title

My Age
------
 36.00

All other formatting options, such as PAGESIZE and LINESIZE, apply when printing bind variables. You can even use the COLUMN command’s NEW_VALUE clause to store the value of a bind variable in a substitution variable, as the following example demonstrates:

SQL> DEFINE s_my_string = ' '
SQL> COLUMN my_string NOPRINT NEW_VALUE s_my_string
SQL> PRINT my_string
SQL> PROMPT &&s_my_string
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

My Motto
----------------------------------------
Brighten the corner where you are


My Age
------
 36.00

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

The CLOB datatype is new with Oracle8. CLOB stands for character large object, and variables of this type can hold up to 2 gigabytes of text data. When printing variables of type CLOB or NCLOB, there are three SQL*Plus settings you can use to control what you see and how that works. See Table 7.2.

By default, SQL*Plus will only display the first 80 characters of a CLOB value. This is rarely enough. After all, if you only needed 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 2 gigabytes of data either.

The following example shows the result of printing a CLOB value using the default settings for the values in Table 7.2:

SQL> SELECT clob_value FROM clob_example;

CLOB_VALUE
-----------------------------------------------------------------------------
By default, SQL*Plus will only display the first 80 characters of a CLOB value.

1 row selected.

As you can see, only 80 characters of the value were displayed. You can change the LONG setting to see more of the value, as the next example shows:

SQL> SET LONG 500
SQL> SELECT clob_value from clob_example;

CLOB_VALUE
-----------------------------------------------------------------------------
By default, SQL*Plus will only display the first 80 characters of a CLOB 
value. This is rarely enough. After all, if you only needed 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 2 gigabytes of data 
either.

1 row selected.

By combining the LOBOFFSET and LONG settings, you can print any arbitrary substring of a CLOB variable. The following example prints the characters 81 through 102, which make up the second sentence in the example:

SQL> SET LONG 22
SQL> SET LOBOFFSET 81
SQL> SELECT clob_value FROM clob_example;

CLOB_VALUE
----------------------
This is rarely enough.

1 row selected.

Finally, the LONGCHUNKSIZE setting controls the amount of the CLOB that is 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 will retrieve the value with one fetch from the database, possibly improving performance.

Beginning with Oracle8, 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 you can use the SQL*Plus PRINT command to format and display the results of that query. The following script makes use of this capability. It asks the user for a pattern match string and prints the list tables with names that match the pattern supplied by the user. A PL/SQL block is used to interpret the user’s response, and to open a cursor that will return the desired results.

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

As you can see, this script defines a SQL*Plus REFCURSOR variable. The cursor is opened and a query assigned by code within a PL/SQL block. Then the SQL*Plus PRINT command is used to display the results of that query.

SQL> @ref_cursor
List tables LIKE > 

TABLE_NAME
------------------------------
EMPLOYEE
PROJECT
PROJECT_HOURS
SQL> 
SQL> 
SQL> @ref_cursor
List tables LIKE > p%

TABLE_NAME
------------------------------
PROJECT
PROJECT_HOURS

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.

The SQL*Plus manual, at least the one for versions 8.0.3 and before, will tell you that bind variables cannot be used in SQL statements. Don’t believe it. Bind variables can be used in SELECT statements, both in the column list and in the WHERE clause. You will frequently see this done in scripts where there is a need to get the contents of a bind variable into a substitution variable. See the section titled Section 7.1.2.2 earlier in this chapter. Here’s an example of a SELECT statement being used to display the contents of a bind variable:

SQL> VARIABLE l_user VARCHAR2(30)
SQL> EXECUTE :l_user := user;
SQL> SELECT :l_user FROM dual;

:L_USER
--------------------------------
NATHAN

Using SELECT like this offers no real advantage over the use of the PRINT command. If you just 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. Here’s an example that combines all three of these:

SQL> SET HEADING OFF
SQL> SELECT 'User ' || :l_user || ' has ' 
  2         || TO_CHAR(COUNT(*)) || ' tables.'
  3    FROM all_tables
  4   WHERE owner = :l_user;
NATHAN
User SQLPLUS has 3 tables.

Two types of bind variables cannot be used in a SQL statement. These are the REFCURSOR and CLOB types. You must use the PRINT command with these.

Oracle provides a number of built-in PL/SQL packages that allow you to do such things as create and execute dynamic SQL queries (the DBMS_SQL package), submit and manage PL/SQL batch jobs (the DBMS_JOBS package), and many other very useful things. In many cases, the procedures and functions in these packages use OUT parameters to return information to the caller. To call these routines from within a SQL*Plus script and display the results to the person running the script, you need to use bind variables.

Consider the problem of writing a SQL*Plus script to submit a PL/SQL job for periodic execution by Oracle. To do this, you would use a procedure in Oracle’s DBMS_JOBS package named SUBMIT. SUBMIT takes several arguments, one of which is an OUT argument. The declaration for SUBMIT looks like this:

DBMS_JOB.SUBMIT(  job             OUT    BINARY_INTEGER,
                  what            IN     VARCHAR2,
                  next_date       IN     DATE DEFAULT SYSDATE,
                  interval        IN     VARCHAR2 DEFAULT 'null',
                  no_parse        IN     BOOLEAN DEFAULT FALSE)

The job parameter is the job number, and is an output from the procedure. This job number uniquely identifies a job within Oracle, and is important to know because all the procedures to modify, delete, or otherwise manage database jobs require the job number as an input. Here is one approach you could use to write a script that submits a PL/SQL job for a user. In particular, note the use of a bind variable in the EXECUTE command. Also note the subsequent PRINT command that displays the job number to the user.

SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY ON
SET HEADING ON
SET TERMOUT ON

--
--This script submits a job to the queue after first
--prompting the user for the required information.

--Get the neccessary information from the user.
PROMPT
prompt Submit a PL/SQL job.
PROMPT
ACCEPT what CHAR PROMPT 'Enter the PL/SQL statement to execute >'
PROMPT
ACCEPT when CHAR PROMPT 'When (e.g. 15-Nov-1961 18:30)?'
ACCEPT interval CHAR PROMPT 'Interval (e.g. sysdate+1)?'

--Submit the job
VARIABLE job_number NUMBER
EXECUTE DBMS_JOB.SUBMIT(:job_number, '&what', &when, '&interval'),

--Tell the user the job's ID number.
PRINT job_number

SET FEEDBACK ON
SET VERIFY ON

Running the above script looks like this:

SQL> @submit_job

Submit a PL/SQL job.

Enter the PL/SQL statement to execute >NULL;

When (e.g. 15-Nov-1961 18:30)?sysdate
Interval (e.g. sysdate+1)?sysdate+1

JOB_NUMBER
----------
         4

NULL is a valid PL/SQL statement that does nothing, and is often used as a placeholder for code to be written later. In this example, Oracle will do nothing once each day.

As mentioned earlier, the REFCURSOR datatype holds a pointer to a cursor, and is a new feature introduced with Oracle8. 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, you saw a REFCURSOR example that printed a list of tables owned by the current user. Here is an enhanced version of that script that allows the user to optionally 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.

--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 then opens the REFCURSOR variable using the appropriate SELECT statement. Here’s how it looks to run the script:

SQL> @ref_cursor
List tables LIKE > 

TABLE_NAME
------------------------------
EMPLOYEE
PROJECT
PROJECT_HOURS
SQL> 
SQL> 
SQL> @ref_cursor
List tables LIKE > p%

TABLE_NAME
------------------------------
PROJECT
PROJECT_HOURS

You can see that when no search string was specified, all the tables were listed, whereas entering a search string of “p%” caused only tables starting with the letter “P” to be listed. The output is just like that from a standard SELECT statement, and all the column and page formatting options of SQL*Plus apply.

Using REFCURSOR variables is one way to add conditional logic to your SQL*Plus scripts. You’ll see another example of this later in the section titled Section 7.2.

Bind variables can make it more convenient to take a query from an application development environment such as PowerBuilder and debug it using SQL*Plus. PowerBuilder queries often contain parameters to be supplied at runtime. PowerBuilder parameters are preceded by colons, the same syntax SQL*Plus uses for bind variables. If you had a PowerBuilder datawindow that allowed you to edit just one employee record, here is how the query behind that datawindow might look:

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

Now if you want to test this query, and you just paste it into SQL*Plus as it is, you will get the following results:

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
  8  /
Bind variable "EMP_ID" not declared.

At this point, you have two choices. You can change the query and simply replace the parameter :emp_id with an employee number that you know exists. Then 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 safer approach would be simply to declare bind variables to match the parameters in the query. In this case there’s 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.

Having declared and initialized the variable, it’s now a simple matter to copy the query directly from PowerBuilder, or wherever, and paste it unchanged into SQL*Plus:

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
  8  /

EMPLOYEE_ID EMPLOYEE_NAME        EMPLOYEE_ EMPLOYEE_ EMPLOYEE_BILLING_RATE
----------- -------------------- --------- --------- ---------------------
        101 Jonathan Gennick     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 manually change a hardcoded value back into a parameter.