SQL*Plus® User's Guide and Reference-VARIABLE

我会带着你远行 2023-10-08 08:08 32阅读 0赞

SQL\*Plus® User's Guide and Reference

VARIABLE

**Syntax**

VAR\[IABLE\] \[*variable* \[*type*\] \]

where*type*represents one of the following:

NUMBER

CHAR

CHAR (*n* \[CHAR | BYTE\])

NCHAR

NCHAR (*n*)

VARCHAR2 (*n* \[CHAR | BYTE\])

NVARCHAR2 (*n*)

CLOB

NCLOB

REFCURSOR

BINARY\_FLOAT

BINARY\_DOUBLE

Declares a bind variable that can be referenced in PL/SQL.

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

To free resources used by CLOB and NCLOB bind variables, you may need to manually free temporary LOBs with:

EXECUTE DBMS\_LOB.FREETEMPORARY(:cv)

See [Using Bind Variables][] for more information on bind variables. See your*[Oracle Database PL/SQL User's Guide and Reference][Oracle Database PL_SQL User_s Guide and Reference]*for more information about PL/SQL.

**Terms**

*variable*

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

NUMBER

Creates a variable of type NUMBER with fixed length.

CHAR

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

CHAR (*n*\[CHAR | BYTE\])

Creates a variable of type CHAR with length*n*bytes or*n*characters. The maximum that*n*can be is 2000 bytes, and the minimum is 1 byte or 1 character. The maximum*n*for a CHAR variable with character semantics is determined by the number of bytes required to store each character for the chosen character set, with an upper limit of 2000 bytes. The length semantics are determined by the length qualifiers CHAR or BYTE, and if not explicitly stated, the value of the NLS\_LENGTH\_SEMANTICS environment variable is applied to the bind variable. Explicitly stating the length semantics at variable definition stage will always take precedence over the NLS\_LENGTH\_SEMANTICS setting.

NCHAR

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

NCHAR (*n*)

Creates a variable of type NCHAR with length*n*characters. The maximum that*n*can be is determined by the number of bytes required to store each character for the chosen national character set, with an upper limit of 2000 bytes. The only exception to this is when a SQL\*Plus session is connected to a pre Oracle9*i* server, or the SQLPLUSCOMPATIBILITY system variable is set to a version less than 9.0.0. In this case the length*n*can be in bytes or characters depending on the chosen national character set, with the upper limit of 2000 bytes still retained.

VARCHAR2 (*n*\[CHAR | BYTE\])

Creates a variable of type VARCHAR2 with length of up to*n*bytes or*n*characters. The maximum that*n*can be is 4000 bytes, and the minimum is 1 byte or 1 character. The maximum*n*for a VARCHAR2 variable with character semantics is determined by the number of bytes required to store each character for the chosen character set, with an upper limit of 4000 bytes. The length semantics are determined by the length qualifiers CHAR or BYTE, and if not explicitly stated, the value of the NLS\_LENGTH\_SEMANTICS environment variable is applied to the bind variable. Explicitly stating the length semantics at variable definition stage will always take precedence over the NLS\_LENGTH\_SEMANTICS setting.

NVARCHAR2 (*n*)

Creates a variable of type NVARCHAR2 with length of up to*n*characters. The maximum that*n*can be is determined by the number of bytes required to store each character for the chosen national character set, with an upper limit of 4000 bytes. The only exception to this is when a SQL\*Plus session is connected to a pre Oracle9*i* server, or the SQLPLUSCOMPATIBILITY system variable is set to a version less than 9.0.0. In this case the length*n*can be in bytes or characters depending on the chosen national character set, with the upper limit of 4000 bytes still retained.

CLOB

Creates a variable of type CLOB.

NCLOB

Creates a variable of type NCLOB.

REFCURSOR

Creates a variable of type REF CURSOR.

BINARY\_FLOAT

Creates a variable of type BINARY\_FLOAT. BINARY\_FLOAT is a floating-point number that conforms substantially with the Institute for Electrical and Electronics Engineers (IEEE) Standard for Binary Floating-Point Arithmetic, IEEE Standard 754-1985.

BINARY\_DOUBLE

Creates a variable of type BINARY\_DOUBLE. BINARY\_DOUBLE is a floating-point number that conforms substantially with the Institute for Electrical and Electronics Engineers (IEEE) Standard for Binary Floating-Point Arithmetic, IEEE Standard 754-1985.

**Usage**

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. See [PRINT][] for more information.

To automatically display the value of a bind variable created with VARIABLE, use the SET AUTOPRINT command. See [SET AUTOP\[RINT\] \{ON | OFF\}][SET AUTOP_RINT_ _ON _ OFF]for more information.

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 when you exit SQL\*Plus.

To free resources used by CLOB and NCLOB bind variables, you may need to manually free temporary LOBs with:

EXECUTE DBMS\_LOB.FREETEMPORARY(:cv)

All temporary LOBs are freed 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*[Oracle Database PL/SQL User's Guide and Reference][Oracle Database 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, changing its value, and displaying its current value.

To create a bind variable, enter:

VARIABLE ret\_val NUMBER

To change this bind variable in SQL\*Plus, you must use a PL/SQL block:

BEGIN

:ret\_val:=4;

END;

<table> 
 <tbody> 
 <tr> 
 <td> PL/SQL procedure successfully completed. </td> 
 </tr> 
 </tbody> 
</table>

To display the value of the bind variable in SQL\*Plus, enter:

PRINT ret\_val

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

VARIABLE id NUMBER

BEGIN

:id := EMP\_MANAGEMENT.HIRE

('BLAKE','MANAGER','KING',2990,'SALES');

END;

The value returned by the stored procedure is being placed in the bind variable, :id. It can be displayed with the PRINT command or used in subsequent PL/SQL subprograms.

The following example illustrates automatically displaying a bind variable:

SET AUTOPRINT ON

VARIABLE a REFCURSOR

BEGIN

OPEN :a FOR SELECT LAST\_NAME, CITY, DEPARTMENT\_ID

FROM EMP\_DETAILS\_VIEW

WHERE SALARY > 12000

ORDER BY DEPARTMENT\_ID;

END;

<table> 
 <tbody> 
 <tr> 
 <td> PL/SQL procedure successfully completed. LAST_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CITY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DEPARTMENT_ID -------------------------&nbsp; ------------------------------&nbsp; ------------- Hartstein&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Toronto&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20 Russell&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Oxford&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 80 Partners&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Oxford&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;80 King&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Seattle&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 90 Kochhar&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Seattle&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 90 De Haan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Seattle&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 90 &nbsp; 6 rows selected. </td> 
 </tr> 
 </tbody> 
</table>

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

The following example creates some variables:

VARIABLE id NUMBER

VARIABLE txt CHAR (20)

VARIABLE myvar REFCURSOR

Enter VARIABLE with no arguments to list the defined variables:

VARIABLE

<table> 
 <tbody> 
 <tr> 
 <td> variable id datatype NUMBER &nbsp; variable txt datatype CHAR(20) &nbsp; variable myvar datatype REFCURSOR </td> 
 </tr> 
 </tbody> 
</table>

The following example lists a single variable:

VARIABLE txt

<table> 
 <tbody> 
 <tr> 
 <td> variable txt datatype CHAR(20) </td> 
 </tr> 
 </tbody> 
</table>

The following example illustrates producing a report listing individual salaries and computing the departmental salary cost for employees who earn more than $12,000 per month:

VARIABLE rc REFCURSOR

BEGIN

OPEN :rc FOR SELECT DEPARTMENT\_NAME, LAST\_NAME, SALARY

FROM EMP\_DETAILS\_VIEW

WHERE SALARY > 12000

ORDER BY DEPARTMENT\_NAME, LAST\_NAME;

END;

<table> 
 <tbody> 
 <tr> 
 <td> PL/SQL procedure successfully completed. </td> 
 </tr> 
 </tbody> 
</table>

SET PAGESIZE 100 FEEDBACK OFF

TTITLE LEFT '\*\*\* Departmental Salary Bill \*\*\*' SKIP 2

COLUMN SALARY FORMAT $999,990.99 HEADING 'Salary'

COLUMN DEPARTMENT\_NAME HEADING 'Department'

COLUMN LAST\_NAME HEADING 'Employee'

COMPUTE SUM LABEL 'Subtotal:' OF SALARY ON DEPARTMENT\_NAME

COMPUTE SUM LABEL 'Total:' OF SALARY ON REPORT

BREAK ON DEPARTMENT\_NAME SKIP 1 ON REPORT SKIP 1

PRINT rc

<table> 
 <tbody> 
 <tr> 
 <td> *** Departmental Salary Bill *** &nbsp; DEPARTMENT_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Employee&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Salary ------------------------------ ------------------------- ------------ Executive&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; De Haan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $17,000.00 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; King&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$24,000.00 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Kochhar&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $17,000.00 ******************************&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ------------ Subtotal:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $58,000.00 &nbsp; Marketing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Hartstein&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $13,000.00 ******************************&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ------------ Subtotal:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $13,000.00 &nbsp; Sales&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Partners&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $13,500.00 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Russell&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $14,000.00 ******************************&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ------------ Subtotal:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $27,500.00 &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;------------ Total:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $98,500.00 </td> 
 </tr> 
 </tbody> 
</table>

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

VARIABLE T CLOB

BEGIN

SELECT CLOB\_COL INTO :T FROM CLOB\_TAB;

END;

<table> 
 <tbody> 
 <tr> 
 <td> PL/SQL PROCEDURE SUCCESSFULLY COMPLETED </td> 
 </tr> 
 </tbody> 
</table>

To print 200 characters from the column clob\_col, enter

SET LINESIZE 70

SET LONG 200

PRINT T

<table> 
 <tbody> 
 <tr> 
 <td> T ---------------------------------------------------------------------- Remember to run the Departmental Salary Bill report each month This r eport contains confidential information. </td> 
 </tr> 
 </tbody> 
</table>

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

SET LOBOFFSET 21

PRINT T

<table> 
 <tbody> 
 <tr> 
 <td> T ---------------------------------------------------------------------- Departmental Salary Bill report each month This report contains confi dential information. </td> 
 </tr> 
 </tbody> 
</table>

For more information on creating CLOB columns, see your*[Oracle Database SQL Reference][]*

[Using Bind Variables]: https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch5.htm#i1211850
[Oracle Database PL_SQL User_s Guide and Reference]: https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/toc.htm
[PRINT]: https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12031.htm#i2698143
[SET AUTOP_RINT_ _ON _ OFF]: https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm#i2698656
[Oracle Database SQL Reference]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/toc.htm