VARIABLE

Purpose

Declares a bind variable that can then be referenced in PL/SQL. For more information on bind variables, see "Using Bind Variables" in Chapter 3. For more information about PL/SQL, see your PL/SQL User's Guide and Reference.

VARIABLE without arguments displays a list of all the variables declared in the session. VARIABLE followed only by a variable name lists that variable.

Syntax

VAR[IABLE] [variable [NUMBER|CHAR|CHAR (n)|NCHAR|NCHAR (n) 
   |VARCHAR2 (n)|NVARCHAR2 (n)|CLOB|NCLOB|REFCURSOR]]

Terms and Clauses

Refer to the following list for a description of each term or clause:

variable

Represents the name of the bind variable you wish to create.

NUMBER

Creates a variable of type NUMBER with a fixed length.

CHAR

Creates a variable of type CHAR (character) with a length of one.

CHAR (n)

Creates a variable of type CHAR with a maximum length of n, up to 2000.

NCHAR

Creates a variable of type NCHAR (national character) with a length of one.

NCHAR (n)

Creates a variable of type NCHAR with a maximum length of n, up to 2000.

VARCHAR2 (n)

Creates a variable of type VARCHAR2 with a maximum length of n, up to 4000.

NVARCHAR2 (n)

Creates a a variable of type NVARCHAR2 (NCHAR VARYING) with a maximum length of n, up to 4000.

CLOB

Creates a variable of type CLOB.

NCLOB

Creates a variable of type NCLOB.

REFCURSOR

Creates a variable of type REF CURSOR.

Usage Notes

Bind variables may be used as parameters to stored procedures, or may be directly referenced in anonymous PL/SQL blocks.

To display the value of a bind variable created with VARIABLE, use the PRINT command. For more information, see the PRINT command in this chapter.

To automatically display the value of a bind variable created with VARIABLE, use the SET AUTOPRINT command. For more information, see the SET AUTOPRINT command in this chapter.

Bind variables cannot be used in the COPY command or SQL statements, except in PL/SQL blocks. Instead, use substitution variables.

When you execute a VARIABLE ... CLOB or NCLOB command, SQL*Plus associates a LOB locator with the bind variable. The LOB locator is automatically populated when you execute a SELECT clob_column INTO :cv statement in a PL/SQL block. SQL*Plus closes the LOB locator after completing a PRINT statement for that bind variable, or when you exit SQL*Plus.

SQL*Plus SET commands such as SET LONG and SET LONGCHUNKSIZE and SET LOBOFFSET may be used to control the size of the buffer while PRINTing CLOB or NCLOB bind variables.

SQL*Plus REFCURSOR bind variables may be used to reference PL/SQL 2.3 or higher Cursor Variables, allowing PL/SQL output to be formatted by SQL*Plus. For more information on PL/SQL Cursor Variables, see your PL/SQL User's Guide and Reference.

When you execute a VARIABLE ... REFCURSOR command, SQL*Plus creates a cursor bind variable. The cursor is automatically opened by an OPEN ... FOR SELECT statement referencing the bind variable in a PL/SQL block. SQL*Plus closes the cursor after completing a PRINT statement for that bind variable, or on exit.

SQL*Plus formatting commands such as BREAK, COLUMN, COMPUTE and SET may be used to format the output from PRINTing a REFCURSOR.

A REFCURSOR bind variable may not be PRINTed more than once without re-executing the PL/SQL OPEN ... FOR statement.

Examples

The following example illustrates creating a bind variable and then setting it to the value returned by a function:

SQL> VARIABLE id NUMBER
SQL> BEGIN
  2    :id := emp_management.hire
  3      ('BLAKE','MANAGER','KING',2990,'SALES');
  4  END;

The bind variable named id can be displayed with the PRINT command or used in subsequent PL/SQL subprograms.

The following example illustrates automatically displaying a bind variable:

SQL> SET AUTOPRINT ON
SQL> VARIABLE a REFCURSOR
SQL> BEGIN
  2  OPEN :a FOR SELECT * FROM DEPT ORDER BY DEPTNO;
  3  END;
  4  /

PL/SQL procedure successfully completed.

DEPTNO   DNAME         LOC
-------- ------------- -------------
      10 ACCOUNTING    NEW YORK
      20 RESEARCH      DALLAS
      30 SALES         CHICAGO
      40 OPERATIONS    BOSTON

In the above example, there is no need to issue a PRINT command to display the variable.

The following example creates some variables and then lists them:

SQL> VARIABLE id NUMBER
SQL> VARIABLE txt CHAR (20)
SQL> VARIABLE myvar REFCURSOR
SQL> VARIABLE
variable id
datatype NUMBER

variable txt
datatype CHAR(20)

variable myvar
datatype REFCURSOR

The following example lists a single variable:

SQL> VARIABLE txt
variable txt
datatype CHAR(20)

The following example illustrates producing a report listing individual salaries and computing the departmental and total salary cost:

SQL> VARIABLE RC REFCURSOR
  2  BEGIN
  3    OPEN :RC FOR SELECT DNAME, ENAME, SAL
  4            FROM EMP, DEPT
  5            WHERE EMP.DEPTNO = DEPT.DEPTNO
  6            ORDER BY EMP.DEPTNO, ENAME;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> SET PAGESIZE 100 FEEDBACK OFF
SQL> TTITLE LEFT '*** Departmental Salary Bill ***' SKIP 2
SQL> COLUMN SAL FORMAT $999,990.99 HEADING 'Salary'
SQL> COLUMN DNAME HEADING 'Department'
SQL> COLUMN ENAME HEADING 'Employee'
SQL> COMPUTE SUM LABEL 'Subtotal:' OF SAL ON DNAME
SQL> COMPUTE SUM LABEL 'Total:' OF SAL ON REPORT
SQL> BREAK ON DNAME SKIP 1 ON REPORT SKIP 1
SQL> PRINT RC


*** Departmental Salary Bill ***

Department     Employee     Salary
-------------- ------------ ----------
ACCOUNTING     CLARK         $2,450.00
               KING          $5,000.00
               MILLER        $1,300.00
**************              ----------
Subtotal:                    $8,750.00

RESEARCH       ADAMS         $1,100.00
               FORD          $3,000.00
               JONES         $2,975.00
               SCOTT         $3,000.00
               SMITH           $800.00
**************              ----------
Subtotal:                   $10,875.00

SALES          ALLEN         $1,600.00
               BLAKE         $2,850.00
               JAMES           $950.00
               MARTIN        $1,250.00
               TURNER        $1,500.00
               WARD          $1,250.00
**************              ----------
Subtotal:                    $9,400.00

                            ----------
Total:                      $29,025.00

The following example illustrates producing a report containing a CLOB column, and then displaying it with the SET LOBOFFSET command.

Assume you have already created a table named clob_tab which contains a column named clob_col of type CLOB. The clob_col contains the following data:

Remember to run the Departmental Salary Bill report each month. This report 
contains confidential information.

To produce a report listing the data in the col_clob column, enter

SQL> variable t clob
SQL> begin
  2    select clob_col into t: from clob_tab;
  3  end;
  4  /
PL/SQL procedure successfully completed

To print 200 characters from the column clob_col, enter

SQL> set LONG 200
SQL> print t

The following output results:

T
------------------------------------------------------------
Remember to run the Departmental Salary Bill report each month. This report
contains confidential information.

To set the printing position to the 21st character, enter

SQL> set LOBOFFSET 21
SQL> print t

the following output results:

T
------------------------------------------------------------
Departmental Salary Bill report each month. This report contains confidential
information.

For more information on creating CLOB columns, see your Oracle8i SQL Reference.