 Library |
 Product |
 Contents |
 Index |
Library |
Product |
Contents |
Index |
Places and formats a specified title at the top of each report page or lists the current TTITLE definition. The old form of TTITLE is used if only a single word or string in quotes follows the TTITLE command.
For a description of the old form of TTITLE, see TTITLE in Appendix F.
Syntax
TTI[TLE] [printspec [text|variable] ...]|[OFF|ON]
where printspec represents one or more of the following clauses used to place and format the text:
COL n S[KIP] [n] TAB n LE[FT] CE[NTER] R[IGHT] BOLD FORMAT text
Terms and Clauses
Refer to the following list for a description of each term or clause. These terms and clauses also apply to the BTITLE command.
text
Represents the title text. Enter text in single quotes if you want to place more than one word on a single line.
variable
Represents a user variable or any of the following system-maintained values:
To print one of these values, reference the appropriate variable in the title. You can format variable with the FORMAT clause.
OFF
Turns the title off (suppresses its display) without affecting its definition.
ON
Turns the title on (restores its display). When you define a top title, SQL*Plus automatically sets TTITLE to ON.
COL n
Indents to column n of the current line (backward if column n has been passed). "Column" in this context means print position, not table column.
S[KIP] [n]
Skips to the start of a new line n times; if you omit n, one time; if you enter zero for n, backward to the start of the current line.
TAB n
Skips forward n columns (backward if you enter a negative value for n). "Column" in this context means print position, not table column.
LE[FT], CE[NTER], and R[IGHT]
Left-align, center, and right-align data on the current line respectively. SQL*Plus aligns following data items as a group, up to the end of the printspec or the next LEFT, CENTER, RIGHT, or COL command. CENTER and RIGHT use the SET LINESIZE value to calculate the position of the data item that follows.
BOLD
Prints data in bold print. SQL*Plus represents bold print on your terminal by repeating the data on three consecutive lines. On some operating systems, SQL*Plus may instruct your printer to print bolded text on three consecutive lines, instead of bold.
FORMAT text
Specifies a format model that determines the format of following data items, up to the next FORMAT clause or the end of the command. The format model must be a text constant such as A10 or $999. See COLUMN FORMAT for more information on formatting and valid format models.
If the datatype of the format model does not match the datatype of a given data item, the FORMAT clause has no effect on that item.
If no appropriate FORMAT model precedes a given data item, SQL*Plus prints NUMBER values according to the format specified by SET NUMFORMAT or, if you have not used SET NUMFORMAT, the default format. SQL*Plus prints DATE values according to the default format.
Refer to the FORMAT clause of the COLUMN command in this chapter for more information on default formats.
Enter TTITLE with no clauses to list the current TTITLE definition.
Usage Notes
If you do not enter a printspec clause before the first occurrence of text, TTITLE left justifies the text. SQL*Plus interprets TTITLE in the new form if a valid printspec clause (LEFT, SKIP, COL, and so on) immediately follows the command name.
See COLUMN NEW_VALUE for information on printing column and DATE values in the top title.
You can use any number of constants and variables in a printspec. SQL*Plus displays the constants and variables in the order you specify them, positioning and formatting each constant or variable as specified by the printspec clauses that precede it.
The length of the title you specify with TTITLE cannot exceed 2400 characters.
The continuation character (a hyphen) will not be recognized inside a single-quoted title text string. To be recognized, the continuation character must appear outside the quotes, as follows:
SQL> TTITLE CENTER 'Summary Report for' - > 'the Month of May'
Examples
To define "Monthly Analysis" as the top title and to left-align it, to center the date, to right-align the page number with a three-digit format, and to display "Data in Thousands" in the center of the next line, enter
SQL> TTITLE LEFT 'Monthly Analysis' CENTER '27 Jun 97' - > RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP CENTER - > 'Data in Thousands'
The following title results:
Monthly Analysis 27 Jun 97 Page: 1
Data in ThousandsTo suppress the top title display without changing its definition, enter
SQL> TTITLE OFF
Deletes one or more user variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command).
Syntax
UNDEF[INE] variable ...
Terms and Clauses
Refer to the following for a description of the term or clause:
variable
Represents the name of the user variable you wish to delete. One or more user variables may be deleted in the same command.
Example
To undefine a user variable named POS, enter
SQL> UNDEFINE POS
To undefine two user variables named MYVAR1 and MYVAR2, enter
SQL> UNDEFINE MYVAR1 MYVAR2
Declares a bind variable that can then be referenced in PL/SQL. For more information on bind variables, see "Using Bind Variables". 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 REFCURSOR.
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 BOSTONIn 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.00The 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 Oracle8 Server SQL Reference Manual.
Exits SQL*Plus if an operating system error occurs (such as a file I/O error).
Syntax
WHENEVER OSERROR {EXIT [SUCCESS|FAILURE|n|variable|:BindVariable] [COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}
Terms and Clauses
Refer to the following list for a description of each term or clause:
EXIT [SUCCESS|FAILURE|n|variable|:BindVariable]
Directs SQL*Plus to exit as soon as an operating system error is detected. You can also specify that SQL*Plus return a success or failure code, the operating system failure code, or a number or variable of your choice. See EXIT in this chapter for details.
CONTINUE
COMMIT
Directs SQL*Plus to execute a COMMIT before exiting or continuing and save pending changes to the database.
ROLLBACK
Directs SQL*Plus to execute a ROLLBACK before exiting or continuing and abandon pending changes to the database.
NONE
Directs SQL*Plus to take no action before continuing.
Usage Notes
If you do not enter the WHENEVER OSERROR command, the default behavior of SQL*Plus is to continue and take no action when an operating system error occurs.
Examples
The commands in the following command file cause SQL*Plus to exit and COMMIT any pending changes if a failure occurs when writing to the output file:
WHENEVER OSERROR EXIT SQL.OSCODE COMMIT SPOOL MYLOG UPDATE EMP SET SAL = SAL*1.1 COPY TO SCOTT/TIGER@HQDB - REPLACE EMP - USING SELECT * FROM EMP SPOOL OUT SELECT SAL FROM EMP
Exits SQL*Plus if a SQL command or PL/SQL block generates an error.
Syntax
WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable] [COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}
Terms and Clauses
Refer to the following list for a description of each term or clause:
EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
Directs SQL*Plus to exit as soon as it detects a SQL command or PL/SQL block error (but after printing the error message). SQL*Plus will not exit on a SQL*Plus error. The EXIT clause of WHENEVER SQLERROR follows the same syntax as the EXIT command. See EXIT in this chapter for details.
CONTINUE
COMMIT
Directs SQL*Plus to execute a COMMIT before exiting or continuing and save pending changes to the database.
ROLLBACK
Directs SQL*Plus to execute a ROLLBACK before exiting or continuing and abandon pending changes to the database.
NONE
Directs SQL*Plus to take no action before continuing.
Usage Notes
The WHENEVER SQLERROR command is triggered by SQL command or PL/SQL block errors, and not by SQL*Plus command errors.
If you do not enter the WHENEVER SQLERROR command, the default behavior of SQL*Plus is to continue and take no action when a SQL error occurs.
Examples
The commands in the following command file cause SQL*Plus to exit and return the SQL error code if the SQL UPDATE command fails:
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE SQL> UPDATE EMP SET SAL = SAL*1.1
The following SQL command error causes SQL*Plus to exit and return the SQL error code:
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
SELECT COLUMN_DOES_NOT_EXIST FROM DUAL
*
ERROR at line 1:
ORA-00904: invalid column name
Disconnected from Oracle.....The following SQL command error causes SQL*Plus to exit and return the value of the variable MY_ERROR_VAR:
SQL> DEFINE MY_ERROR_VAR = 99
SQL> WHENEVER SQLERROR EXIT MY_ERROR_VAR
SQL> UPDATE NON_EXISTED_TABLE SET COL1 = COL1 + 1;
UPDATE NON_EXISTED_TABLE SET COL1 = COL1 + 1
*
ERROR at line 1:
ORA-00942: table or view does not exist
Disconnected from Oracle.....
The following examples show that the WHENEVER SQLERROR command does not have any effect on SQL*Plus commands, but does on SQL commands and PL/SQL blocks:
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE SQL> COLUMN ENAME HEADIING "EMPLOYEE NAME" Unknown COLUMN option "HEADIING" SQL> SHOW NON_EXISTED_OPTION Unknown SHOW option "NON_EXISTED_OPTION" SQL> GET NON_EXISTED_FILE.SQL Unable to open "NON_EXISTED_FILE.SQL" SQL>
The following PL/SQL block error causes SQL*Plus to exit and return the SQL error code:
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> BEGIN
2 SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
3 END;
4 /
SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
*
ERROR at line 2:
ORA-06550: line 2, column 10:
PLS-00201: identifier 'COLUMN_DOES_NOT_EXIST' must be declared
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored
Disconnected from Oracle.....
|
Prev Next |
 Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |