Various SET commands in SQL Part 2
P1 CHAR IN
P2 NUMBER IN
To create and describe the object type ADDRESS that contains the attributes STREET and CITY, enter
CREATE TYPE ADDRESS AS OBJECT
( STREET VARCHAR2(20),
CITY VARCHAR2(20)
);
/
Type created.
DESCRIBE address
Name Null? Type
----------------------------------------- -------- ----------------------
STREET VARCHAR2(20)
CITY VARCHAR2(20)
To create and describe the object type EMPLOYEE that contains the attributes LAST_NAME, EMPADDR, JOB_ID and SALARY, enter
CREATE TYPE EMPLOYEE AS OBJECT
(LAST_NAME VARCHAR2(30),
EMPADDR ADDRESS,
JOB_ID VARCHAR2(20),
SALARY NUMBER(7,2)
);
/
Type created.
DESCRIBE employee
Name Null? Type
----------------------------------------- -------- ----------------------
LAST_NAME VARCHAR2(30)
EMPADDR ADDRESS
JOB_ID VARCHAR2(20)
SALARY NUMBER(7,2)
To create and describe the object type addr_type as a table of the object type ADDRESS, enter
CREATE TYPE addr_type IS TABLE OF ADDRESS;/
Type created.
DESCRIBE addr_type
addr_type TABLE OF ADDRESS
Name Null? Type
----------------------------------------- -------- ----------------------
STREET VARCHAR2(20)
CITY VARCHAR2(20)
To create and describe the object type addr_varray as a varray of the object type ADDRESS, enter
CREATE TYPE addr_varray AS VARRAY(10) OF ADDRESS;/
Type created.
DESCRIBE addr_varray
addr_varray VARRAY(10) OF ADDRESS
Name Null? Type
----------------------------------------- -------- ----------------------
STREET VARCHAR2(20)
CITY VARCHAR2(20)
To create and describe the table department that contains the columns DEPARTMENT_ID, PERSON and LOC, enter
CREATE TABLE department
(DEPARTMENT_ID NUMBER,
PERSON EMPLOYEE,
LOC NUMBER
);
/
Table created.
DESCRIBE department
Name Null? Type
----------------------------------------- -------- ----------------------
DEPARTMENT_ID NUMBER
PERSON EMPLOYEE
LOC NUMBER
To create and describe the object type rational that contains the attributes NUMERATOR and DENOMINATOR, and the METHOD rational_order, enter
CREATE OR REPLACE TYPE rational AS OBJECT
(NUMERATOR NUMBER,
DENOMINATOR NUMBER,
MAP MEMBER FUNCTION rational_order -
RETURN DOUBLE PRECISION,
PRAGMA RESTRICT_REFERENCES
(rational_order, RNDS, WNDS, RNPS, WNPS) );
/
CREATE OR REPLACE TYPE BODY rational AS OBJECT
MAP MEMBER FUNCTION rational_order -
RETURN DOUBLE PRECISION IS
BEGIN
RETURN NUMERATOR/DENOMINATOR;
END;
END;
/
DESCRIBE rational
Name Null? Type
------------------------------ -------- ------------
NUMERATOR NUMBER
DENOMINATOR NUMBER
METHOD
MAP MEMBER FUNCTION RATIONAL_ORDER RETURNS NUMBER
To create a table which contains a column of XMLType, and describe it, enter
CREATE TABLE PROPERTY (Price NUMBER, Description SYS.XMLTYPE);
Table created
DESCRIBE PROPERTY;
Name Null? Type
----------------------------------------- -------- ----------------------
PRICE NUMBER
DESCRIPTION SYS.XMLTYPE
To format the DESCRIBE output use the SET command as follows:
SET LINESIZE 80
SET DESCRIBE DEPTH 2
SET DESCRIBE INDENT ON
SET DESCRIBE LINE OFF
To display the settings for the object, use the SHOW command as follows:
SHOW DESCRIBE
DESCRIBE DEPTH 2 LINENUM OFF INDENT ON
DESCRIBE employee
Name Null? Type
----------------------------------------- -------- ----------------------
FIRST_NAME VARCHAR2(30)
EMPADDR ADDRESS
STREET VARCHAR2(20)
CITY VARCHAR2(20)
JOB_ID VARCHAR2(20)
SALARY NUMBER(7,2)
For more information on using the CREATE TYPE command, see your Oracle Database SQL Reference.
For information about using the SET DESCRIBE and SHOW DESCRIBE commands, see the SET and SHOW commands.
DISCONNECT
Syntax
DISC[ONNECT]
Commits pending changes to the database and logs the current username out of Oracle Database, but does not exit SQL*Plus.
Usage
Use DISCONNECT within a script to prevent user access to the database when you want to log the user out of Oracle Database but have the user remain in SQL*Plus. In SQL*Plus command-line, use EXIT or QUIT to log out of Oracle Database and return control to your computer's operating system. In iSQL*Plus, click the Logout button to log out of Oracle Database.
Examples
Your script might begin with a CONNECT command and end with a DISCONNECT, as shown later.
CONNECT HR/your_password
SELECT LAST_NAME, DEPARTMENT_NAME FROM EMP_DETAILS_VIEW;
DISCONNECT
SET INSTANCE FIN2
CONNECT HR2/your_password
EDIT
EDIT is not available in iSQL*Plus.
Syntax
ED[IT] [file_name[.ext]]
where file_name[.ext] represents the file you wish to edit (typically a script).
Invokes an operating system text editor on the contents of the specified file or on the contents of the buffer. The buffer has no command history list and does not record SQL*Plus commands.
Enter EDIT with no filename to edit the contents of the SQL buffer with the operating system text editor.
Usage
If you omit the file extension, SQL*Plus assumes the default command-file extension (normally SQL). For information on changing the default extension, see the SUFFIX variable of the SET command in this chapter.
If you specify a filename, SQL*Plus searches for the file in the directory set by ORACLE_PATH. If SQL*Plus cannot find the file in ORACLE_PATH, or if ORACLE_PATH is not set, it searches for the file in the current working directory. If SQL*Plus cannot find the file in either directory, it creates a file with the specified name.
The substitution variable, _EDITOR, contains the name of the text editor invoked by EDIT. You can change the text editor by changing the value of _EDITOR. For information about changing the value of a substitution variable, see DEFINE. EDIT attempts to run the default operating system editor if _EDITOR is undefined.
EDIT places the contents of the SQL buffer in a file named AFIEDT.BUF by default (in your current working directory) and runs the text editor on the contents of the file. If the file AFIEDT.BUF already exists, it is overwritten with the contents of the buffer. You can change the default filename by using the SET EDITFILE command. For more information about setting a default filename for the EDIT command, see the EDITFILE variable of the SET command in this chapter.
Note:
The default file, AFIEDT.BUF, may have a different name on some operating systems.
If you do not specify a filename and the buffer is empty, EDIT returns an error message.
To leave the editing session and return to SQL*Plus, terminate the editing session in the way customary for the text editor. When you leave the editor, SQL*Plus loads the contents of the file into the buffer.
Note:
In Windows, if you use WordPad as your editor (_EDITOR=write.exe), the buffer is not reloaded when you exit WordPad. In this case, use GET to reload the buffer.
Examples
To edit the file REPORT with the extension SQL using your operating system text editor, enter
EDIT REPORT
EXECUTE
Syntax
EXEC[UTE] statement
where statement represents a PL/SQL statement.
Executes a single PL/SQL statement. The EXECUTE command is often useful when you want to execute a PL/SQL statement that references a stored procedure. For more information on PL/SQL, see yourPL/SQL User's Guide and Reference.
Usage
If your EXECUTE command cannot fit on one line because of the PL/SQL statement, use the SQL*Plus continuation character (a hyphen).
The length of the command and the PL/SQL statement cannot exceed the length defined by SET LINESIZE.
You can suppress printing of the message "PL/SQL procedure successfully completed" with SET FEEDBACK OFF.
Examples
If the variable :n has been defined with:
VARIABLE n NUMBER
The following EXECUTE command assigns a value to the bind variable n:
EXECUTE :n := 1
PL/SQL procedure successfully completed.
For information on how to create a bind variable, see the VARIABLE command.
EXIT
Syntax
{EXIT | QUIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]
Commits or rolls back all pending changes, logs out of Oracle Database, terminates SQL*Plus and returns control to the operating system.
In iSQL*Plus, commits or rolls back all pending changes, stops processing the current iSQL*Plus script and returns focus to the Input area. There is no way to access the return code in iSQL*Plus. IniSQL*Plus click the Logout button to exit the Oracle Database.
Commit on exit, or commit on termination of processing in iSQL*Plus, is performed regardless of the status of SET AUTOCOMMIT.
Terms
{EXIT | QUIT}
Can be used interchangeably (QUIT is a synonym for EXIT).
SUCCESS
Exits normally.
FAILURE
Exits with a return code indicating failure.
WARNING
Exits with a return code indicating warning.
COMMIT
Saves pending changes to the database before exiting.
n
Represents an integer you specify as the return code.
variable
Represents a user-defined or system variable (but not a bind variable), such as SQL.SQLCODE. EXIT variable exits with the value of variable as the return code.
:BindVariable
Represents a variable created in SQL*Plus with the VARIABLE command, and then referenced in PL/SQL, or other subprograms. :BindVariable exits the subprogram and returns you to SQL*Plus.
ROLLBACK
Executes a ROLLBACK statement and abandons pending changes to the database before exiting.
EXIT with no clauses commits and exits with a value of SUCCESS.
Usage
EXIT enables you to specify an operating system return code. This enables you to run SQL*Plus scripts in batch mode and to detect programmatically the occurrence of an unexpected event. The manner of detection is operating-system specific.
The key words SUCCESS, WARNING, and FAILURE represent operating-system dependent values. On some systems, WARNING and FAILURE may be indistinguishable.
The range of operating system return codes is also restricted on some operating systems. This limits the portability of EXIT n and EXIT variable between platforms. For example, on UNIX there is only one byte of storage for return codes; therefore, the range for return codes is limited to zero to 255.
If you make a syntax error in the EXIT options or use a non-numeric variable, SQL*Plus performs an EXIT FAILURE COMMIT.
For information on exiting conditionally, see the WHENEVER SQLERROR and WHENEVER OSERROR commands.
Examples
The following example commits all uncommitted transactions and returns the error code of the last executed SQL command or PL/SQL block:
EXIT SQL.SQLCODE
GET
GET is not available in iSQL*Plus. In iSQL*Plus use Load Script.
Syntax
GET [FILE] file_name[.ext] [LIS[T] | NOL[IST]]
Loads an operating system file into the SQL buffer.
In iSQL*Plus click the Load Script button to load a script into the Input area.
The buffer has no command history list and does not record SQL*Plus commands.
Terms
FILE
Keyword to specify that the following argument is the name of the script you want to load. This optional keyword is usually omitted.
If you want to load a script with the name file, because it is a command keyword, you need to put the name file in single quotes.
file_name[.ext]
Represents the file you wish to load (typically a script).
LIS[T]
Lists the contents of the file after it is loaded. This is the default.
NOL[IST]
Suppresses the listing.
Examples
To load a file called YEARENDRPT with the extension SQL into the buffer, enter
GET YEARENDRPT
HELP
Syntax
HELP | ? [topic]
where topic represents a SQL*Plus help topic, for example, COLUMN.
Accesses the SQL*Plus command-line help system. Enter HELP INDEX or ? INDEX for a list of topics. You can view SQL*Plus resources at http://otn.oracle.com/tech/sql_plus/ and the Oracle Database Library at http://otn.oracle.com/documentation/.
In iSQL*Plus, click the Help icon to access the iSQL*Plus Online Help.
Enter HELP or ? without topic to get help on the help system.
Usage
You can only enter one topic after HELP. You can abbreviate the topic (for example, COL for COLUMN). However, if you enter only an abbreviated topic and the abbreviation is ambiguous, SQL*Plus displays help for all topics that match the abbreviation. For example, if you enter
HELP EX
SQL*Plus displays the syntax for the EXECUTE command followed by the syntax for the EXIT command.
If you get a response indicating that help is not available, consult your database administrator.
Examples
To see a list of SQL*Plus commands for which help is available, enter
HELP INDEX
or
? INDEX
To see a single column list of SQL*Plus commands for which help is available, enter
HELP TOPICS
HOST
HOST is not available in iSQL*Plus.
Syntax
HO[ST] [command]
where command represents an operating system command.
Executes an operating system command without leaving SQL*Plus.
Enter HOST without command to display an operating system prompt. You can then enter multiple operating system commands. For information on returning to SQL*Plus, refer to the platform-specific Oracle documentation provided for your operating system.
Note:
Operating system commands entered from a SQL*Plus session using the HOST command do not affect the current SQL*Plus session. For example, setting an operating system environment variable only affects SQL*Plus sessions started subsequently.
You can disable HOST. For more information about disabling HOST, see " SQL*Plus Security".
Usage
In some operating systems, you can use a character in place of HOST such as "$" in Windows or "!" in UNIX, or you may not have access to the HOST command. See the platform-specific Oracle documentation provided for your operating system or ask your DBA for more information.
On some platforms, an _RC substitution variable may be created with a HOST return value that is operation system dependent. It is recommended that you do not use the _RC substitution variable in scripts as it is not portable.
SQL*Plus removes the SQLTERMINATOR (a semicolon by default) before the HOST command is issued. A workaround for this is to add another SQLTERMINATOR. See SET SQLT[ERMINATOR] {; | c | ON | OFF} for more information.
Examples
To execute a UNIX operating system command, ls *.sql, enter
HOST ls *.sql
To execute a Windows operating system command, dir *.sql, enter
HOST dir *.sql
INPUT
INPUT is not available in iSQL*Plus.
Syntax
I[NPUT] [text]
where text represents the text you wish to add.
Adds one or more new lines of text after the current line in the buffer. The buffer has no command history list and does not record SQL*Plus commands.
To add a single line, enter the text of the line after the command INPUT, separating the text from the command with a space. To begin the line with one or more spaces, enter two or more spaces between INPUT and the first non-blank character of text.
To add several lines, enter INPUT with no text. INPUT prompts you for each line. To leave INPUT, enter a null (empty) line or a period.
Usage
If you enter a line number at the command prompt larger than the number of lines in the buffer, and follow the number with text, SQL*Plus adds the text in a new line at the end of the buffer. If you specify zero (0) for the line number and follow the zero with text, then SQL*Plus inserts the line at the beginning of the buffer (that line becomes line 1).
Examples
Assume the SQL buffer contains the following command:
SELECT LAST_NAME, DEPARTMENT_ID, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
To add an ORDER BY clause to the query, enter
LIST 2
2* FROM EMP_DETAILS_VIEW
INPUT ORDER BY LAST_NAME
LIST 2 ensures that line 2 is the current line. INPUT adds a new line containing the ORDER BY clause after the current line. The SQL buffer now contains the following lines:
1 SELECT LAST_NAME, DEPARTMENT_ID, SALARY, COMMISSION_PCT
2 FROM EMP_DETAILS_VIEW
3* ORDER BY LAST_NAME
To add a two-line WHERE clause, enter
LIST 2
2* FROM EMP_DETAILS_VIEW
INPUT
3 WHERE JOB_ID = 'SA_MAN'
4 AND COMMISSION_PCT=.25
5
INPUT prompts you for new lines until you enter an empty line or a period. The SQL buffer now contains the following lines:
SELECT LAST_NAME, DEPARTMENT_ID, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'SA_MAN'
AND COMMISSION_PCT = .25
ORDER BY LAST_NAME
LIST
Syntax
L[IST] [n | n m | n * | n LAST | * | * n | * LAST | LAST]
Lists one or more lines of the SQL buffer.
The buffer has no command history list and does not record SQL*Plus commands. In SQL*Plus command-line you can also use ";" to list all the lines in the SQL buffer.
Terms
Term Description
n Lists line n.
n m Lists lines n through m.
n * Lists line n through the current line.
n LAST Lists line n through the last line.
*
Lists the current line.
LAST Lists the last line.
Enter LIST with no clauses, or ";" to list all lines. The last line listed becomes the new current line (marked by an asterisk).
Examples
To list the contents of the buffer, enter
LIST
or enter
;
1 SELECT LAST_NAME, DEPARTMENT_ID, JOB_ID
2 FROM EMP_DETAILS_VIEW
3 WHERE JOB_ID = 'SH_CLERK'
4* ORDER BY DEPARTMENT_ID
The asterisk indicates that line 4 is the current line.
To list the second line only, enter
LIST 2
The second line is displayed:
2* FROM EMP_DETAILS_VIEW
To list the current line (now line 2) to the last line, enter
LIST * LAST
You will then see this:
2 FROM EMP_DETAILS_VIEW
3 WHERE JOB_ID = 'SH_CLERK'
4* ORDER BY DEPARTMENT_ID
PASSWORD
Syntax
PASSW[ORD] [username]
where username specifies the user. If omitted, username defaults to the current user.
Enables you to change a password without echoing it on an input device. In iSQL*Plus, use the Password screen to change your password.
Usage
To change the password of another user, you must have been granted the appropriate privilege. See CONNECT for more information about changing your password.
Examples
If you want to change your current password, enter
PASSWORD
Changing password for your_password
Old password: your_password
New password: new_password
Retype new password: new_password
Password changed
If you are logged on as a DBA, and want to change the password for user johnw (currently identified by johnwpass) to johnwnewpass
PASSWORD johnw
Changing password for johnw
New password: johnwnewpass
Retype new password: johnwnewpass
Password changed
Passwords are not echoed to the screen, they are shown here for your convenience.
PAUSE
Syntax
PAU[SE] [text]
where text represents the text you wish to display.
Displays the specified text then waits for the user to press RETURN.
In iSQL*Plus, displays the Next Page button which the user must click to continue.
Enter PAUSE followed by no text to display two empty lines.
Usage
Because PAUSE always waits for the user's response, it is best to use a message that tells the user explicitly to press [Return].
PAUSE reads input from the terminal (if a terminal is available) even when you have designated the source of the command input as a file.
See SET PAU[SE] {ON | OFF | text} for information on pausing between pages of a report.
Examples
To print "Adjust paper and press RETURN to continue." and to have SQL*Plus wait for the user to press [Return], you might include the following PAUSE command in a script:
SET PAUSE OFF
PAUSE Adjust paper and press RETURN to continue.
SELECT ...
PRINT
Syntax
PRI[NT] [variable ...]
where variable ... represents names of bind variables whose values you want to display.
Displays the current values of bind variables. For more information on bind variables, see your PL/SQL User's Guide and Reference.
Enter PRINT with no variables to print all bind variables.
Usage
Bind variables are created using the VARIABLE command. See VARIABLE for more information and examples.
You can control the formatting of the PRINT output just as you would query output. For more information, see the formatting techniques described in " Formatting SQL*Plus Reports".
To automatically display bind variables referenced in a successful PL/SQL block or used in an EXECUTE command, use the AUTOPRINT clause of the SET command. See SET for more information.
Examples
The following example illustrates a PRINT command:
VARIABLE n NUMBER
BEGIN
:n := 1;
END;
/
PL/SQL procedure successfully completed.
PRINT n
N
1
PROMPT
Syntax
PRO[MPT] [text]
where text represents the text of the message you want to display.
Sends the specified message or a blank line to the user's screen. If you omit text, PROMPT displays a blank line on the user's screen.
Usage
You can use this command in scripts to give information to the user.
Examples
The following example shows the use of PROMPT in conjunction with ACCEPT in a script called ASKFORDEPT.SQL. ASKFORDEPT.SQL contains the following SQL*Plus and SQL commands:
PROMPTPROMPT Please enter a valid departmentPROMPT For example: 10SELECT DEPARTMENT_NAME FROM EMP_DETAILS_VIEWWHERE DEPARTMENT_ID = &NEWDEPT
Assume you run the file using START or @:
@ASKFORDEPT.SQL VAL1
@HTTP:machine_name.domain:port/ASKFORDEPT.SQL VAL1
Please enter a valid department
For example: 10
Department ID?>
You can enter a department number at the prompt Department ID?>. By default, SQL*Plus lists the line containing &NEWDEPT before and after substitution, and then displays the department name corresponding to the number entered at the Department ID?> prompt. You can use SET VERIFY OFF to prevent this behavior.
RECOVER
Syntax
RECOVER {general | managed | BEGIN BACKUP | END BACKUP}
where the general clause has the following syntax:
[AUTOMATIC] [FROM location]
{ {full_database_recovery | partial_database_recovery | LOGFILE filename}
[ {TEST | ALLOW integer CORRUPTION | parallel_clause } [TEST | ALLOW integer CORRUPTION | parallel_clause ]...]
| CONTINUE [DEFAULT] | CANCEL}
where the full_database_recovery clause has the following syntax:
[STANDBY] DATABASE
[ {UNTIL {CANCEL | TIME date | CHANGE integer} | USING BACKUP CONTROLFILE}
[UNTIL {CANCEL | TIME date | CHANGE integer} | USING BACKUP CONTROLFILE]...]
where the partial_database_recovery clause has the following syntax:
{TABLESPACE tablespace [, tablespace]...
| DATAFILE {filename | filenumber} [, filename | filenumber]...
| STANDBY {TABLESPACE tablespace [, tablespace]...
| DATAFILE {filename | filenumber} [, filename | filenumber]...}
UNTIL [CONSISTENT WITH] CONTROLFILE }
where the parallel clause has the following syntax:
{ NOPARALLEL | PARALLEL [ integer ] }
where the managed clause has the following syntax:
MANAGED STANDBY DATABASE recover_clause | cancel_clause | finish_clause
where the recover_clause has the following syntax:
{ { DISCONNECT [ FROM SESSION ] | { TIMEOUT integer | NOTIMEOUT } }
| { NODELAY | DEFAULT DELAY | DELAY integer } | NEXT integer
| { EXPIRE integer | NO EXPIRE } | parallel_clause
| USING CURRENT LOGFILE | UNTIL CHANGE integer
| THROUGH { [ THREAD integer ] SEQUENCE integer
| ALL ARCHIVELOG | { ALL | LAST | NEXT } SWITCHOVER} }
[ DISCONNECT [ FROM SESSION ] | { TIMEOUT integer | NOTIMEOUT }
| { NODELAY | DEFAULT DELAY | DELAY integer } | NEXT integer
| { EXPIRE integer | NO EXPIRE } | parallel_clause
| USING CURRENT LOGFILE | UNTIL CHANGE integer
| THROUGH { [ THREAD integer ] SEQUENCE integer
| ALL ARCHIVELOG | { ALL | LAST | NEXT } SWITCHOVER} ] ...
where the cancel_clause has the following syntax:
CANCEL [IMMEDIATE] [WAIT | NOWAIT]
where the finish_clause has the following syntax:
[ DISCONNECT [ FROM SESSION ] ] [ parallel_clause ]
FINISH [ SKIP [ STANDBY LOGFILE ] ] [ WAIT | NOWAIT ]
where the parallel_clause has the following syntax:
{ NOPARALLEL | PARALLEL [ integer ] }
Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database. For more information on the RECOVER command, see the Oracle Database Administrator's Guide, the ALTER DATABASE RECOVER command in the Oracle Database SQL Reference, and the Oracle Database Backup and Recovery Basics guide.
Because of possible network timeouts, it is recommended that you use SQL*Plus command-line, not iSQL*Plus, for long running DBA operations such as RECOVER.
Terms
AUTOMATIC
Automatically generates the name of the next archived redo log file needed to continue the recovery operation. Oracle Database uses the LOG_ARCHIVE_DEST (or LOG_ARCHIVE_DEST_ 1) and LOG_ARCHIVE_FORMAT parameters (or their defaults) to generate the target redo log filename. If the file is found, the redo contained in that file is applied. If the file is not found, SQL*Plus prompts you for a filename, displaying a generated filename as a suggestion.
If you do not specify either AUTOMATIC or LOGFILE, SQL*Plus prompts you for a filename, suggesting the generated filename. You can either accept the generated filename or replace it with a fully qualified filename. You can save time by using the LOGFILE clause to specify the filename if you know the archived filename differs from the filename Oracle Database would generate.
FROM location
Specifies the location from which the archived redo log file group is read. The value of location must be a fully specified file location. If you omit this parameter, SQL*Plus assumes the archived redo log file group is in the location specified by the initialization parameter LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_1. Do not specify FROM if you have set a file with SET LOGSOURCE.
full_database_recovery
Enables you to specify the recovery of a full database.
partial_database_recovery
Enables you to specify the recovery of individual tablespaces and datafiles.
LOGFILE
Continues media recovery by applying the specified redo log file. In interactive recovery mode (AUTORECOVERY OFF), if a bad log name is entered, errors for the bad log name are displayed and you are prompted to enter a new log name.
TEST
Specifies a trial recovery to detect possible problems. Redo is applied normally, but no changes are written to disk, and changes are rolled back at the end of the trial recovery. You can only use the TEST clause for a trial recovery if you have restored a backup. In the event of logfile corruption, specifies the number of corrupt blocks that can be tolerated while allowing recovery to proceed. During normal recovery, integer cannot exceed 1.
ALLOW integer CORRUPTION
In the event of logfile corruption, specifies the number of corrupt blocks that can be tolerated while allowing recovery to proceed. During normal recovery, integer cannot exceed 1.
parallel _clause
Enables you to specify the degree of parallel processing to use during the recovery operation.
CONTINUE
Continues multi-instance recovery after it has been interrupted to disable a thread.
CONTINUE DEFAULT
Continues recovery using the redo log file generated automatically by Oracle Database if no other logfile is specified. This is equivalent to specifying AUTOMATIC, except that Oracle Database does not prompt for a filename.
CANCEL
Terminates cancel-based recovery.
STANDBY DATABASE
Recovers the standby database using the control file and archived redo log files copied from the primary database. The standby database must be mounted but not open.
DATABASE
Recovers the entire database.
UNTIL CANCEL
Specifies an incomplete, cancel-based recovery. Recovery proceeds by prompting you with suggested filenames of archived redo log files, and recovery completes when you specify CANCEL instead of a filename.
UNTIL TIME
Specifies an incomplete, time-based recovery. Use single quotes, and the following format:
'YYYY-MM-DD:HH24:MI:SS'
UNTIL CHANGE
Specifies an incomplete, change-based recovery. integer is the number of the System Change Number (SCN) following the last change you wish to recover. For example, if you want to restore your database up to the transaction with an SCN of 9, you would specify UNTIL CHANGE 10.
USING BACKUP CONTROLFILE
Specifies that a backup of the control file be used instead of the current control file.
TABLESPACE
Recovers a particular tablespace. tablespace is the name of a tablespace in the current database. You may recover up to 16 tablespaces in one statement.
DATAFILE
Recovers a particular datafile. You can specify any number of datafiles.
STANDBY TABLESPACE
Reconstructs a lost or damaged tablespace in the standby database using archived redo log files copied from the primary database and a control file.
STANDBY DATAFILE
Reconstructs a lost or damaged datafile in the standby database using archived redo log files copied from the primary database and a control file.
UNTIL CONSISTENT WITH CONTROLFILE
Specifies that the recovery of an old standby datafile or tablespace uses the current standby database control file.
PARALLEL [integer]
SQL*Plus selects a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
The PARALLEL keyword overrides the RECOVERY_PARALLELISM initialization parameter. For more information about the PARALLEL keyword see the Oracle Real Application Clusters Quick Start guide.
Use integer to specify the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution processes.
NOPARALLEL
Specifies serial recovery processing. This is the default.
MANAGED STANDBY DATABASE
Specifies sustained standby recovery mode. This mode assumes that the standby database is an active component of an overall standby database architecture. A primary database actively archives its redo log files to the standby site. As these archived redo logs arrive at the standby site, they become available for use by a managed standby recovery operation. Sustained standby recovery is restricted to media recovery.
For more information on the parameters of this clause, see the Oracle Database Backup and Recovery Advanced User's Guide.
DISCONNECT
Indicates that the managed redo process (MRP) should apply archived redo files as a detached background process. Doing so leaves the current session available.
TIMEOUT
Specifies in minutes the wait period of the sustained recovery operation. The recovery process waits for integer minutes for a requested archived log redo to be available for writing to the standby database. If the redo log file does not become available within that time, the recovery process terminates with an error message. You can then issue the statement again to return to sustained standby recovery mode.
If you do not specify this clause, or if you specify NOTIMEOUT, the database remains in sustained standby recovery mode until you reissue the statement with the RECOVER CANCEL clause or until instance shutdown or failure.
NODELAY
Applies a delayed archivelog immediately to the standby database overriding any DELAY setting in the LOG_ARCHIVE_DEST_n parameter on the primary database. If you omit this clause, application of the archivelog is delayed according to the parameter setting. If DELAY was not specified in the parameter, the archivelog is applied immediately.
DEFAULT DELAY
Waits the default number of minutes specified in the LOG_ARCHIVE_DEST_n initialization parameter before applying the archived redo logs.
DELAY integer
Waits integer minutes before applying the archived redo logs.
NEXT integer
Applies the specified number of archived redo logs as soon as possible after they have been archived. It temporarily overrides any DELAY setting in the LOG_ARCHIVE_DEST_n parameter on the primary database, and any delay values set in an earlier SQL*Plus RECOVER command or an ALTER DATABASE RECOVER command.
EXPIRE integer
Specifies the number of minutes from the current time after which managed recovery terminates automatically.
NO EXPIRE
Disables a previously specified EXPIRE integer option.
USING CURRENT LOGFILE
Recovers redo from standby online logs as they are being filled, without requiring them to be archived in the standby database first.
UNTIL CHANGE integer
Processes managed recovery up to but not including the specified system change number (SCN).
THROUGH THREAD integer SEQUENCE integer
Terminates managed recovery based on archivelog thread number and sequence number. Managed recovery terminates when the corresponding archivelog has been applied. If omitted, THREAD defaults to 1.
THROUGH ALL ARCHIVELOG
Continues managed standby until all archivelogs have been recovered. You can use this statement to override a THROUGH THREAD integer SEQUENCE integer clause issued in an earlier statement. If the THROUGH clause is omitted, this is the default.
THROUGH ALL SWITCHOVER
Keeps managed standby recovery running through all switchover operations.
THROUGH LAST SWITCHOVER
Terminates managed standby recovery after the final end-of-redo archival indicator.
THROUGH NEXT SWITCHOVER
Terminates managed standby recovery after recovering the next end-of-redo archival indicator.
CANCEL (managed clause)
Terminates managed standby recovery after applying the current archived redo file. Session control returns when the recovery process terminates.
CANCEL IMMEDIATE
Terminates managed standby recovery after applying the current archived redo file, or after the next redo log file read, whichever comes first. Session control returns when the recovery process terminates.
CANCEL IMMEDIATE WAIT
Terminates managed standby recovery after applying the current archived redo file or after the next redo log file read, whichever comes first. Session control returns when the managed standby recovery terminates.
CANCEL IMMEDIATE cannot be issued from the same session that issued the RECOVER MANAGED STANDBY DATABASE statement.
CANCEL IMMEDIATE NOWAIT
Terminates managed standby recovery after applying the current archived redo file, or after the next redo log file read, whichever comes first. Session control returns immediately.
CANCEL NOWAIT
Terminates managed standby recovery after the next redo log file read and returns session control immediately.
FINISH
Recovers the current standby online logfiles of the standby database. This clause may be useful if the primary database fails. It overrides any delays specified for archivelogs, so that logs are applied immediately.
FINISH cannot be issued if you have also specified TIMEOUT, DELAY, EXPIRE or NEXT clauses.
Usage
You must have the OSDBA role enabled. You cannot use the RECOVER command when connected through the multi-threaded server.
To perform media recovery on an entire database (all tablespaces), the database must be mounted and closed, and all tablespaces requiring recovery must be online.
To perform media recovery on a tablespace, the database must be mounted and open, and the tablespace must be offline.
To perform media recovery on a datafile, the database can remain open and mounted with the damaged datafiles offline (unless the file is part of the SYSTEM tablespace).
Before using the RECOVER command you must have restored copies of the damaged datafiles from a previous backup. Be sure you can access all archived and online redo log files dating back to when that backup was made.
When another log file is required during recovery, a prompt suggests the names of files that are needed. The name is derived from the values specified in the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT. You should restore copies of the archived redo log files needed for recovery to the destination specified in LOG_ARCHIVE_DEST, if necessary. You can override the initialization parameters by setting the LOGSOURCE variable with the SET LOGSOURCE command.
During recovery you can accept the suggested log name by pressing return, cancel recovery by entering CANCEL instead of a log name, or enter AUTO at the prompt for automatic file selection without further prompting.
If you have enabled autorecovery (that is, SET AUTORECOVERY ON), recovery proceeds without prompting you with filenames. Status messages are displayed when each log file is applied. When normal media recovery is done, a completion status is returned.
Examples
To recover the entire database, enter
RECOVER DATABASE
To recover the database until a specified time, enter
RECOVER DATABASE UNTIL TIME 01-JAN-2001:04:32:00
To recover the two tablespaces ts_one and ts_two from the database, enter
RECOVER TABLESPACE ts_one, ts_two
To recover the datafile data1.db from the database, enter
RECOVER DATAFILE 'data1.db'
REMARK
Syntax
REM[ARK]
Begins a comment in a script. SQL*Plus does not interpret the comment as a command.
Usage
The REMARK command must appear at the beginning of a line, and the comment ends at the end of the line. A line cannot contain both a comment and a command.
A "–" at the end of a REMARK line is treated as a line continuation character.
For details on entering comments in scripts using the SQL comment delimiters, /* ... */, or the ANSI/ISO comment delimiter, - -, see "Placing Comments in Scripts".
Examples
The following script contains some typical comments:
REM COMPUTE uses BREAK ON REPORT to break on end of table
BREAK ON REPORT
COMPUTE SUM OF "DEPARTMENT 10" "DEPARTMENT 20" -
"DEPARTMENT 30" "TOTAL BY JOB_ID" ON REPORT
REM Each column displays the sums of salaries by job for
REM one of the departments 10, 20, 30.
SELECT JOB_ID,
SUM(DECODE( DEPARTMENT_ID, 10, SALARY, 0)) "DEPARTMENT 10",
SUM(DECODE( DEPARTMENT_ID, 20, SALARY, 0)) "DEPARTMENT 20",
SUM(DECODE( DEPARTMENT_ID, 30, SALARY, 0)) "DEPARTMENT 30",
SUM(SALARY) "TOTAL BY JOB_ID"
FROM EMP_DETAILS_VIEW
GROUP BY JOB_ID;
REPFOOTER
Syntax
REPF[OOTER] [PAGE] [printspec [text | variable] ...] | [ON | OFF]
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
Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition.
Enter REPFOOTER with no clauses to list the current REPFOOTER definition.
Terms
See the REPHEADER command for additional information on terms and clauses in the REPFOOTER command syntax.
Usage
If you do not enter a printspec clause before the text or variables, REPFOOTER left justifies the text or variables.
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.
Note:
If SET EMBEDDED is ON, the report footer is suppressed.
Examples
To define "END EMPLOYEE LISTING REPORT" as a report footer on a separate page and to center it, enter:
REPFOOTER PAGE CENTER 'END EMPLOYEE LISTING REPORT'
TTITLE RIGHT 'Page: ' FORMAT 999 SQL.PNO
SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000;
LAST_NAME SALARY
------------------------- ----------
King 24000
Kochhar 17000
De Haan 17000
Russell 14000
Partners 13500
Hartstein 13000
sum 98500
Page: 2
END EMPLOYEE LISTING REPORT
6 rows selected.
To suppress the report footer without changing its definition, enter
REPFOOTER OFF
REPHEADER
Syntax
REPH[EADER] [PAGE] [printspec [text | variable] ...] | [ON | OFF]
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
Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition.
Enter REPHEADER with no clauses to list the current REPHEADER definition.
Terms
These terms and clauses also apply to the REPFOOTER command.
PAGE
Begins a new page after printing the specified report header or before printing the specified report footer.
text
The report header or footer text. Enter text in single quotes if you want to place more than one word on a single line. The default is NULL.
variable
A substitution variable or any of the following system-maintained values. SQL.LNO is the current line number, SQL.PNO is the current page number, SQL.CODE is the current error code, SQL.RELEASE is the current Oracle Database release number, and SQL.USER is the current username.
To print one of these values, reference the appropriate variable in the report header or footer. You can use the FORMAT clause to format variable.
OFF
Turns the report header or footer off (suppresses its display) without affecting its definition.
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] 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 bold text on three consecutive lines, instead of bold.
FORMAT text
Specifies a format model that determines the format of 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 COLUMNfor 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 using the default format.
Usage
If you do not enter a printspec clause before the text or variables, REPHEADER left justifies the text or variables.
You can use any number of constants and variables in a printspec. SQL*Plus displays the constants and variables in the order you specify, positioning and formatting each constant or variable as specified by the printspec clauses that precede it.
Examples
To define "EMPLOYEE LISTING REPORT" as a report header on a separate page, and to center it, enter:
REPHEADER PAGE CENTER 'EMPLOYEE LISTING REPORT'
TTITLE RIGHT 'Page: ' FORMAT 999 SQL.PNO
SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000;
Page: 1
EMPLOYEE LISTING REPORT
Page: 2
LAST_NAME SALARY
------------------------- ----------
King 24000
Kochhar 17000
De Haan 17000
Russell 14000
Partners 13500
Hartstein 13000
sum 98500
6 rows selected.
To suppress the report header without changing its definition, enter:
REPHEADER OFF
RUN
Syntax
R[UN]
Lists and executes the SQL command or PL/SQL block currently stored in the SQL buffer.
The buffer has no command history list and does not record SQL*Plus commands.
Usage
RUN causes the last line of the SQL buffer to become the current line.
The slash command (/) functions similarly to RUN, but does not list the command in the SQL buffer on your screen. The SQL buffer always contains the last SQL statement or PL/SQL block entered.
Examples
Assume the SQL buffer contains the following script:
SELECT DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000
To RUN the script, enter
RUN
1 SELECT DEPARTMENT_ID
2 FROM EMP_DETAILS_VIEW
3 WHERE SALARY>12000
DEPARTMENT_ID
90
90
90
80
80
20
6 rows selected.
SAVE
SAVE is not available in iSQL*Plus. In iSQL*Plus use Save Script.
Syntax
SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
Saves the contents of the SQL buffer in an operating system script. In iSQL*Plus, click the Save Script button to save the Input area contents to a script.
The buffer has no command history list and does not record SQL*Plus commands.
Terms
FILE
Keyword to specify that the following argument is the name you want to give to the saved script. This optional keyword is usually omitted.
If you want to save the script with the name file, because it is a command keyword, you need to put the name file in single quotes.
file_name[.ext]
Specifies the script in which you wish to save the buffer's contents.
CREATE
Creates a new file with the name specified. This is the default behavior.
REP[LACE]
Replaces the contents of an existing file. If the file does not exist, REPLACE creates the file.
APP[END]
Adds the contents of the buffer to the end of the file you specify.
Usage
If you do not specify an extension, SQL*Plus assumes the default command-file extension (normally SQL). See SET SUF[FIX] {SQL | text} for information on changing this default extension.
If you wish to SAVE a file under a name identical to a SAVE command clause (CREATE, REPLACE, or APPEND), you must specify a file extension.
When you SAVE the contents of the SQL buffer, SAVE adds a line containing a slash (/) to the end of the file.
Examples
To save the contents of the buffer in a file named DEPTSALRPT with the extension SQL, enter
SAVE DEPTSALRPT
To save the contents of the buffer in a file named DEPTSALRPT with the extension OLD, enter
SAVE DEPTSALRPT.OLD
SET
Sets a system variable to alter the SQL*Plus environment settings for your current session, for example, to:
• customize HTML formatting
• enable or disable the printing of column headings
• set the number of lines per page
• set the display width for data
You also use the Preferences screens in iSQL*Plus to set system variables.
Syntax
SET system_variable value
where system_variable and value represent one of the clauses shown in the "SET System Variable Summary" table following.
Usage
SQL*Plus maintains system variables (also called SET command variables) to enable you to set up a particular environment for a SQL*Plus session. You can change these system variables with the SET command and list them with the SHOW command.
SET ROLE and SET TRANSACTION are SQL commands (see the Oracle Database SQL Reference for more information). When not followed by the keywords TRANSACTION or ROLE, SET is assumed to be a SQL*Plus command.
SET System Variable Summary
System Variable Description
SET APPI[NFO]{ON | OFF | text}
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.
SET ARRAY[SIZE] {15 | n}
Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time.
SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}
Controls when Oracle Database commits pending changes to the database.
SET AUTOP[RINT] {ON | OFF}
Sets the automatic printing of bind variables.
SET AUTORECOVERY [ON | OFF]
ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE).
SET BLO[CKTERMINATOR] {. | c | ON | OFF}
Sets the non-alphanumeric character used to end PL/SQL blocks to c.
SET CMDS[EP] {; | c | ON | OFF}
Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.
SET COLSEP { | text}
In iSQL*Plus, SET COLSEP determines the column separator character to be printed between column output that is rendered inside <PRE> tags.
Sets the text to be printed between selected columns.
SET COM[PATIBILITY]{V7 | V8 | NATIVE}
Specifies the version of Oracle Database to which you are currently connected.
SET CON[CAT] {. | c | ON | OFF}
Sets the character you can use to terminate a substitution variable reference if you wish to immediately follow the variable with a character that SQL*Plus would otherwise interpret as a part of the substitution variable name.
SET COPYC[OMMIT] {0 | n}
Controls the number of batches after which the COPY command commits changes to the database.
SET COPYTYPECHECK {ON | OFF}
Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.
SET DEF[INE] {& | c | ON | OFF}
Sets the character used to prefix variables to c.
SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]
Sets the depth of the level to which you can recursively describe an object.
SET ECHO {ON | OFF}
Controls whether the START command lists each command in a script as the command is executed.
*SET EDITF[ILE] file_name[.ext]
Sets the default filename for the EDIT command.
SET EMB[EDDED] {ON | OFF}
Controls where on a page each report begins.
SET ESC[APE] {\ | c | ON | OFF}
Defines the character you enter as the escape character.
SET FEED[BACK] {6 | n | ON | OFF}
Displays the number of records returned by a query when a query selects at least n records.
SET FLAGGER {OFF | ENTRY | INTERMED[IATE] | FULL}
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
*SET FLU[SH] {ON | OFF}
Controls when output is sent to the user's display device.
SET HEA[DING] {ON | OFF}
Controls printing of column headings in reports.
SET HEADS[EP] { | | c | ON | OFF}
Defines the character you enter as the heading separator character.
SET INSTANCE [instance_path | LOCAL]
Changes the default instance for your session to the specified instance path.
SET LIN[ESIZE] {80 | n}
SET LIN[ESIZE] {150 | n} in iSQL*Plus
Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
SET LOBOF[FSET] {1 | n}
Sets the starting position from which CLOB and NCLOB data is retrieved and displayed.
SET LOGSOURCE [pathname]
Specifies the location from which archive logs are retrieved during recovery.
SET LONG {80 | n}
Sets maximum width (in bytes) for displaying LONG, CLOB, NCLOB and XMLType values; and for copying LONG values.
SET LONGC[HUNKSIZE] {80 | n}
Sets the size (in bytes) of the increments in which SQL*Plus retrieves a LONG, CLOB, NCLOB or XMLType value.
SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}]
Outputs HTML marked up text, which is the output used by iSQL*Plus.
SET NEWP[AGE] {1 | n | NONE}
Sets the number of blank lines to be printed from the top of each page to the top title.
SET NULL text
Sets the text that represents a null value in the result of a SQL SELECT command.
SET NUMF[ORMAT] format
Sets the default format for displaying numbers.
SET NUM[WIDTH] {10 | n}
Sets the default width for displaying numbers.
SET PAGES[IZE] {14 | n}
Sets the number of lines in each page.
SET PAU[SE] {ON | OFF | text}
Enables you to control scrolling of your terminal when running reports.
SET RECSEP {WR[APPED] | EA[CH] | OFF}
RECSEP tells SQL*Plus where to make the record separation.
SET RECSEPCHAR { | c}
Display or print record separators.
SET SERVEROUT[PUT] {ON | OFF} [SIZE n] [FOR[MAT] {WRA[PPED]
| WOR[D_WRAPPED] | TRU[NCATED]}]
Controls whether to display the output (that is, DBMS_OUTPUT PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
*SET SHIFT[INOUT] {VIS[IBLE] | INV[ISIBLE]}
Enables correct alignment for terminals that display shift characters.
*SET SHOW[MODE] {ON | OFF}
Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET.
*SET SQLBL[ANKLINES] {ON | OFF}
Controls whether SQL*Plus puts blank lines within a SQL command or script.
SET SQLC[ASE] {MIX[ED] | LO[WER] | UP[PER]}
Converts the case of SQL commands and PL/SQL blocks just prior to execution.
*SET SQLCO[NTINUE] {> | text}
Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (–).
*SET SQLN[UMBER] {ON | OFF}
Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block.
SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
Sets the behavior or output format of VARIABLE to that of the release or version specified by x.y[.z].
*SET SQLPRE[FIX] {# | c}
Sets the SQL*Plus prefix character.
*SET SQLP[ROMPT] {SQL> | text}
Sets the SQL*Plus command prompt.
SET SQLT[ERMINATOR] {; | c | ON | OFF}
Sets the character used to end and execute SQL commands to c.
*SET SUF[FIX] {SQL | text}
Sets the default file that SQL*Plus uses in commands that refer to scripts.
*SET TAB {ON | OFF}
Determines how SQL*Plus formats white space in terminal output.
*SET TERM[OUT] {ON | OFF}
Controls the display of output generated by commands executed from a script.
*SET TI[ME] {ON | OFF}
Controls the display of the current time.
SET TIMI[NG] {ON | OFF}
Controls the display of timing statistics.
*SET TRIM[OUT] {ON | OFF}
Determines whether SQL*Plus puts trailing blanks at the end of each displayed line.
*SET TRIMS[POOL] {ON | OFF}
Determines whether SQL*Plus puts trailing blanks at the end of each spooled line.
SET UND[ERLINE] {- | c | ON | OFF}
Sets the character used to underline column headings in SQL*Plus reports to c.
SET VER[IFY] {ON | OFF}
Controls whether SQL*Plus lists the text of a SQL statement or PL/SQL command before and after SQL*Plus replaces substitution variables with values.
SET WRA[P] {ON | OFF}
Controls whether SQL*Plus truncates the display of a SELECTed row if it is too long for the current line width.
*SET command not available in iSQL*Plus.
SET APPI[NFO]{ON | OFF | text}
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.
This enables the performance and resource usage of each script to be monitored by your DBA. The registered name appears in the MODULE column of the V$SESSION and V$SQLAREA virtual tables. You can also read the registered name using the DBMS_APPLICATION_INFO.READ_MODULE procedure.
ON registers scripts invoked by the @, @@ or START commands. OFF disables registering of scripts. Instead, the current value of text is registered. text specifies the text to register when no script is being run or when APPINFO is OFF, which is the default. The default for text is "SQL*Plus". If you enter multiple words for text, you must enclose them in quotes. The maximum length for text is limited by the DBMS_APPLICATION_INFO package.
The registered name has the format nn@xfilename where: nn is the depth level of script; x is '<' when the script name is truncated, otherwise, it is blank; and filename is the script name, possibly truncated to the length allowed by the DBMS_APPLICATION_INFO package interface.
For more information on the DBMS_APPLICATION_INFO package, see the Oracle Database Performance Tuning Guide.
Example
To display the value of APPINFO, as it is SET OFF by default, enter
SET APPINFO ON
SHOW APPINFO
APPINFO is ON and set to "SQL*Plus"
To change the default text, enter
SET APPINFO 'This is SQL*Plus'
To make sure that registration has taken place, enter
VARIABLE MOD VARCHAR2(50)
VARIABLE ACT VARCHAR2(40)
EXECUTE DBMS_APPLICATION_INFO.READ_MODULE(:MOD, :ACT);
PL/SQL procedure successfully completed.
PRINT MOD
MOD
This is SQL*Plus
To change APPINFO back to its default setting, enter
SET APPINFO OFF
SET ARRAY[SIZE] {15 | n}
Sets the number of rows that SQL*Plus will fetch from the database at one time.
Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency.
SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}
Controls when Oracle Database commits pending changes to the database after SQL or PL/SQL commands.
ON commits pending changes to the database after Oracle Database executes each successful INSERT, UPDATE, or DELETE, or PL/SQL block. OFF suppresses automatic committing so that you must commit changes manually (for example, with the SQL command COMMIT). IMMEDIATE functions in the same manner as ON. n commits pending changes to the database after Oracle Database executesn successful SQL INSERT, UPDATE, or DELETE commands, or PL/SQL blocks. n cannot be less than zero or greater than 2,000,000,000. The statement counter is reset to zero after successful completion of n INSERT, UPDATE or DELETE commands or PL/SQL blocks, a commit, a rollback, or a SET AUTOCOMMIT command.
SET AUTOCOMMIT does not alter the commit behavior when SQL*Plus exits. Any uncommitted data is committed by default.
Note:
For this feature, a PL/SQL block is considered one transaction, regardless of the actual number of SQL commands contained within it.
SET AUTOP[RINT] {ON | OFF}
Sets the automatic printing of bind variables.
ON or OFF controls whether SQL*Plus automatically displays bind variables (referenced in a successful PL/SQL block or used in an EXECUTE command).
See PRINT for more information about displaying bind variables.
SET AUTORECOVERY [ON | OFF]
ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.
No interaction is needed, provided the necessary files are in the expected locations with the expected names. The filenames used are derived from the values of the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT.
OFF, the default option, requires that you enter the filenames manually or accept the suggested default filename given. See RECOVER for more information about database recovery.
Example
To set the recovery mode to AUTOMATIC, enter
SET AUTORECOVERY ON
RECOVER DATABASE
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE).
The report can include execution statistics and the query execution path.
OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics. Information about EXPLAIN PLAN is documented in the Oracle Database SQL Reference.
Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.
The TRACEONLY option may be useful to suppress the query data of large queries. If STATISTICS is specified, SQL*Plus still fetches the query data from the server, however, the data is not displayed.
The AUTOTRACE report is printed after the statement has successfully completed.
Information about Execution Plans and the statistics is documented in the Oracle Database Performance Tuning Guide.
When SQL*Plus produces a STATISTICS report, a second connection to the database is automatically created. This connection is closed when the STATISTICS option is set to OFF, or you log out of SQL*Plus.
The formatting of your AUTOTRACE report may vary depending on the version of the server to which you are connected and the configuration of the server.
AUTOTRACE is not available when FIPS flagging is enabled.
See Tracing Statements for more information on AUTOTRACE.
SET BLO[CKTERMINATOR] {. | c | ON | OFF}
Sets the character used to end PL/SQL blocks to c.
It cannot be an alphanumeric character or a whitespace. To execute the block, you must issue a RUN or / (slash) command.
OFF means that SQL*Plus recognizes no PL/SQL block terminator. ON changes the value of c back to the default period (.), not the most recently used character.
SET CMDS[EP] {; | c | ON | OFF}
Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.
ON or OFF controls whether you can enter multiple commands on a line. ON automatically sets the command separator character to a semicolon (;).
Example
To specify a title with TTITLE and format a column with COLUMN, both on the same line, enter
SET CMDSEP +
TTITLE LEFT 'SALARIES' + COLUMN SALARY FORMAT $99,999
SELECT LAST_NAME, SALARY FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'SH_CLERK';
SALARIES
LAST_NAME SALARY
------------------------- --------
Taylor $3,200
Fleaur $3,100
Sullivan $2,500
Geoni $2,800
Sarchand $4,200
Bull $4,100
Dellinger $3,400
Cabrio $3,000
Chung $3,800
Dilly $3,600
Gates $2,900
Perkins $2,500
Bell $4,000
Everett $3,900
McCain $3,200
Jones $2,800
SALARIES
LAST_NAME SALARY
------------------------- --------
Walsh $3,100
Feeney $3,000
OConnell $2,600
Grant $2,600
20 rows selected.
SET COLSEP { | text}
Sets the column separator character printed between columns in output.
If the COLSEP variable contains blanks or punctuation characters, you must enclose it with single quotes. The default value for text is a single space.
In multi-line rows, the column separator does not print between columns that begin on different lines. The column separator does not appear on blank lines produced by BREAK ... SKIP n and does not overwrite the record separator. See SET RECSEP {WR[APPED] | EA[CH] | OFF} for more information.
The Column Separator (SET COLSEP) is only used in iSQL*Plus when Preformatted Output is ON (SET MARKUP HTML PREFORMAT).
Example
To set the column separator to "|" enter
SET MARKUP HTML PREFORMAT ON
SET COLSEP '|'
SELECT LAST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID = 20;
LAST_NAME |JOB_ID |DEPARTMENT_ID
-------------------------|----------|-------------
Hartstein |MK_MAN | 20
Fay |MK_REP | 20
SET COM[PATIBILITY]{V7 | V8 | NATIVE}
Specifies the version of Oracle Database SQL syntax to use.
Set COMPATIBILITY to V7 for Oracle7, or to V8 for Oracle8 or later. COMPATIBILITY always defaults to NATIVE. Set COMPATIBILITY for the version of Oracle Database SQL syntax you want to use on the connected database, otherwise, you may be unable to run any SQL commands.
Example
To run a script, SALARY.SQL, created with Oracle7 SQL syntax, enter
SET COMPATIBILITY V7
START SALARY
After running the file, reset compatibility to NATIVE to run scripts created for Oracle Database 10g:
SET COMPATIBILITY NATIVE
Alternatively, you can add the command SET COMPATIBILITY V7 to the beginning of the script, and reset COMPATIBILITY to NATIVE at the end of the file.
SET CON[CAT] {. | c | ON | OFF}
Sets the character used to terminate a substitution variable reference when SQL*Plus would otherwise interpret the next character as a part of the variable name.
SQL*Plus resets the value of CONCAT to a period when you switch CONCAT on.
SET COPYC[OMMIT] {0 | n}
Controls the number of rows after which the COPY command commits changes to the database.
COPY commits rows to the destination database each time it copies n row batches. Valid values are zero to 5000. You can set the size of a batch with the ARRAYSIZE variable. If you set COPYCOMMIT to zero, COPY performs a commit only at the end of a copy operation.
SET COPYTYPECHECK {ON | OFF}
Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.
This is to facilitate copying to DB2, which requires that a CHAR be copied to a DB2 DATE.
SET DEF[INE] {& | c | ON | OFF}
Sets the character used to prefix substitution variables to c.
ON or OFF controls whether SQL*Plus will scan commands for substitution variables and replace them with their values. ON changes the value of c back to the default '&', not the most recently used character. The setting of DEFINE to OFF overrides the setting of the SCAN variable.
See SET SCAN {ON|OFF} (obsolete) for more information on the SCAN variable.
SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]
Sets the depth of the level to which you can recursively describe an object.
The valid range of the DEPTH clause is from 1 to 50. If you SET DESCRIBE DEPTH ALL, then the depth will be set to 50, which is the maximum level allowed. You can also display the line number and indentation of the attribute or column name when an object contains multiple object types. Use the SET LINESIZE command to control the width of the data displayed.
See DESCRIBE for more information about describing objects.
Example
To create an object type ADDRESS, enter
CREATE TYPE ADDRESS AS OBJECT
( STREET VARCHAR2(20),
CITY VARCHAR2(20)
);
/
Type created
To create the table EMPLOYEE that contains a nested object, EMPADDR, of type ADDRESS, enter
CREATE TABLE EMPLOYEE
(LAST_NAME VARCHAR2(30),
EMPADDR ADDRESS,
JOB_ID VARCHAR2(20),
SALARY NUMBER(7,2)
);
/
Table created
To describe the table EMPLOYEE to a depth of two levels, and to indent the output and display line numbers, enter:
SET DESCRIBE DEPTH 2 LINENUM ON INDENT ON
DESCRIBE employee
Name Null? Type
------------------------------- -------- --------------------------
1 LAST_NAME VARCHAR2(30)
2 EMPADDR ADDRESS
3 2 STREET VARCHAR2(20)
4 2 CITY VARCHAR2(20)
5 JOB_ID VARCHAR2(20)
6 SALARY NUMBER(7,2)
SET ECHO {ON | OFF}
Controls whether or not to echo commands in a script that is executed with @, @@ or START. ON displays the commands on screen. OFF suppresses the display. ECHO does not affect the display of commands you enter interactively or redirect to SQL*Plus from the operating system.
SET EDITF[ILE] file_name[.ext]
SET EDITFILE is not supported in iSQL*Plus
Sets the default filename for the EDIT command. See EDIT for more information about the EDIT command. The default filename for the EDIT command is afiedt.buf which is the SQL buffer. The buffer has no command history list and does not record SQL*Plus commands.
You can include a path and/or file extension. See SET SUF[FIX] {SQL | text} for information on changing the default extension. The default filename and maximum filename length are operating system specific.
SET EMB[EDDED] {ON | OFF}
Controls where on a page each report begins.
OFF forces each report to start at the top of a new page. ON enables a report to begin anywhere on a page. Set EMBEDDED to ON when you want a report to begin printing immediately following the end of the previously run report.
SET ESC[APE] {\ | c | ON | OFF}
Defines the character used as the escape character.
OFF undefines the escape character. ON enables the escape character. ON changes the value of c back to the default "\".
You can use the escape character before the substitution character (set through SET DEFINE) to indicate that SQL*Plus should treat the substitution character as an ordinary character rather than as a request for variable substitution.
Example
If you define the escape character as an exclamation point (!), then
SET ESCAPE !
ACCEPT v1 PROMPT 'Enter !&1:'
displays this prompt:
Enter &1:
To set the escape character back to the default value of \ (backslash), enter
SET ESCAPE ON
SET FEED[BACK] {6 | n | ON | OFF}
Displays the number of records returned by a script when a script selects at least n records.
ON or OFF turns this display on or off. Turning feedback ON sets n to 1. Setting feedback to zero is equivalent to turning it OFF.
SET FEEDBACK OFF also turns off the statement confirmation messages such as 'Table created' and 'PL/SQL procedure successfully completed' that are displayed after successful SQL or PL/SQL statements.
SET FLAGGER {OFF | ENTRY | INTERMED[IATE] | FULL}
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
If any non-standard constructs are found, the Oracle Database Server flags them as errors and displays the violating syntax. This is the equivalent of the SQL language ALTER SESSION SET FLAGGER command.
You may execute SET FLAGGER even if you are not connected to a database. FIPS flagging will remain in effect across SQL*Plus sessions until a SET FLAGGER OFF (or ALTER SESSION SET FLAGGER = OFF) command is successful or you exit SQL*Plus.
When FIPS flagging is enabled, SQL*Plus displays a warning for the CONNECT, DISCONNECT, and ALTER SESSION SET FLAGGER commands, even if they are successful.
SET FLU[SH] {ON | OFF}
SET FLUSH is not supported in iSQL*Plus
Controls when output is sent to the user's display device. OFF enables the operating system to buffer output. ON disables buffering. FLUSH only affects display output, it does not affect spooled output.
Use OFF only when you run a script non-interactively (that is, when you do not need to see output and/or prompts until the script finishes running). The use of FLUSH OFF may improve performance by reducing the amount of program I/O.
SET HEA[DING] {ON | OFF}
Controls printing of column headings in reports.
ON prints column headings in reports; OFF suppresses column headings.
The SET HEADING OFF command does not affect the column width displayed, it only suppresses the printing of the column header itself.
Example
To suppress the display of column headings in a report, enter
SET HEADING OFF
If you then run a SQL SELECT command
SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'AC_MGR';
the following output results:
Higgins 12000
To turn the display of column headings back on, enter
SET HEADING ON
SET HEADS[EP] { | | c | ON | OFF}
Defines the character used as a line break in column headings.
The heading separator character cannot be alphanumeric or white space. You can use the heading separator character in the COLUMN command and in the old forms of BTITLE and TTITLE to divide a column heading or title onto more than one line. ON or OFF turns heading separation on or off. When heading separation is OFF, SQL*Plus prints a heading separator character like any other character. ON changes the value of c back to the default "|".
The Heading Separator character (SET HEADSEP) is only supported in iSQL*Plus when the Preformatted Output preference is ON (SET MARKUP HTML PREFORMAT).
SET INSTANCE [instance_path | LOCAL]
Changes the default instance for your session to the specified instance path.
Using the SET INSTANCE command does not connect to a database. The default instance is used for commands when no instance is specified. Any commands preceding the first use of SET INSTANCE communicate with the default instance.
To reset the instance to the default value for your operating system, you can either enter SET INSTANCE with no instance_path or SET INSTANCE LOCAL.
Note, you can only change the instance when you are not currently connected to any instance. That is, you must first make sure that you have disconnected from the current instance, then set or change the instance, and reconnect to an instance in order for the new setting to be enabled.
This command may only be issued when Oracle Net is running. You can use any valid Oracle Net connect identifier as the specified instance path. See your operating system-specific Oracle Database documentation for a complete description of how your operating system specifies Oracle Net connect identifiers. The maximum length of the instance path is 64 characters.
Example
To set the default instance to "PROD1" enter
DISCONNECT
SET INSTANCE PROD1
To set the instance back to the default of local, enter
SET INSTANCE local
You must disconnect from any connected instances to change the instance.
SET LIN[ESIZE] {80 | n}
SET LIN[ESIZE] {150 | n} in iSQL*Plus
Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
It also controls the position of centered and right-aligned text in TTITLE, BTITLE, REPHEADER and REPFOOTER. Changing the linesize setting can affect text wrapping in output from the DESCRIBE command. DESCRIBE output columns are typically allocated a proportion of the linesize. Decreasing or increasing the linesize may give unexpected text wrapping in your display. You can define LINESIZE as a value from 1 to a maximum that is system dependent.
SET LOBOF[FSET] {1 | n}
Sets the starting position from which CLOB and NCLOB data is retrieved and displayed.
Example
To set the starting position from which a CLOB column's data is retrieved to the 22nd position, enter
SET LOBOFFSET 22
The CLOB data will wrap on your screen; SQL*Plus will not truncate until the 23rd character.
SET LOGSOURCE [pathname]
Specifies the location from which archive logs are retrieved during recovery.
The default value is set by the LOG_ARCHIVE_DEST initialization parameter in the Oracle Database initialization file, init.ora. Using the SET LOGSOURCE command without a pathname restores the default location.
Example
To set the default location of log files for recovery to the directory "/usr/oracle10/dbs/arch" enter
SET LOGSOURCE "/usr/oracle10/dbs/arch"
RECOVER DATABASE
SET LONG {80 | n}
Sets maximum width (in bytes) for displaying CLOB, LONG, NCLOB and XMLType values; and for copying LONG values.
The maximum value of n is 2,000,000,000 bytes.
Example
To set the maximum number of bytes to fetch for displaying and copying LONG values, to 500, enter
SET LONG 500
The LONG data will wrap on your screen; SQL*Plus will not truncate until the 501st byte. The default for LONG is 80 bytes.
SET LONGC[HUNKSIZE] {80 | n}
Sets the size (in bytes) of the increments SQL*Plus uses to retrieve a CLOB, LONG, NCLOB or XMLType value.
LONGCHUNKSIZE is not used for object relational queries such as CLOB, or NCLOB.
Example
To set the size of the increments in which SQL*Plus retrieves LONG values to 100 bytes, enter
SET LONGCHUNKSIZE 100
The LONG data will be retrieved in increments of 100 bytes until the entire value is retrieved or the value of SET LONG is reached, whichever is the smaller.
SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON |OFF}]
Outputs HTML marked up text, which is the output used by iSQL*Plus.
Beware of using options which generate invalid HTML output in iSQL*Plus as it may corrupt the browser screen. The HEAD and BODY options may be useful for dynamic reports and for reports saved to local files.
To be effective, SET MARKUP commands that change values in dynamic report output must occur before statements that produce query output. The first statement that produces query output triggers the output of information affected by SET MARKUP such as HEAD and TABLE settings. Subsequent SET MARKUP commands have no effect on the information already sent to the report.
SET MARKUP only specifies that SQL*Plus output will be HTML encoded. You must use SET MARKUP HTML ON SPOOL ON and the SQL*Plus SPOOL command to create and name a spool file, and to begin writing HMTL output to it. SET MARKUP has the same options and behavior as SQLPLUS -MARKUP.
See "MARKUP Options" for detailed information. For examples of usage, see SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON |OFF}] [PRE[FORMAT] {ON | OFF}], and "Generating HTML Reports from SQL*Plus".
Use the SHOW MARKUP command to view the status of MARKUP options.
Example
The following is a script which uses the SET MARKUP HTML command to enable HTML marked up text to be spooled to a specified file:
Note:
The SET MARKUP example command is laid out for readability using line continuation characters "–" and spacing. Command options are concatenated in normal entry.
Use your favorite text editor to enter the commands necessary to set up the HTML options and the query you want for your report.
SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title> -
STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"
SET ECHO OFF
SPOOL employee.htm
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON
As this script contains SQL*Plus commands, do not attempt to run it with / (slash) from the buffer because it will fail. Save the script in your text editor and use START to execute it:
START employee.sql
As well as writing the HTML spool file, employee.htm, the output is also displayed on screen because SET TERMOUT defaults to ON. You can view the spool file, employee.htm, in your web browser. It should appear something like the following:
Description of the illustration markup.gif
SET NEWP[AGE] {1 | n | NONE}
Sets the number of blank lines to be printed from the top of each page to the top title. A value of zero places a formfeed at the beginning of each page (including the first page) and clears the screen on most terminals. If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between the report pages.
SET NULL text
Sets the text displayed whenever a null value occurs in the result of a SQL SELECT command.
Use the NULL clause of the COLUMN command to override the setting of the NULL variable for a given column. The default output for a null is blank ("").
SET NUMF[ORMAT] format
Sets the default format for displaying numbers. Enter a number format for format. Enter
SET NUMFORMAT ""
to use the default field width and formatting model specified by SET NUMWIDTH.
SET NUM[WIDTH] {10 | n}
Sets the default width for displaying numbers. See the FORMAT clause of the COLUMN command and SET NUMF[ORMAT] format and SET NUM[WIDTH] {10 | n}. for number format descriptions.
COLUMN FORMAT settings take precedence over SET NUMFORMAT settings, which take precedence over SET NUMWIDTH settings.
SET PAGES[IZE] {14 | n}
Sets the number of rows on each page of output in iSQL*Plus, and the number of lines on each page of output in command-line and Windows GUI. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.
In iSQL*Plus, sets the number of rows displayed on each page. Error and informational messages are not counted in the page size, so pages may not always be exactly the same length. The default pagesize for iSQL*Plus is 24.
SET PAU[SE] {ON | OFF | text}
Enables you to control scrolling of your terminal when running reports. You need to first, SET PAUSE text, and then SET PAUSE ON if you want text to appear each time SQL*Plus pauses.
In command-line and Windows GUI, SET PAUSE ON pauses output at the beginning of each PAGESIZE number of lines of report output. Press Return to view more output. SET PAUSE text specifies the text to be displayed each time SQL*Plus pauses. Multiple words in text must be enclosed in single quotes.
You can embed terminal-dependent escape sequences in the PAUSE command. These sequences allow you to create inverse video messages or other effects on terminals that support such characteristics.
In iSQL*Plus, SET PAUSE ON displays the value of text, then pauses output and displays a Next Page button after PAGESIZE number of rows of report output. Click the Next Page button to view more report output. The Next Page button is not displayed on the final page of output.
SET RECSEP {WR[APPED] | EA[CH] | OFF}
RECSEP tells SQL*Plus where to make the record separation.
For example, if you set RECSEP to WRAPPED, SQL*Plus prints a record separator only after wrapped lines. If you set RECSEP to EACH, SQL*Plus prints a record separator following every row. If you set RECSEP to OFF, SQL*Plus does not print a record separator.
The Display Record Separator preference (SET RECSEP) is only supported in iSQL*Plus when Preformatted Output is On (SET MARKUP HTML PREFORMAT).
SET RECSEPCHAR { | c}
Defines the character to display or print to separate records.
A record separator consists of a single line of the RECSEPCHAR (record separating character) repeated LINESIZE times. The default is a single space.
SET SERVEROUT[PUT] {ON | OFF} [SIZE n] [FOR[MAT] {WRA[PPED]
| WOR[D_WRAPPED] | TRU[NCATED]}]
Controls whether to display output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
OFF suppresses the output of DBMS_OUTPUT.PUT_LINE; ON displays the output.
SIZE sets the number of bytes of the output that can be buffered within the Oracle Database server. The default for n is 2000. n cannot be less than 2000 or greater than 1,000,000.
Every server output line begins on a new output line.
When WRAPPED is enabled SQL*Plus wraps the server output within the line size specified by SET LINESIZE, beginning new lines when required.
When WORD_WRAPPED is enabled, each line of server output is wrapped within the line size specified by SET LINESIZE. Lines are broken on word boundaries. SQL*Plus left justifies each line, skipping all leading whitespace.
When TRUNCATED is enabled, each line of server output is truncated to the line size specified by SET LINESIZE.
For more information on DBMS_OUTPUT.PUT_LINE, see your Oracle Database Application Developer's Guide - Fundamentals.
Example
To enable text display in a PL/SQL block using DBMS_OUTPUT.PUT_LINE, enter
SET SERVEROUTPUT ON
The following example shows what happens when you execute an anonymous procedure with SET SERVEROUTPUT ON:
BEGIN
DBMS_OUTPUT.PUT_LINE('Task is complete');
END;
/
Task is complete.
PL/SQL procedure successfully completed.
The following example shows what happens when you create a trigger with SET SERVEROUTPUT ON:
CREATE TABLE SERVER_TAB (Letter CHAR);
CREATE TRIGGER SERVER_TRIG BEFORE INSERT OR UPDATE -
OR DELETE
ON SERVER_TAB
BEGIN
DBMS_OUTPUT.PUT_LINE('Task is complete.');
END;
/
Trigger Created.
INSERT INTO SERVER_TAB VALUES ('M');
DROP TABLE SERVER_TAB;
/* Remove SERVER_TAB from database */
Task is complete.
1 row created.
To set the output to WORD_WRAPPED, enter
SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
SET LINESIZE 20
BEGIN
DBMS_OUTPUT.PUT_LINE('If there is nothing left to do');
DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?');
END;
/
If there is nothing
left to do
shall we continue
with plan B?
To set the output to TRUNCATED, enter
SET SERVEROUTPUT ON FORMAT TRUNCATED
SET LINESIZE 20
BEGIN
DBMS_OUTPUT.PUT_LINE('If there is nothing left to do');
DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?');
END;
/
If there is nothing
shall we continue wi
SET SHIFT[INOUT] {VIS[IBLE] | INV[ISIBLE]}
SET SHIFTINOUT is not supported in iSQL*Plus
Enables correct alignment for terminals that display shift characters. The SET SHIFTINOUT command is useful for terminals which display shift characters together with data (for example, IBM 3270 terminals). You can only use this command with shift sensitive character sets (for example, JA16DBCS).
Use VISIBLE for terminals that display shift characters as a visible character (for example, a space or a colon). INVISIBLE is the opposite and does not display any shift characters.
Example
To enable the display of shift characters on a terminal that supports them, enter
SET SHIFTINOUT VISIBLE
SELECT LAST_NAME, JOB_ID FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000;
LAST_NAME JOB_ID
---------- ----------
:JJOO: :AABBCC:
:AA:abc :DDEE:e
where ":" = visible shift character
uppercase represents multibyte characters
lowercase represents singlebyte characters
SET SHOW[MODE] {ON | OFF}
SET SHOWMODE is not supported in iSQL*Plus
Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET. ON lists the settings; OFF suppresses the listing. SHOWMODE ON has the same behavior as the obsolete SHOWMODE BOTH.
SET SQLBL[ANKLINES] {ON | OFF}
SET SQLBLANKLINES is not supported in iSQL*Plus
Controls whether SQL*Plus puts blank lines within a SQL command or script. ON interprets blank lines and new lines as part of a SQL command or script. OFF, the default value, does not allow blank lines or new lines in a SQL command or script or script.
Enter the BLOCKTERMINATOR to stop SQL command entry without running the SQL command. Enter the SQLTERMINATOR character to stop SQL command entry and run the SQL statement.
Example
To allow blank lines in a SQL statement, enter
SET SQLBLANKLINES ON
REM Using the SQLTERMINATOR (default is ";")
REM Could have used the BLOCKTERMINATOR (default is ".")
SELECT *
FROM
DUAL
;
The following output results:
D
-
X
SET SQLC[ASE] {MIX[ED] | LO[WER] | UP[PER]}
Converts the case of SQL commands and PL/SQL blocks just prior to execution.
SQL*Plus converts all text within the command, including quoted literals and identifiers, to uppercase if SQLCASE equals UPPER, to lowercase if SQLCASE equals LOWER, and makes no changes if SQLCASE equals MIXED.
SQLCASE does not change the SQL buffer itself.
SET SQLCO[NTINUE] {> | text}
SET SQLCONTINUE is not supported in iSQL*Plus
Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (–).
Example
To set the SQL*Plus command continuation prompt to an exclamation point followed by a space, enter
SET SQLCONTINUE '! '
SQL*Plus will prompt for continuation as follows:
TTITLE 'MONTHLY INCOME' -
! RIGHT SQL.PNO SKIP 2 -
! CENTER 'PC DIVISION'
The default continuation prompt is "> ".
SET SQLN[UMBER] {ON | OFF}
SET SQLNUMBER is not supported in iSQL*Plus
Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block. ON sets the prompt to be the line number. OFF sets the prompt to the value of SQLPROMPT.
SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
Sets the behavior to that of the release or version specified by x.y[.z].
Where x is the version number, y is the release number, and z is the update number. For example, 8.1.7, 9.0.1 or 10.1. The features affected by SQLPLUSCOMPATIBILITY are tabulated in the SQL*Plus Compatibility Matrix shown. You can also set the value of SQLPLUSCOMPATIBILITY using the -C[OMPATIBILITY] argument of the SQLPLUS command when starting SQL*Plus from the command line.
The default setting for SQLPLUSCOMPATIBILITY is the value of the SQL*Plus client, which is 10.1.0.
It is recommended that you add SET SQLPLUSCOMPATIBILITY 10.1.0 to your scripts to maximize their compatibility with future versions of SQL*Plus.
SQL*Plus Compatibility Matrix
The SQL*Plus Compatibility Matrix tabulates behavior affected by each SQL*Plus compatibility setting. SQL*Plus compatibility modes can be set in three ways:
• You can include a SET SQLPLUSCOMPATIBILITY command in your site or user profile. On installation, there is no SET SQLPLUSCOMPATIBILITY setting in glogin.sql. Therefore the default compatibility is 10.1.
• You can use the SET SQLPLUSCOMPATIBILITY {x.y[.z]} command during a session to set the SQL*Plus behavior you want for that session.
The following table shows the release of SQL*Plus which introduced the behavior change, and hence the minimum value of SQLPLUSCOMPATIBILITY to obtain that behavior. For example, to obtain the earlier behavior of the VARIABLE command, you must either use a version of SQL*Plus earlier than 9.0.1, or you must use a SQLPLUSCOMPATIBILITY value of less than 9.0.1. The lowest value that can be set for SQLPLUSCOMPATIBILITY is 7.3.4
Table 13-4 Compatibility Matrix
Value Consequence When available
>=10.1 SHOW ERRORS sorts PL/SQL error messages using new columns only available in Oracle Database 10g. 10.1
>=10.1 SPOOL Options CREATE, REPLACE, SAVE were added which may affect filename parsing on some platforms. 10.1
>=10.1 SET SQLPROMPT 10.1
>=10.1 Whitespace characters are allowed in Windows file names that are enclosed in quotes. Some other special punctuation characters are now disallowed in Windows. 10.1
>=10.1 Glogin/login files are called for each reconnect. 10.1
<10.1 Uses the obsolete DOC> prompt when echoing /* comments. 10.1
>= 9.2 A wide column defined FOLD_AFTER may be displayed at the start of a new line. Otherwise it is incorrectly put at the end of the preceding line with a smaller width than expected. 9.2.
>= 9.0 Whitespace before a slash ("/") in a SQL statement is ignored and the slash is taken to mean execute the statement. Otherwise the slash is treated as part of the statement, for example, as a division sign. 9.0.1.4.
>= 9.0 The length specified for NCHAR and NVARCHAR2 types is characters. Otherwise the length may represent bytes or characters depending on the character set. 9.0.1
SET SQLPRE[FIX] {# | c}
SET SQLPREFIX is not supported in iSQL*Plus
Sets the SQL*Plus prefix character. While you are entering a SQL command or PL/SQL block, you can enter a SQL*Plus command on a separate line, prefixed by the SQL*Plus prefix character. SQL*Plus will execute the command immediately without affecting the SQL command or PL/SQL block that you are entering. The prefix character must be a non-alphanumeric character.
SET SQLP[ROMPT] {SQL> | text}
SET SQLPROMPT is not supported in iSQL*Plus
Sets the SQL*Plus command prompt. SET SQLPROMPT substitute variables dynamically. This enables the inclusion of runtime variables such as the current connection identifier. Substitution variables used in SQLPROMPT do not have to be prefixed with '&', and they can be used and accessed like any other substitution variable. Variable substitution is not attempted for 'SQL' in the default prompt.
Variable substitution occurs each time SQLPROMPT is SET. If SQLPROMPT is included in glogin.sql, then substitution variables in SQLPROMPT are refreshed with each login or connect.
Example
You need the Select Any Table privilege to successfully run the following example scripts.
To change your SQL*Plus prompt to display your connection identifier, enter:
SET SQLPROMPT "_CONNECT_IDENTIFIER > "
To set the SQL*Plus command prompt to show the current user, enter
SET SQLPROMPT "_USER > "
To change your SQL*Plus prompt to display your the current date, the current user and the users privilege level, enter:
SET SQLPROMPT "_DATE _USER _PRIVILEGE> "
To change your SQL*Plus prompt to display a variable you have defined, enter:
DEFINE mycon = Prod1
SET SQLPROMPT "mycon> "
Prod1>
Text in nested quotes is not parsed for substitution. To have a SQL*Plus prompt of your username, followed by "@", and then your connection identifier, enter:
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "
SET SQLT[ERMINATOR] {; | c | ON | OFF}
Sets the character used to end script or data entry for PL/SQL blocks or SQL statements, to execute the script and to load it into the buffer.
It cannot be an alphanumeric character or a whitespace. OFF means that SQL*Plus recognizes no command terminator; you terminate a SQL command by entering an empty line or a slash (/). If SQLBLANKLINES is set ON, you must use the BLOCKTERMINATOR to terminate a SQL command. ON resets the terminator to the default semicolon (;).
SET SUF[FIX] {SQL | text}
SET SUFFIX is not supported in iSQL*Plus
Sets the default file extension that SQL*Plus uses in commands that refer to scripts. SUFFIX does not control extensions for spool files.
Example
To change the default command-file extension from the default, .SQL to .TXT, enter
SET SUFFIX TXT
If you then enter
GET EXAMPLE
SQL*Plus will look for a file named EXAMPLE.TXT instead of EXAMPLE.SQL.
SET TAB {ON | OFF}
SET TAB is not supported in iSQL*Plus
Determines how SQL*Plus formats white space in terminal output. OFF uses spaces to format white space in the output. ON uses the TAB character. TAB settings are every eight characters. The default value for TAB is system dependent.
SET TERM[OUT] {ON | OFF}
SET TERMOUT is not supported in iSQL*Plus
Controls the display of output generated by commands in a script that is executed with @, @@ or START. OFF suppresses the display so that you can spool output to a file without displaying the output on screen. ON displays the output on screen. TERMOUT OFF does not affect output from commands you enter interactively or redirect to SQL*Plus from the operating system.
SET TI[ME] {ON | OFF}
SET TIME is not supported in iSQL*Plus
Controls the display of the current time. ON displays the current time before each command prompt. OFF suppresses the time display.
SET TIMI[NG] {ON | OFF}
Controls the display of timing statistics.
ON displays timing statistics on each SQL command or PL/SQL block run. OFF suppresses timing of each command.
See TIMING for information on timing multiple commands.
SET TRIM[OUT] {ON | OFF}
SET TRIMOUT is not supported in iSQL*Plus
Determines whether SQL*Plus puts trailing blanks at the end of each displayed line. ON removes blanks at the end of each line, improving performance especially when you access SQL*Plus from a slow communications device. OFF enables SQL*Plus to display trailing blanks. TRIMOUT ON does not affect spooled output.
SET TRIMS[POOL] {ON | OFF}
SET TRIMSPOOL is not supported in iSQL*Plus
Determines whether SQL*Plus puts trailing blanks at the end of each spooled line. ON removes blanks at the end of each line. OFF enables SQL*Plus to include trailing blanks. TRIMSPOOL ON does not affect terminal output.
SET UND[ERLINE] {- | c | ON | OFF}
Sets the character used to underline column headings in reports. The underline character cannot be an alphanumeric character or a white space. ON or OFF turns underlining on or off. ON changes the value of c back to the default "-".
SET UNDERLINE is supported in iSQL*Plus when SET MARKUP HTML PREFORMAT ON is set.
SET VER[IFY] {ON | OFF}
Controls whether to list the text of a SQL statement or PL/SQL command before and after replacing substitution variables with values. ON lists the text; OFF suppresses the listing.
SET WRA[P] {ON | OFF}
Controls whether to truncate the display of a selected row if it is too long for the current line width. OFF truncates the selected row; ON enables the selected row to wrap to the next line.
Use the WRAPPED and TRUNCATED clauses of the COLUMN command to override the setting of WRAP for specific columns.
SHOW
Syntax
SHO[W] option
where option represents one of the following terms or clauses:
system_variable
ALL
BTI[TLE]
ERR[ORS] [ { FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
| VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS } [schema.]name]
LNO
PARAMETERS [parameter_name]
PNO
RECYC[LEBIN] [original_name]
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L] (Not available in iSQL*Plus)
SQLCODE
TTI[TLE]
USER
Shows the value of a SQL*Plus system variable or the current SQL*Plus environment. SHOW SGA requires a DBA privileged login.
Terms
system_variable
Represents any system variable set by the SET command.
ALL
Lists the settings of all SHOW options, except ERRORS and SGA, in alphabetical order.
BTI[TLE]
Shows the current BTITLE definition.
ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
| VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
Shows the compilation errors of a stored procedure (includes stored functions, procedures, and packages). After you use the CREATE command to create a stored procedure, a message is displayed if the stored procedure has any compilation errors. To see the errors, you use SHOW ERRORS.
When you specify SHOW ERRORS with no arguments, SQL*Plus shows compilation errors for the most recently created or altered stored procedure. When you specify the type (function, procedure, package, package body, trigger, view, type, type body, dimension, or java class) and the name of the PL/SQL stored procedure, SQL*Plus shows errors for that stored procedure. For more information on compilation errors, see your PL/SQL User's Guide and Reference.
schema contains the named object. If you omit schema, SHOW ERRORS assumes the object is located in your current schema.
SHOW ERRORS output displays the line and column number of the error (LINE/COL) as well as the error itself (ERROR). LINE/COL and ERROR have default widths of 8 and 65, respectively. You can use the COLUMN command to alter the default widths.
LNO
Shows the current line number (the position in the current page of the display and/or spooled output).
PARAMETERS [parameter_name]
Displays the current values for one or more initialization parameters. You can use a string after the command to see a subset of parameters whose names include that string. For example, if you enter:
SHOW PARAMETERS COUNT
NAME TYPE VALUE
------------------------------ ----- -----
db_file_multiblock_read_count integer 12
spin_count integer 0
The SHOW PARAMETERS command, without any string following the command, displays all initialization parameters.
The column names and formats used in the SHOW PARAMETERS output is set in the site profile file, glogin.sql. The value column display may be truncated.
Your output may vary depending on the version and configuration of the Oracle Database server to which you are connected. You need SELECT ON V_$PARAMETER object privileges to use the PARAMETERS clause, otherwise you will receive a message
ORA-00942: table or view does not exist
PNO
Shows the current page number.
RECYC[LEBIN] [original_name]
Shows objects in the recycle bin that can be reverted with the FLASHBACK BEFORE DROP command. You do not need to remember column names, or interpret the less readable output from the query:
SELECT * FROM USER_RECYCLEBIN
The query returns four columns displayed in the following order:
Column Name Description
ORIGINAL NAME Shows the original name used when creating the object.
RECYCLEBIN NAME Shows the name used to identify the object in the recyclebin.
OBJECT TYPE Shows the type of the object.
DROP TIME Shows the time when the object was dropped.
The output columns can be formatted with the COLUMN command. The default COLUMN formatting is in the site profile, glogin.sql.
For DBAs, the command lists their own objects as they have their own user_recyclebin view.
REL[EASE]
Shows the release number of Oracle Database that SQL*Plus is accessing.
REPF[OOTER]
Shows the current REPFOOTER definition.
REPH[EADER]
Shows the current REPHEADER definition.
SPOO[L]
Shows whether output is being spooled.
SGA
Displays information about the current instance's System Global Area. You need SELECT ON V_$SGA object privileges otherwise you will receive a message
ORA-00942: table or view does not exist
SQLCODE
Shows the value of SQL.SQLCODE (the SQL return code of the most recent operation).
TTI[TLE]
Shows the current TTITLE definition.
USER
Shows the username you are currently using to access SQL*Plus. If you connect as "/ AS SYSDBA", then the SHOW USER command displays
USER is "SYS"
Examples
To display information about the SGA, enter
SHOW SGA
Total System Global Area 7629732 bytes
Fixed Size 60324 bytes
Variable Size 6627328 bytes
Database Buffers 409600 bytes
Redo Buffers 532480 bytes
The following example illustrates how to create a stored procedure and then show its compilation errors:
CONNECT SYSTEM/MANAGER
CREATE PROCEDURE HR.PROC1 AS
BEGIN
:P1 := 1;
END;
/
Warning: Procedure created with compilation errors.
SHOW ERRORS PROCEDURE PROC1
NO ERRORS.
SHOW ERRORS PROCEDURE HR.PROC1
Errors for PROCEDURE HR PROC1:
LINE/COL ERROR
3/3 PLS-00049: bad bind variable 'P1'
To show whether AUTORECOVERY is enabled, enter
SHOW AUTORECOVERY
AUTORECOVERY ON
To display the connect identifier for the default instance, enter
SHOW INSTANCE
INSTANCE "LOCAL"
To display the location for archive logs, enter
SHOW LOGSOURCE
LOGSOURCE "/usr/oracle90/dbs/arch"
To display objects that can be reverted with the FLASHBACK commands where CJ1 and ABC were objects dropped, enter:
SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-------------- ------------------ ------------ --------------------
CJ1 RB$$29458$TABLE$0 TABLE 2003-01-22:14:54:07
ABC RB$$29453$TABLE$0 TABLE 2003-01-20:18:50:29
To restore CJ1, enter
FLASHBACK TABLE CJ1 TO BEFORE DROP;
SHUTDOWN
Syntax
SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]]
Shuts down a currently running Oracle Database instance, optionally closing and dismounting a database.
Terms
ABORT
Proceeds with the fastest possible shutdown of the database without waiting for calls to complete or users to disconnect.
Uncommitted transactions are not rolled back. Client SQL statements currently being processed are terminated. All users currently connected to the database are implicitly disconnected and the next database startup will require instance recovery.
You must use this option if a background process terminates abnormally.
IMMEDIATE
Does not wait for current calls to complete or users to disconnect from the database.
Further connects are prohibited. The database is closed and dismounted. The instance is shutdown and no instance recovery is required on the next database startup.
NORMAL
NORMAL is the default option which waits for users to disconnect from the database.
Further connects are prohibited. The database is closed and dismounted. The instance is shutdown and no instance recovery is required on the next database startup.
TRANSACTIONAL [LOCAL]
Performs a planned shutdown of an instance while allowing active transactions to complete first. It prevents clients from losing work without requiring all users to log off.
No client can start a new transaction on this instance. Attempting to start a new transaction results in disconnection. After completion of all transactions, any client still connected to the instance is disconnected. Now the instance shuts down just as it would if a SHUTDOWN IMMEDIATE statement was submitted. The next startup of the database will not require any instance recovery procedures.
The LOCAL mode specifies a transactional shutdown on the local instance only, so that it only waits on local transactions to complete, not all transactions. This is useful, for example, for scheduled outage maintenance.
Usage
SHUTDOWN with no arguments is equivalent to SHUTDOWN NORMAL.
You must be connected to a database as SYSOPER, or SYSDBA. You cannot connect through a multi-threaded server. See CONNECT for more information about connecting to a database.
Examples
To shutdown the database in normal mode, enter
SHUTDOWN
Database closed.
Database dismounted.
Oracle instance shut down.
SPOOL
SPOOL is not available in iSQL*Plus.
Syntax
SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
Stores query results in a file, or optionally sends the file to a printer. In iSQL*Plus, use the preference settings to direct output to a file.
Terms
file_name[.ext]
Represents the name of the file to which you wish to spool. SPOOL followed by file_name begins spooling displayed output to the named file. If you do not specify an extension, SPOOL uses a default extension (LST or LIS on most systems).
CRE[ATE]
Creates a new file with the name specified.
REP[LACE]
Replaces the contents of an existing file. If the file does not exist, REPLACE creates the file. This is the default behavior.
APP[END]
Adds the contents of the buffer to the end of the file you specify.
OFF
Stops spooling.
OUT
Stops spooling and sends the file to your computer's standard (default) printer. This option is not available on some operating systems.
Enter SPOOL with no clauses to list the current spooling status.
Usage
To spool output generated by commands in a script without displaying the output on the screen, use SET TERMOUT OFF. SET TERMOUT OFF does not affect output from commands that run interactively.
You must use quotes around file names containing white space.
To create a valid HTML file using SPOOL APPEND commands, you must use PROMPT or a similar command to create the HTML page header and footer. The SPOOL APPEND command does not parse HTML tags.
Use SET SQLPLUSCOMPAT[IBILITY] 9.2 or earlier to use the earlier behavior. However, this will also disable other functionality that is available in SQL*Plus Release 10.1. See "SQL*Plus Compatibility Matrix" to determine what functionality is controlled by the SET SQLPLUSCOMPAT[IBILITY] command.
Examples of SPOOL Command
To record your output in the new file DIARY using the default file extension, enter
SPOOL DIARY CREATE
To append your output to the existing file DIARY, enter
SPOOL DIARY APPEND
To record your output to the file DIARY, overwriting the existing content, enter
SPOOL DIARY REPLACE
To stop spooling and print the file on your default printer, enter
SPOOL OUT
START
Syntax
STA[RT] {url | file_name[.ext] } [arg...]
Runs the SQL*Plus statements in the specified script. The script can be called from the local file system or from a web server. Only the url form is supported in iSQL*Plus. You can pass values to script variables in the usual way.
Terms
url
Specifies the Uniform Resource Locator of a script to run on the specified web server. SQL*Plus supports HTTP and FTP protocols, but not HTTPS. HTTP authentication in the form http://username:password@machine_name.domain... is not supported in this release.
file_name[.ext]
The script you wish to execute. The file can contain any command that you can run interactively.
If you do not specify an extension, SQL*Plus assumes the default command-file extension (normally SQL). See SET SUF[FIX] {SQL | text} for information on changing the default extension.
When you enter START file_name.ext, SQL*Plus searches for a file with the filename and extension you specify in the current default directory. If SQL*Plus does not find such a file, SQL*Plus will search a system-dependent path to find the file. Some operating systems may not support the path search. See the platform-specific Oracle documentation provided for your operating system for specific information related to your operating system environment.
arg ...
Data items you wish to pass to parameters in the script. If you enter one or more arguments, SQL*Plus substitutes the values into the parameters (&1, &2, and so forth) in the script. The first argument replaces each occurrence of &1, the second replaces each occurrence of &2, and so on.
The START command defines the parameters with the values of the arguments; if you START the script again in this session, you can enter new arguments or omit the arguments to use the old values.
See "Defining Substitution Variables" and "Substitution Variables in iSQL*Plus" for more information on using parameters.
Usage
All previous settings like COLUMN command settings stay in effect when the script starts. If the script changes any setting, then this new value stays in effect after the script has finished
The @ ("at" sign) and @@ (double "at" sign) commands function similarly to START. Disabling the START command in the Product User Profile also disables the @ and @@ commands. See @ ("at" sign)and @@ (double "at" sign) for further information on these commands. See "Disabling SQL*Plus, SQL, and PL/SQL Commands" for more information.
The EXIT or QUIT command in a script terminates SQL*Plus.
Examples
A file named PROMOTE with the extension SQL, used to promote employees, might contain the following command:
SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARYFROM EMP_DETAILS_VIEWWHERE JOB_ID='&1' AND SALARY>&2;
To run this script, enter
START PROMOTE ST_MAN 7000
or if it is located on a web server, enter a command in the form:
START HTTP:machine_name.domain:port/PROMOTE.SQL ST_MAN 7000
Where machine_name.domain must be replaced by the host.domain name, and port by the port number used by the web server where the script is located.
The following command is executed:
SELECT LAST_NAME, LAST_NAME
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='ST_MAN' AND SALARY>7000;
and the results displayed.
STARTUP
Syntax
STARTUP options | upgrade_options
where options has the following syntax:
[FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] |
[ OPEN [open_options] [dbname] ] | NOMOUNT ]
where open_options has the following syntax:
READ {ONLY | WRITE [RECOVER]} | RECOVER
and where upgrade_options has the following syntax:
[PFILE=filename] {UPGRADE | DOWNGRADE} [QUIET]
Starts an Oracle Database instance with several options, including mounting and opening a database.
Terms
FORCE
Shuts down the current Oracle Database instance (if it is running) with SHUTDOWN mode ABORT, before restarting it. If the current instance is running and FORCE is not specified, an error results. FORCE is useful while debugging and under abnormal circumstances. It should not normally be used.
RESTRICT
Only enables Oracle Database users with the RESTRICTED SESSION system privilege to connect to the database. Later, you can use the ALTER SYSTEM command to disable the restricted session feature.
PFILE=filename
Causes the specified parameter file to be used while starting up the instance. If PFILE is not specified, then the default STARTUP parameter file is used. The default file used is platform specific. For example, the default file is $ORACLE_HOME/dbs/init$ORACLE_SID.ora on UNIX, and %ORACLE_HOME%\database\initORCL.ora on Windows.
QUIET
Suppresses the display of System Global Area information for the starting instance.
MOUNT dbname
Mounts a database but does not open it.
dbname is the name of the database to mount or open. If no database name is specified, the database name is taken from the initialization parameter DB_NAME.
OPEN
Mounts and opens the specified database.
NOMOUNT
Causes the database not to be mounted upon instance startup.
Cannot be used with MOUNT, or OPEN.
RECOVER
Specifies that media recovery should be performed, if necessary, before starting the instance. STARTUP RECOVER has the same effect as issuing the RECOVER DATABASE command and starting an instance. Only complete recovery is possible with the RECOVER option.
Recovery proceeds, if necessary, as if AUTORECOVERY is set to ON, regardless of whether or not AUTORECOVERY is enabled. If a redo log file is not found in the expected location, recovery continues as if AUTORECOVERY is disabled, by prompting you with the suggested location and name of the subsequent log files that need to be applied.
UPGRADE
Starts the database in OPEN UPGRADE mode and sets system initialization parameters to specific values required to enable database upgrade scripts to be run. UPGRADE should only be used when a database is first started with a new version of the Oracle Database Server.
See the Oracle Database Upgrade Guide for details about preparing for, testing and implementing a database version upgrade.
When run, upgrade scripts transform an installed version or release of an Oracle database into a later version, for example, to upgrade an Oracle9i database to Oracle Database 10g. Once the upgrade completes, the database should be shut down and restarted normally.
DOWNGRADE
Starts the database in OPEN DOWNGRADE mode and sets system initialization parameters to specific values required to enable database downgrade scripts to be run.
See the Oracle Database Upgrade Guide for details about preparing for, testing and implementing a database version downgrade.
When run, downgrade scripts transform an installed version or release of Oracle Database into a previous version, for example, to downgrade an Oracle10g database to an Oracle9i database. Once the downgrade completes, the database should be shut down and restarted normally.
Usage
You must be connected to a database as SYSOPER, or SYSDBA. You cannot be connected through a multi-threaded server.
STARTUP with no arguments is equivalent to STARTUP OPEN.
STARTUP OPEN RECOVER mounts and opens the database even when recovery fails.
Examples
To start an instance using the standard parameter file, mount the default database, and open the database, enter
STARTUP
or enter
STARTUP OPEN database
To start an instance using the standard parameter file, mount the default database, and open the database, enter
STARTUP FORCE RESTRICT MOUNT
To start an instance using the parameter file TESTPARM without mounting the database, enter
STARTUP PFILE=testparm NOMOUNT
To shutdown a particular database, immediately restart and open it, allow access only to users with the RESTRICTED SESSION privilege, and use the parameter file MYINIT.ORA. enter
STARTUP FORCE RESTRICT PFILE=myinit.ora OPEN database
To startup an instance and mount but not open a database, enter
CONNECT / as SYSDBA
Connected to an idle instance.
STARTUP MOUNT
ORACLE instance started.
Total System Global Area 7629732 bytes
Fixed Size 60324 bytes
Variable Size 6627328 bytes
Database Buffers 409600 bytes
Redo Buffers 532480 bytes
STORE
STORE is not available in iSQL*Plus.
Syntax
STORE SET file_name[.ext] [ CRE[ATE | REP[LACE] | APP[END]]
Saves attributes of the current SQL*Plus environment in a script.
Terms
See SAVE for information on the other terms and clauses in the STORE command syntax.
SET
Saves the values of the system variables.
Usage
This command creates a script which can be executed with the START, @ ("at" sign) or @@ (double "at" sign) commands.
If you want to store a file under a name identical to a STORE command clause (that is, CREATE, REPLACE or APPEND), you must put the name in single quotes or specify a file extension.
Examples
To store the current SQL*Plus system variables in a file named DEFAULTENV with the default command-file extension, enter
STORE SET DEFAULTENV
To append the current SQL*Plus system variables to an existing file called DEFAULTENV with the extension OLD, enter
STORE SET DEFAULTENV.OLD APPEND
TIMING
Syntax
TIMI[NG] [START text | SHOW | STOP]
Records timing data for an elapsed period of time, lists the current timer's name and timing data, or lists the number of active timers.
Terms
START text
Sets up a timer and makes text the name of the timer. You can have more than one active timer by STARTing additional timers before STOPping the first; SQL*Plus nests each new timer within the preceding one. The timer most recently STARTed becomes the current timer.
SHOW
Lists the current timer's name and timing data.
STOP
Lists the current timer's name and timing data, then deletes the timer. If any other timers are active, the next most recently STARTed timer becomes the current timer.
Enter TIMING with no clauses to list the number of active timers. For other information about TIMING, see SET AUTOTRACE
Usage
You can use this data to do a performance analysis on any commands or blocks run during the period.
Refer to the SET TIMING command for information on automatically displaying timing data after each SQL command or PL/SQL block you run.
To delete all timers, use the CLEAR TIMING command.
Examples
To create a timer named SQL_TIMER, enter
TIMING START SQL_TIMER
To list the current timer's title and accumulated time, enter
TIMING SHOW
To list the current timer's title and accumulated time and to remove the timer, enter
TIMING STOP
TTITLE
Syntax
TTI[TLE] [printspec [text | variable] ...] [ON | OFF]
where printspec represents one or more of the following clauses used to place and format the text:
BOLD
CE[NTER]
COL n
FORMAT text
LE[FT]
R[IGHT]
S[KIP] [n]
TAB n
Places and formats a specified title at the top of each report page. Enter TTITLE with no clauses to list its current definition. The old form of TTITLE is used if only a single word or string in quotes follows the TTITLE command.
See TTI[TLE] text (obsolete old form) for a description of the old form of TTITLE.
Terms
These terms and clauses also apply to the BTITLE command.
text
The title text. Enter text in single quotes if you want to place more than one word on a single line.
variable
A substitution variable or any of the following system-maintained values, SQL.LNO (the current line number), SQL.PNO (the current page number), SQL.RELEASE (the current Oracle Database release number), SQL.SQLCODE (the current error code), or SQL.USER (the current username).
To print one of these values, reference the appropriate variable in the title. You can format variable with the FORMAT clause.
SQL*Plus substitution variables (& variables) are expanded before TTITLE is executed. The resulting string is stored as the TTITLE text. During subsequent execution for each page of results, the expanded value of a variable may itself be interpreted as a substitution variable with unexpected results.
You can avoid this double substitution in a TTITLE command by not using the & prefix for variables that are to be substituted on each page of results. If you want to use a substitution variable to insert unchanging text in a TTITLE, enclose it in quotes so that it is only substituted once.
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). Here "column" 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] | 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 bold 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 the COLUMN command 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 using 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.
Enter TTITLE with no clauses to list the current TTITLE definition.
Usage
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 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 them in the order you specify them, positioning and formatting each constant or variable as specified by the printspecclauses 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:
TTITLE CENTER 'Summary Report for' -
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
TTITLE LEFT 'Monthly Analysis' CENTER '01 Jan 2003' -
RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP CENTER -
'Data in Thousands'
Monthly Analysis 01 Jan 2003 Page: 1
Data in Thousands
To suppress the top title display without changing its definition, enter
TTITLE OFF
UNDEFINE
Syntax
UNDEF[INE] variable ...
where variable represents the name of the substitution variable you want to delete.
Deletes one or more substitution variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command).
Examples
To undefine a substitution variable named POS, enter
UNDEFINE POS
To undefine two substitution variables named MYVAR1 and MYVAR2, enter
UNDEFINE MYVAR1 MYVAR2
VARIABLE
Syntax
VAR[IABLE] [variable [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.
See "Using Bind Variables" for more information on bind variables. See your 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 Oracle9i 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 Oracle9i 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_DOUBLE
Creates a variable of type BINARY_DOUBLE.
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}" 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 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;
/
PL/SQL procedure successfully completed.
To display the value of the bind variable in SQL*Plus, enter:
PRINT ret_val
RET_VAL
4
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;
/
PL/SQL procedure successfully completed.
LAST_NAME CITY DEPARTMENT_ID
------------------------- ------------------------------ -------------
Hartstein Toronto 20
Russell Oxford 80
Partners Oxford 80
King Seattle 90
Kochhar Seattle 90
De Haan Seattle 90
6 rows selected.
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
variable id
datatype NUMBER
variable txt
datatype CHAR(20)
variable myvar
datatype REFCURSOR
The following example lists a single variable:
VARIABLE txt
variable txt
datatype CHAR(20)
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;
/
PL/SQL procedure successfully completed.
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
------------------------------ ------------------------- ------------
Executive De Haan $17,000.00
King $24,000.00
Kochhar $17,000.00
Subtotal: $58,000.00
Marketing Hartstein $13,000.00
Subtotal: $13,000.00
Sales Partners $13,500.00
Russell $14,000.00
Subtotal: $27,500.00
Total: $98,500.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
VARIABLE T CLOB
BEGIN
SELECT CLOB_COL INTO :T FROM CLOB_TAB;
END;
/
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED
To print 200 characters from the column clob_col, enter
SET LINESIZE 70
SET LONG 200
PRINT T
T
Remember to run the Departmental Salary Bill report each month This r
eport contains confidential information.
To set the printing position to the 21st character, enter
SET LOBOFFSET 21
PRINT T
T
Departmental Salary Bill report each month This report contains confi
dential information.
For more information on creating CLOB columns, see your Oracle Database SQL Reference
WHENEVER OSERROR
Syntax
WHENEVER OSERROR {EXIT [SUCCESS | FAILURE | n | variable | :BindVariable]
[COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}
Performs the specified action (exits SQL*Plus by default) if an operating system error occurs (such as a file writing error).
In iSQL*Plus, performs the specified action (stops the current script by default) and returns focus to the Workspace if an operating system error occurs.
Terms
[SUCCESS | FAILURE | n | variable | :BindVariable]
Directs SQL*Plus to perform the specified action 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.
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 for more information.
CONTINUE
Turns off the EXIT option.
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
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.
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 script cause iSQL*Plus to stop processing the current script and return focus to the Input area on the Workspace:
cause SQL*Plus to exit and COMMIT any pending changes if a failure occurs when reading from the output file:
WHENEVER OSERROR EXIT
START no_such_file
OS Message: No such file or directory
Disconnected from Oracle......
WHENEVER SQLERROR
Syntax
WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}
Performs the specified action (exits SQL*Plus by default) if a SQL command or PL/SQL block generates an error.
In iSQL*Plus, performs the specified action (stops the current script by default) and returns focus to the Workspace if a SQL command or PL/SQL block generates an error.
Terms
[SUCCESS | FAILURE | WARNING | n | variable | :BindVariable]
Directs SQL*Plus to perform the specified action 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.
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 for more information.
CONTINUE
Turns off the EXIT option.
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
The WHENEVER SQLERROR command is triggered by SQL command or PL/SQL block errors, and not by SQL*Plus command errors.
Examples
The commands in the following script cause iSQL*Plus to stop processing the current script and return focus to the Input area on the Workspace:
The commands in the following script cause SQL*Plus to exit and return the SQL error code if the SQL UPDATE command fails:
WHENEVER SQLERROR EXIT SQL.SQLCODE
UPDATE EMP_DETAILS_VIEW SET SALARY = SALARY*1.1;
The following SQL command error causes iSQL*Plus to stop processing the current script and return focus to the Input area on the Workspace:
WHENEVER SQLERROR EXIT SQL.SQLCODE
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 examples show that the WHENEVER SQLERROR command is not executed after errors with SQL*Plus commands, but it is executed if SQL commands or PL/SQL blocks cause errors:
WHENEVER SQLERROR EXIT SQL.SQLCODE
column LAST_name headIing "Employee Name"
Unknown COLUMN option "headiing"
SHOW non_existed_option
The following PL/SQL block error causes SQL*Plus to exit and return the SQL error code:
WHENEVER SQLERROR EXIT SQL.SQLCODE
begin
SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
END;
/
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.....
P2 NUMBER IN
To create and describe the object type ADDRESS that contains the attributes STREET and CITY, enter
CREATE TYPE ADDRESS AS OBJECT
( STREET VARCHAR2(20),
CITY VARCHAR2(20)
);
/
Type created.
DESCRIBE address
Name Null? Type
----------------------------------------- -------- ----------------------
STREET VARCHAR2(20)
CITY VARCHAR2(20)
To create and describe the object type EMPLOYEE that contains the attributes LAST_NAME, EMPADDR, JOB_ID and SALARY, enter
CREATE TYPE EMPLOYEE AS OBJECT
(LAST_NAME VARCHAR2(30),
EMPADDR ADDRESS,
JOB_ID VARCHAR2(20),
SALARY NUMBER(7,2)
);
/
Type created.
DESCRIBE employee
Name Null? Type
----------------------------------------- -------- ----------------------
LAST_NAME VARCHAR2(30)
EMPADDR ADDRESS
JOB_ID VARCHAR2(20)
SALARY NUMBER(7,2)
To create and describe the object type addr_type as a table of the object type ADDRESS, enter
CREATE TYPE addr_type IS TABLE OF ADDRESS;/
Type created.
DESCRIBE addr_type
addr_type TABLE OF ADDRESS
Name Null? Type
----------------------------------------- -------- ----------------------
STREET VARCHAR2(20)
CITY VARCHAR2(20)
To create and describe the object type addr_varray as a varray of the object type ADDRESS, enter
CREATE TYPE addr_varray AS VARRAY(10) OF ADDRESS;/
Type created.
DESCRIBE addr_varray
addr_varray VARRAY(10) OF ADDRESS
Name Null? Type
----------------------------------------- -------- ----------------------
STREET VARCHAR2(20)
CITY VARCHAR2(20)
To create and describe the table department that contains the columns DEPARTMENT_ID, PERSON and LOC, enter
CREATE TABLE department
(DEPARTMENT_ID NUMBER,
PERSON EMPLOYEE,
LOC NUMBER
);
/
Table created.
DESCRIBE department
Name Null? Type
----------------------------------------- -------- ----------------------
DEPARTMENT_ID NUMBER
PERSON EMPLOYEE
LOC NUMBER
To create and describe the object type rational that contains the attributes NUMERATOR and DENOMINATOR, and the METHOD rational_order, enter
CREATE OR REPLACE TYPE rational AS OBJECT
(NUMERATOR NUMBER,
DENOMINATOR NUMBER,
MAP MEMBER FUNCTION rational_order -
RETURN DOUBLE PRECISION,
PRAGMA RESTRICT_REFERENCES
(rational_order, RNDS, WNDS, RNPS, WNPS) );
/
CREATE OR REPLACE TYPE BODY rational AS OBJECT
MAP MEMBER FUNCTION rational_order -
RETURN DOUBLE PRECISION IS
BEGIN
RETURN NUMERATOR/DENOMINATOR;
END;
END;
/
DESCRIBE rational
Name Null? Type
------------------------------ -------- ------------
NUMERATOR NUMBER
DENOMINATOR NUMBER
METHOD
MAP MEMBER FUNCTION RATIONAL_ORDER RETURNS NUMBER
To create a table which contains a column of XMLType, and describe it, enter
CREATE TABLE PROPERTY (Price NUMBER, Description SYS.XMLTYPE);
Table created
DESCRIBE PROPERTY;
Name Null? Type
----------------------------------------- -------- ----------------------
PRICE NUMBER
DESCRIPTION SYS.XMLTYPE
To format the DESCRIBE output use the SET command as follows:
SET LINESIZE 80
SET DESCRIBE DEPTH 2
SET DESCRIBE INDENT ON
SET DESCRIBE LINE OFF
To display the settings for the object, use the SHOW command as follows:
SHOW DESCRIBE
DESCRIBE DEPTH 2 LINENUM OFF INDENT ON
DESCRIBE employee
Name Null? Type
----------------------------------------- -------- ----------------------
FIRST_NAME VARCHAR2(30)
EMPADDR ADDRESS
STREET VARCHAR2(20)
CITY VARCHAR2(20)
JOB_ID VARCHAR2(20)
SALARY NUMBER(7,2)
For more information on using the CREATE TYPE command, see your Oracle Database SQL Reference.
For information about using the SET DESCRIBE and SHOW DESCRIBE commands, see the SET and SHOW commands.
DISCONNECT
Syntax
DISC[ONNECT]
Commits pending changes to the database and logs the current username out of Oracle Database, but does not exit SQL*Plus.
Usage
Use DISCONNECT within a script to prevent user access to the database when you want to log the user out of Oracle Database but have the user remain in SQL*Plus. In SQL*Plus command-line, use EXIT or QUIT to log out of Oracle Database and return control to your computer's operating system. In iSQL*Plus, click the Logout button to log out of Oracle Database.
Examples
Your script might begin with a CONNECT command and end with a DISCONNECT, as shown later.
CONNECT HR/your_password
SELECT LAST_NAME, DEPARTMENT_NAME FROM EMP_DETAILS_VIEW;
DISCONNECT
SET INSTANCE FIN2
CONNECT HR2/your_password
EDIT
EDIT is not available in iSQL*Plus.
Syntax
ED[IT] [file_name[.ext]]
where file_name[.ext] represents the file you wish to edit (typically a script).
Invokes an operating system text editor on the contents of the specified file or on the contents of the buffer. The buffer has no command history list and does not record SQL*Plus commands.
Enter EDIT with no filename to edit the contents of the SQL buffer with the operating system text editor.
Usage
If you omit the file extension, SQL*Plus assumes the default command-file extension (normally SQL). For information on changing the default extension, see the SUFFIX variable of the SET command in this chapter.
If you specify a filename, SQL*Plus searches for the file in the directory set by ORACLE_PATH. If SQL*Plus cannot find the file in ORACLE_PATH, or if ORACLE_PATH is not set, it searches for the file in the current working directory. If SQL*Plus cannot find the file in either directory, it creates a file with the specified name.
The substitution variable, _EDITOR, contains the name of the text editor invoked by EDIT. You can change the text editor by changing the value of _EDITOR. For information about changing the value of a substitution variable, see DEFINE. EDIT attempts to run the default operating system editor if _EDITOR is undefined.
EDIT places the contents of the SQL buffer in a file named AFIEDT.BUF by default (in your current working directory) and runs the text editor on the contents of the file. If the file AFIEDT.BUF already exists, it is overwritten with the contents of the buffer. You can change the default filename by using the SET EDITFILE command. For more information about setting a default filename for the EDIT command, see the EDITFILE variable of the SET command in this chapter.
Note:
The default file, AFIEDT.BUF, may have a different name on some operating systems.
If you do not specify a filename and the buffer is empty, EDIT returns an error message.
To leave the editing session and return to SQL*Plus, terminate the editing session in the way customary for the text editor. When you leave the editor, SQL*Plus loads the contents of the file into the buffer.
Note:
In Windows, if you use WordPad as your editor (_EDITOR=write.exe), the buffer is not reloaded when you exit WordPad. In this case, use GET to reload the buffer.
Examples
To edit the file REPORT with the extension SQL using your operating system text editor, enter
EDIT REPORT
EXECUTE
Syntax
EXEC[UTE] statement
where statement represents a PL/SQL statement.
Executes a single PL/SQL statement. The EXECUTE command is often useful when you want to execute a PL/SQL statement that references a stored procedure. For more information on PL/SQL, see yourPL/SQL User's Guide and Reference.
Usage
If your EXECUTE command cannot fit on one line because of the PL/SQL statement, use the SQL*Plus continuation character (a hyphen).
The length of the command and the PL/SQL statement cannot exceed the length defined by SET LINESIZE.
You can suppress printing of the message "PL/SQL procedure successfully completed" with SET FEEDBACK OFF.
Examples
If the variable :n has been defined with:
VARIABLE n NUMBER
The following EXECUTE command assigns a value to the bind variable n:
EXECUTE :n := 1
PL/SQL procedure successfully completed.
For information on how to create a bind variable, see the VARIABLE command.
EXIT
Syntax
{EXIT | QUIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]
Commits or rolls back all pending changes, logs out of Oracle Database, terminates SQL*Plus and returns control to the operating system.
In iSQL*Plus, commits or rolls back all pending changes, stops processing the current iSQL*Plus script and returns focus to the Input area. There is no way to access the return code in iSQL*Plus. IniSQL*Plus click the Logout button to exit the Oracle Database.
Commit on exit, or commit on termination of processing in iSQL*Plus, is performed regardless of the status of SET AUTOCOMMIT.
Terms
{EXIT | QUIT}
Can be used interchangeably (QUIT is a synonym for EXIT).
SUCCESS
Exits normally.
FAILURE
Exits with a return code indicating failure.
WARNING
Exits with a return code indicating warning.
COMMIT
Saves pending changes to the database before exiting.
n
Represents an integer you specify as the return code.
variable
Represents a user-defined or system variable (but not a bind variable), such as SQL.SQLCODE. EXIT variable exits with the value of variable as the return code.
:BindVariable
Represents a variable created in SQL*Plus with the VARIABLE command, and then referenced in PL/SQL, or other subprograms. :BindVariable exits the subprogram and returns you to SQL*Plus.
ROLLBACK
Executes a ROLLBACK statement and abandons pending changes to the database before exiting.
EXIT with no clauses commits and exits with a value of SUCCESS.
Usage
EXIT enables you to specify an operating system return code. This enables you to run SQL*Plus scripts in batch mode and to detect programmatically the occurrence of an unexpected event. The manner of detection is operating-system specific.
The key words SUCCESS, WARNING, and FAILURE represent operating-system dependent values. On some systems, WARNING and FAILURE may be indistinguishable.
The range of operating system return codes is also restricted on some operating systems. This limits the portability of EXIT n and EXIT variable between platforms. For example, on UNIX there is only one byte of storage for return codes; therefore, the range for return codes is limited to zero to 255.
If you make a syntax error in the EXIT options or use a non-numeric variable, SQL*Plus performs an EXIT FAILURE COMMIT.
For information on exiting conditionally, see the WHENEVER SQLERROR and WHENEVER OSERROR commands.
Examples
The following example commits all uncommitted transactions and returns the error code of the last executed SQL command or PL/SQL block:
EXIT SQL.SQLCODE
GET
GET is not available in iSQL*Plus. In iSQL*Plus use Load Script.
Syntax
GET [FILE] file_name[.ext] [LIS[T] | NOL[IST]]
Loads an operating system file into the SQL buffer.
In iSQL*Plus click the Load Script button to load a script into the Input area.
The buffer has no command history list and does not record SQL*Plus commands.
Terms
FILE
Keyword to specify that the following argument is the name of the script you want to load. This optional keyword is usually omitted.
If you want to load a script with the name file, because it is a command keyword, you need to put the name file in single quotes.
file_name[.ext]
Represents the file you wish to load (typically a script).
LIS[T]
Lists the contents of the file after it is loaded. This is the default.
NOL[IST]
Suppresses the listing.
Examples
To load a file called YEARENDRPT with the extension SQL into the buffer, enter
GET YEARENDRPT
HELP
Syntax
HELP | ? [topic]
where topic represents a SQL*Plus help topic, for example, COLUMN.
Accesses the SQL*Plus command-line help system. Enter HELP INDEX or ? INDEX for a list of topics. You can view SQL*Plus resources at http://otn.oracle.com/tech/sql_plus/ and the Oracle Database Library at http://otn.oracle.com/documentation/.
In iSQL*Plus, click the Help icon to access the iSQL*Plus Online Help.
Enter HELP or ? without topic to get help on the help system.
Usage
You can only enter one topic after HELP. You can abbreviate the topic (for example, COL for COLUMN). However, if you enter only an abbreviated topic and the abbreviation is ambiguous, SQL*Plus displays help for all topics that match the abbreviation. For example, if you enter
HELP EX
SQL*Plus displays the syntax for the EXECUTE command followed by the syntax for the EXIT command.
If you get a response indicating that help is not available, consult your database administrator.
Examples
To see a list of SQL*Plus commands for which help is available, enter
HELP INDEX
or
? INDEX
To see a single column list of SQL*Plus commands for which help is available, enter
HELP TOPICS
HOST
HOST is not available in iSQL*Plus.
Syntax
HO[ST] [command]
where command represents an operating system command.
Executes an operating system command without leaving SQL*Plus.
Enter HOST without command to display an operating system prompt. You can then enter multiple operating system commands. For information on returning to SQL*Plus, refer to the platform-specific Oracle documentation provided for your operating system.
Note:
Operating system commands entered from a SQL*Plus session using the HOST command do not affect the current SQL*Plus session. For example, setting an operating system environment variable only affects SQL*Plus sessions started subsequently.
You can disable HOST. For more information about disabling HOST, see " SQL*Plus Security".
Usage
In some operating systems, you can use a character in place of HOST such as "$" in Windows or "!" in UNIX, or you may not have access to the HOST command. See the platform-specific Oracle documentation provided for your operating system or ask your DBA for more information.
On some platforms, an _RC substitution variable may be created with a HOST return value that is operation system dependent. It is recommended that you do not use the _RC substitution variable in scripts as it is not portable.
SQL*Plus removes the SQLTERMINATOR (a semicolon by default) before the HOST command is issued. A workaround for this is to add another SQLTERMINATOR. See SET SQLT[ERMINATOR] {; | c | ON | OFF} for more information.
Examples
To execute a UNIX operating system command, ls *.sql, enter
HOST ls *.sql
To execute a Windows operating system command, dir *.sql, enter
HOST dir *.sql
INPUT
INPUT is not available in iSQL*Plus.
Syntax
I[NPUT] [text]
where text represents the text you wish to add.
Adds one or more new lines of text after the current line in the buffer. The buffer has no command history list and does not record SQL*Plus commands.
To add a single line, enter the text of the line after the command INPUT, separating the text from the command with a space. To begin the line with one or more spaces, enter two or more spaces between INPUT and the first non-blank character of text.
To add several lines, enter INPUT with no text. INPUT prompts you for each line. To leave INPUT, enter a null (empty) line or a period.
Usage
If you enter a line number at the command prompt larger than the number of lines in the buffer, and follow the number with text, SQL*Plus adds the text in a new line at the end of the buffer. If you specify zero (0) for the line number and follow the zero with text, then SQL*Plus inserts the line at the beginning of the buffer (that line becomes line 1).
Examples
Assume the SQL buffer contains the following command:
SELECT LAST_NAME, DEPARTMENT_ID, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
To add an ORDER BY clause to the query, enter
LIST 2
2* FROM EMP_DETAILS_VIEW
INPUT ORDER BY LAST_NAME
LIST 2 ensures that line 2 is the current line. INPUT adds a new line containing the ORDER BY clause after the current line. The SQL buffer now contains the following lines:
1 SELECT LAST_NAME, DEPARTMENT_ID, SALARY, COMMISSION_PCT
2 FROM EMP_DETAILS_VIEW
3* ORDER BY LAST_NAME
To add a two-line WHERE clause, enter
LIST 2
2* FROM EMP_DETAILS_VIEW
INPUT
3 WHERE JOB_ID = 'SA_MAN'
4 AND COMMISSION_PCT=.25
5
INPUT prompts you for new lines until you enter an empty line or a period. The SQL buffer now contains the following lines:
SELECT LAST_NAME, DEPARTMENT_ID, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'SA_MAN'
AND COMMISSION_PCT = .25
ORDER BY LAST_NAME
LIST
Syntax
L[IST] [n | n m | n * | n LAST | * | * n | * LAST | LAST]
Lists one or more lines of the SQL buffer.
The buffer has no command history list and does not record SQL*Plus commands. In SQL*Plus command-line you can also use ";" to list all the lines in the SQL buffer.
Terms
Term Description
n Lists line n.
n m Lists lines n through m.
n * Lists line n through the current line.
n LAST Lists line n through the last line.
*
Lists the current line.
- n Lists the current line through line n.
- LAST Lists the current line through the last line.
LAST Lists the last line.
Enter LIST with no clauses, or ";" to list all lines. The last line listed becomes the new current line (marked by an asterisk).
Examples
To list the contents of the buffer, enter
LIST
or enter
;
1 SELECT LAST_NAME, DEPARTMENT_ID, JOB_ID
2 FROM EMP_DETAILS_VIEW
3 WHERE JOB_ID = 'SH_CLERK'
4* ORDER BY DEPARTMENT_ID
The asterisk indicates that line 4 is the current line.
To list the second line only, enter
LIST 2
The second line is displayed:
2* FROM EMP_DETAILS_VIEW
To list the current line (now line 2) to the last line, enter
LIST * LAST
You will then see this:
2 FROM EMP_DETAILS_VIEW
3 WHERE JOB_ID = 'SH_CLERK'
4* ORDER BY DEPARTMENT_ID
PASSWORD
Syntax
PASSW[ORD] [username]
where username specifies the user. If omitted, username defaults to the current user.
Enables you to change a password without echoing it on an input device. In iSQL*Plus, use the Password screen to change your password.
Usage
To change the password of another user, you must have been granted the appropriate privilege. See CONNECT for more information about changing your password.
Examples
If you want to change your current password, enter
PASSWORD
Changing password for your_password
Old password: your_password
New password: new_password
Retype new password: new_password
Password changed
If you are logged on as a DBA, and want to change the password for user johnw (currently identified by johnwpass) to johnwnewpass
PASSWORD johnw
Changing password for johnw
New password: johnwnewpass
Retype new password: johnwnewpass
Password changed
Passwords are not echoed to the screen, they are shown here for your convenience.
PAUSE
Syntax
PAU[SE] [text]
where text represents the text you wish to display.
Displays the specified text then waits for the user to press RETURN.
In iSQL*Plus, displays the Next Page button which the user must click to continue.
Enter PAUSE followed by no text to display two empty lines.
Usage
Because PAUSE always waits for the user's response, it is best to use a message that tells the user explicitly to press [Return].
PAUSE reads input from the terminal (if a terminal is available) even when you have designated the source of the command input as a file.
See SET PAU[SE] {ON | OFF | text} for information on pausing between pages of a report.
Examples
To print "Adjust paper and press RETURN to continue." and to have SQL*Plus wait for the user to press [Return], you might include the following PAUSE command in a script:
SET PAUSE OFF
PAUSE Adjust paper and press RETURN to continue.
SELECT ...
Syntax
PRI[NT] [variable ...]
where variable ... represents names of bind variables whose values you want to display.
Displays the current values of bind variables. For more information on bind variables, see your PL/SQL User's Guide and Reference.
Enter PRINT with no variables to print all bind variables.
Usage
Bind variables are created using the VARIABLE command. See VARIABLE for more information and examples.
You can control the formatting of the PRINT output just as you would query output. For more information, see the formatting techniques described in " Formatting SQL*Plus Reports".
To automatically display bind variables referenced in a successful PL/SQL block or used in an EXECUTE command, use the AUTOPRINT clause of the SET command. See SET for more information.
Examples
The following example illustrates a PRINT command:
VARIABLE n NUMBER
BEGIN
:n := 1;
END;
/
PL/SQL procedure successfully completed.
PRINT n
N
1
PROMPT
Syntax
PRO[MPT] [text]
where text represents the text of the message you want to display.
Sends the specified message or a blank line to the user's screen. If you omit text, PROMPT displays a blank line on the user's screen.
Usage
You can use this command in scripts to give information to the user.
Examples
The following example shows the use of PROMPT in conjunction with ACCEPT in a script called ASKFORDEPT.SQL. ASKFORDEPT.SQL contains the following SQL*Plus and SQL commands:
PROMPTPROMPT Please enter a valid departmentPROMPT For example: 10SELECT DEPARTMENT_NAME FROM EMP_DETAILS_VIEWWHERE DEPARTMENT_ID = &NEWDEPT
Assume you run the file using START or @:
@ASKFORDEPT.SQL VAL1
@HTTP:machine_name.domain:port/ASKFORDEPT.SQL VAL1
Please enter a valid department
For example: 10
Department ID?>
You can enter a department number at the prompt Department ID?>. By default, SQL*Plus lists the line containing &NEWDEPT before and after substitution, and then displays the department name corresponding to the number entered at the Department ID?> prompt. You can use SET VERIFY OFF to prevent this behavior.
RECOVER
Syntax
RECOVER {general | managed | BEGIN BACKUP | END BACKUP}
where the general clause has the following syntax:
[AUTOMATIC] [FROM location]
{ {full_database_recovery | partial_database_recovery | LOGFILE filename}
[ {TEST | ALLOW integer CORRUPTION | parallel_clause } [TEST | ALLOW integer CORRUPTION | parallel_clause ]...]
| CONTINUE [DEFAULT] | CANCEL}
where the full_database_recovery clause has the following syntax:
[STANDBY] DATABASE
[ {UNTIL {CANCEL | TIME date | CHANGE integer} | USING BACKUP CONTROLFILE}
[UNTIL {CANCEL | TIME date | CHANGE integer} | USING BACKUP CONTROLFILE]...]
where the partial_database_recovery clause has the following syntax:
{TABLESPACE tablespace [, tablespace]...
| DATAFILE {filename | filenumber} [, filename | filenumber]...
| STANDBY {TABLESPACE tablespace [, tablespace]...
| DATAFILE {filename | filenumber} [, filename | filenumber]...}
UNTIL [CONSISTENT WITH] CONTROLFILE }
where the parallel clause has the following syntax:
{ NOPARALLEL | PARALLEL [ integer ] }
where the managed clause has the following syntax:
MANAGED STANDBY DATABASE recover_clause | cancel_clause | finish_clause
where the recover_clause has the following syntax:
{ { DISCONNECT [ FROM SESSION ] | { TIMEOUT integer | NOTIMEOUT } }
| { NODELAY | DEFAULT DELAY | DELAY integer } | NEXT integer
| { EXPIRE integer | NO EXPIRE } | parallel_clause
| USING CURRENT LOGFILE | UNTIL CHANGE integer
| THROUGH { [ THREAD integer ] SEQUENCE integer
| ALL ARCHIVELOG | { ALL | LAST | NEXT } SWITCHOVER} }
[ DISCONNECT [ FROM SESSION ] | { TIMEOUT integer | NOTIMEOUT }
| { NODELAY | DEFAULT DELAY | DELAY integer } | NEXT integer
| { EXPIRE integer | NO EXPIRE } | parallel_clause
| USING CURRENT LOGFILE | UNTIL CHANGE integer
| THROUGH { [ THREAD integer ] SEQUENCE integer
| ALL ARCHIVELOG | { ALL | LAST | NEXT } SWITCHOVER} ] ...
where the cancel_clause has the following syntax:
CANCEL [IMMEDIATE] [WAIT | NOWAIT]
where the finish_clause has the following syntax:
[ DISCONNECT [ FROM SESSION ] ] [ parallel_clause ]
FINISH [ SKIP [ STANDBY LOGFILE ] ] [ WAIT | NOWAIT ]
where the parallel_clause has the following syntax:
{ NOPARALLEL | PARALLEL [ integer ] }
Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database. For more information on the RECOVER command, see the Oracle Database Administrator's Guide, the ALTER DATABASE RECOVER command in the Oracle Database SQL Reference, and the Oracle Database Backup and Recovery Basics guide.
Because of possible network timeouts, it is recommended that you use SQL*Plus command-line, not iSQL*Plus, for long running DBA operations such as RECOVER.
Terms
AUTOMATIC
Automatically generates the name of the next archived redo log file needed to continue the recovery operation. Oracle Database uses the LOG_ARCHIVE_DEST (or LOG_ARCHIVE_DEST_ 1) and LOG_ARCHIVE_FORMAT parameters (or their defaults) to generate the target redo log filename. If the file is found, the redo contained in that file is applied. If the file is not found, SQL*Plus prompts you for a filename, displaying a generated filename as a suggestion.
If you do not specify either AUTOMATIC or LOGFILE, SQL*Plus prompts you for a filename, suggesting the generated filename. You can either accept the generated filename or replace it with a fully qualified filename. You can save time by using the LOGFILE clause to specify the filename if you know the archived filename differs from the filename Oracle Database would generate.
FROM location
Specifies the location from which the archived redo log file group is read. The value of location must be a fully specified file location. If you omit this parameter, SQL*Plus assumes the archived redo log file group is in the location specified by the initialization parameter LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_1. Do not specify FROM if you have set a file with SET LOGSOURCE.
full_database_recovery
Enables you to specify the recovery of a full database.
partial_database_recovery
Enables you to specify the recovery of individual tablespaces and datafiles.
LOGFILE
Continues media recovery by applying the specified redo log file. In interactive recovery mode (AUTORECOVERY OFF), if a bad log name is entered, errors for the bad log name are displayed and you are prompted to enter a new log name.
TEST
Specifies a trial recovery to detect possible problems. Redo is applied normally, but no changes are written to disk, and changes are rolled back at the end of the trial recovery. You can only use the TEST clause for a trial recovery if you have restored a backup. In the event of logfile corruption, specifies the number of corrupt blocks that can be tolerated while allowing recovery to proceed. During normal recovery, integer cannot exceed 1.
ALLOW integer CORRUPTION
In the event of logfile corruption, specifies the number of corrupt blocks that can be tolerated while allowing recovery to proceed. During normal recovery, integer cannot exceed 1.
parallel _clause
Enables you to specify the degree of parallel processing to use during the recovery operation.
CONTINUE
Continues multi-instance recovery after it has been interrupted to disable a thread.
CONTINUE DEFAULT
Continues recovery using the redo log file generated automatically by Oracle Database if no other logfile is specified. This is equivalent to specifying AUTOMATIC, except that Oracle Database does not prompt for a filename.
CANCEL
Terminates cancel-based recovery.
STANDBY DATABASE
Recovers the standby database using the control file and archived redo log files copied from the primary database. The standby database must be mounted but not open.
DATABASE
Recovers the entire database.
UNTIL CANCEL
Specifies an incomplete, cancel-based recovery. Recovery proceeds by prompting you with suggested filenames of archived redo log files, and recovery completes when you specify CANCEL instead of a filename.
UNTIL TIME
Specifies an incomplete, time-based recovery. Use single quotes, and the following format:
'YYYY-MM-DD:HH24:MI:SS'
UNTIL CHANGE
Specifies an incomplete, change-based recovery. integer is the number of the System Change Number (SCN) following the last change you wish to recover. For example, if you want to restore your database up to the transaction with an SCN of 9, you would specify UNTIL CHANGE 10.
USING BACKUP CONTROLFILE
Specifies that a backup of the control file be used instead of the current control file.
TABLESPACE
Recovers a particular tablespace. tablespace is the name of a tablespace in the current database. You may recover up to 16 tablespaces in one statement.
DATAFILE
Recovers a particular datafile. You can specify any number of datafiles.
STANDBY TABLESPACE
Reconstructs a lost or damaged tablespace in the standby database using archived redo log files copied from the primary database and a control file.
STANDBY DATAFILE
Reconstructs a lost or damaged datafile in the standby database using archived redo log files copied from the primary database and a control file.
UNTIL CONSISTENT WITH CONTROLFILE
Specifies that the recovery of an old standby datafile or tablespace uses the current standby database control file.
PARALLEL [integer]
SQL*Plus selects a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
The PARALLEL keyword overrides the RECOVERY_PARALLELISM initialization parameter. For more information about the PARALLEL keyword see the Oracle Real Application Clusters Quick Start guide.
Use integer to specify the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution processes.
NOPARALLEL
Specifies serial recovery processing. This is the default.
MANAGED STANDBY DATABASE
Specifies sustained standby recovery mode. This mode assumes that the standby database is an active component of an overall standby database architecture. A primary database actively archives its redo log files to the standby site. As these archived redo logs arrive at the standby site, they become available for use by a managed standby recovery operation. Sustained standby recovery is restricted to media recovery.
For more information on the parameters of this clause, see the Oracle Database Backup and Recovery Advanced User's Guide.
DISCONNECT
Indicates that the managed redo process (MRP) should apply archived redo files as a detached background process. Doing so leaves the current session available.
TIMEOUT
Specifies in minutes the wait period of the sustained recovery operation. The recovery process waits for integer minutes for a requested archived log redo to be available for writing to the standby database. If the redo log file does not become available within that time, the recovery process terminates with an error message. You can then issue the statement again to return to sustained standby recovery mode.
If you do not specify this clause, or if you specify NOTIMEOUT, the database remains in sustained standby recovery mode until you reissue the statement with the RECOVER CANCEL clause or until instance shutdown or failure.
NODELAY
Applies a delayed archivelog immediately to the standby database overriding any DELAY setting in the LOG_ARCHIVE_DEST_n parameter on the primary database. If you omit this clause, application of the archivelog is delayed according to the parameter setting. If DELAY was not specified in the parameter, the archivelog is applied immediately.
DEFAULT DELAY
Waits the default number of minutes specified in the LOG_ARCHIVE_DEST_n initialization parameter before applying the archived redo logs.
DELAY integer
Waits integer minutes before applying the archived redo logs.
NEXT integer
Applies the specified number of archived redo logs as soon as possible after they have been archived. It temporarily overrides any DELAY setting in the LOG_ARCHIVE_DEST_n parameter on the primary database, and any delay values set in an earlier SQL*Plus RECOVER command or an ALTER DATABASE RECOVER command.
EXPIRE integer
Specifies the number of minutes from the current time after which managed recovery terminates automatically.
NO EXPIRE
Disables a previously specified EXPIRE integer option.
USING CURRENT LOGFILE
Recovers redo from standby online logs as they are being filled, without requiring them to be archived in the standby database first.
UNTIL CHANGE integer
Processes managed recovery up to but not including the specified system change number (SCN).
THROUGH THREAD integer SEQUENCE integer
Terminates managed recovery based on archivelog thread number and sequence number. Managed recovery terminates when the corresponding archivelog has been applied. If omitted, THREAD defaults to 1.
THROUGH ALL ARCHIVELOG
Continues managed standby until all archivelogs have been recovered. You can use this statement to override a THROUGH THREAD integer SEQUENCE integer clause issued in an earlier statement. If the THROUGH clause is omitted, this is the default.
THROUGH ALL SWITCHOVER
Keeps managed standby recovery running through all switchover operations.
THROUGH LAST SWITCHOVER
Terminates managed standby recovery after the final end-of-redo archival indicator.
THROUGH NEXT SWITCHOVER
Terminates managed standby recovery after recovering the next end-of-redo archival indicator.
CANCEL (managed clause)
Terminates managed standby recovery after applying the current archived redo file. Session control returns when the recovery process terminates.
CANCEL IMMEDIATE
Terminates managed standby recovery after applying the current archived redo file, or after the next redo log file read, whichever comes first. Session control returns when the recovery process terminates.
CANCEL IMMEDIATE WAIT
Terminates managed standby recovery after applying the current archived redo file or after the next redo log file read, whichever comes first. Session control returns when the managed standby recovery terminates.
CANCEL IMMEDIATE cannot be issued from the same session that issued the RECOVER MANAGED STANDBY DATABASE statement.
CANCEL IMMEDIATE NOWAIT
Terminates managed standby recovery after applying the current archived redo file, or after the next redo log file read, whichever comes first. Session control returns immediately.
CANCEL NOWAIT
Terminates managed standby recovery after the next redo log file read and returns session control immediately.
FINISH
Recovers the current standby online logfiles of the standby database. This clause may be useful if the primary database fails. It overrides any delays specified for archivelogs, so that logs are applied immediately.
FINISH cannot be issued if you have also specified TIMEOUT, DELAY, EXPIRE or NEXT clauses.
Usage
You must have the OSDBA role enabled. You cannot use the RECOVER command when connected through the multi-threaded server.
To perform media recovery on an entire database (all tablespaces), the database must be mounted and closed, and all tablespaces requiring recovery must be online.
To perform media recovery on a tablespace, the database must be mounted and open, and the tablespace must be offline.
To perform media recovery on a datafile, the database can remain open and mounted with the damaged datafiles offline (unless the file is part of the SYSTEM tablespace).
Before using the RECOVER command you must have restored copies of the damaged datafiles from a previous backup. Be sure you can access all archived and online redo log files dating back to when that backup was made.
When another log file is required during recovery, a prompt suggests the names of files that are needed. The name is derived from the values specified in the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT. You should restore copies of the archived redo log files needed for recovery to the destination specified in LOG_ARCHIVE_DEST, if necessary. You can override the initialization parameters by setting the LOGSOURCE variable with the SET LOGSOURCE command.
During recovery you can accept the suggested log name by pressing return, cancel recovery by entering CANCEL instead of a log name, or enter AUTO at the prompt for automatic file selection without further prompting.
If you have enabled autorecovery (that is, SET AUTORECOVERY ON), recovery proceeds without prompting you with filenames. Status messages are displayed when each log file is applied. When normal media recovery is done, a completion status is returned.
Examples
To recover the entire database, enter
RECOVER DATABASE
To recover the database until a specified time, enter
RECOVER DATABASE UNTIL TIME 01-JAN-2001:04:32:00
To recover the two tablespaces ts_one and ts_two from the database, enter
RECOVER TABLESPACE ts_one, ts_two
To recover the datafile data1.db from the database, enter
RECOVER DATAFILE 'data1.db'
REMARK
Syntax
REM[ARK]
Begins a comment in a script. SQL*Plus does not interpret the comment as a command.
Usage
The REMARK command must appear at the beginning of a line, and the comment ends at the end of the line. A line cannot contain both a comment and a command.
A "–" at the end of a REMARK line is treated as a line continuation character.
For details on entering comments in scripts using the SQL comment delimiters, /* ... */, or the ANSI/ISO comment delimiter, - -, see "Placing Comments in Scripts".
Examples
The following script contains some typical comments:
REM COMPUTE uses BREAK ON REPORT to break on end of table
BREAK ON REPORT
COMPUTE SUM OF "DEPARTMENT 10" "DEPARTMENT 20" -
"DEPARTMENT 30" "TOTAL BY JOB_ID" ON REPORT
REM Each column displays the sums of salaries by job for
REM one of the departments 10, 20, 30.
SELECT JOB_ID,
SUM(DECODE( DEPARTMENT_ID, 10, SALARY, 0)) "DEPARTMENT 10",
SUM(DECODE( DEPARTMENT_ID, 20, SALARY, 0)) "DEPARTMENT 20",
SUM(DECODE( DEPARTMENT_ID, 30, SALARY, 0)) "DEPARTMENT 30",
SUM(SALARY) "TOTAL BY JOB_ID"
FROM EMP_DETAILS_VIEW
GROUP BY JOB_ID;
REPFOOTER
Syntax
REPF[OOTER] [PAGE] [printspec [text | variable] ...] | [ON | OFF]
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
Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition.
Enter REPFOOTER with no clauses to list the current REPFOOTER definition.
Terms
See the REPHEADER command for additional information on terms and clauses in the REPFOOTER command syntax.
Usage
If you do not enter a printspec clause before the text or variables, REPFOOTER left justifies the text or variables.
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.
Note:
If SET EMBEDDED is ON, the report footer is suppressed.
Examples
To define "END EMPLOYEE LISTING REPORT" as a report footer on a separate page and to center it, enter:
REPFOOTER PAGE CENTER 'END EMPLOYEE LISTING REPORT'
TTITLE RIGHT 'Page: ' FORMAT 999 SQL.PNO
SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000;
LAST_NAME SALARY
------------------------- ----------
King 24000
Kochhar 17000
De Haan 17000
Russell 14000
Partners 13500
Hartstein 13000
sum 98500
Page: 2
END EMPLOYEE LISTING REPORT
6 rows selected.
To suppress the report footer without changing its definition, enter
REPFOOTER OFF
REPHEADER
Syntax
REPH[EADER] [PAGE] [printspec [text | variable] ...] | [ON | OFF]
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
Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition.
Enter REPHEADER with no clauses to list the current REPHEADER definition.
Terms
These terms and clauses also apply to the REPFOOTER command.
PAGE
Begins a new page after printing the specified report header or before printing the specified report footer.
text
The report header or footer text. Enter text in single quotes if you want to place more than one word on a single line. The default is NULL.
variable
A substitution variable or any of the following system-maintained values. SQL.LNO is the current line number, SQL.PNO is the current page number, SQL.CODE is the current error code, SQL.RELEASE is the current Oracle Database release number, and SQL.USER is the current username.
To print one of these values, reference the appropriate variable in the report header or footer. You can use the FORMAT clause to format variable.
OFF
Turns the report header or footer off (suppresses its display) without affecting its definition.
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] 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 bold text on three consecutive lines, instead of bold.
FORMAT text
Specifies a format model that determines the format of 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 COLUMNfor 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 using the default format.
Usage
If you do not enter a printspec clause before the text or variables, REPHEADER left justifies the text or variables.
You can use any number of constants and variables in a printspec. SQL*Plus displays the constants and variables in the order you specify, positioning and formatting each constant or variable as specified by the printspec clauses that precede it.
Examples
To define "EMPLOYEE LISTING REPORT" as a report header on a separate page, and to center it, enter:
REPHEADER PAGE CENTER 'EMPLOYEE LISTING REPORT'
TTITLE RIGHT 'Page: ' FORMAT 999 SQL.PNO
SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000;
Page: 1
EMPLOYEE LISTING REPORT
Page: 2
LAST_NAME SALARY
------------------------- ----------
King 24000
Kochhar 17000
De Haan 17000
Russell 14000
Partners 13500
Hartstein 13000
sum 98500
6 rows selected.
To suppress the report header without changing its definition, enter:
REPHEADER OFF
RUN
Syntax
R[UN]
Lists and executes the SQL command or PL/SQL block currently stored in the SQL buffer.
The buffer has no command history list and does not record SQL*Plus commands.
Usage
RUN causes the last line of the SQL buffer to become the current line.
The slash command (/) functions similarly to RUN, but does not list the command in the SQL buffer on your screen. The SQL buffer always contains the last SQL statement or PL/SQL block entered.
Examples
Assume the SQL buffer contains the following script:
SELECT DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000
To RUN the script, enter
RUN
1 SELECT DEPARTMENT_ID
2 FROM EMP_DETAILS_VIEW
3 WHERE SALARY>12000
DEPARTMENT_ID
90
90
90
80
80
20
6 rows selected.
SAVE
SAVE is not available in iSQL*Plus. In iSQL*Plus use Save Script.
Syntax
SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
Saves the contents of the SQL buffer in an operating system script. In iSQL*Plus, click the Save Script button to save the Input area contents to a script.
The buffer has no command history list and does not record SQL*Plus commands.
Terms
FILE
Keyword to specify that the following argument is the name you want to give to the saved script. This optional keyword is usually omitted.
If you want to save the script with the name file, because it is a command keyword, you need to put the name file in single quotes.
file_name[.ext]
Specifies the script in which you wish to save the buffer's contents.
CREATE
Creates a new file with the name specified. This is the default behavior.
REP[LACE]
Replaces the contents of an existing file. If the file does not exist, REPLACE creates the file.
APP[END]
Adds the contents of the buffer to the end of the file you specify.
Usage
If you do not specify an extension, SQL*Plus assumes the default command-file extension (normally SQL). See SET SUF[FIX] {SQL | text} for information on changing this default extension.
If you wish to SAVE a file under a name identical to a SAVE command clause (CREATE, REPLACE, or APPEND), you must specify a file extension.
When you SAVE the contents of the SQL buffer, SAVE adds a line containing a slash (/) to the end of the file.
Examples
To save the contents of the buffer in a file named DEPTSALRPT with the extension SQL, enter
SAVE DEPTSALRPT
To save the contents of the buffer in a file named DEPTSALRPT with the extension OLD, enter
SAVE DEPTSALRPT.OLD
SET
Sets a system variable to alter the SQL*Plus environment settings for your current session, for example, to:
• customize HTML formatting
• enable or disable the printing of column headings
• set the number of lines per page
• set the display width for data
You also use the Preferences screens in iSQL*Plus to set system variables.
Syntax
SET system_variable value
where system_variable and value represent one of the clauses shown in the "SET System Variable Summary" table following.
Usage
SQL*Plus maintains system variables (also called SET command variables) to enable you to set up a particular environment for a SQL*Plus session. You can change these system variables with the SET command and list them with the SHOW command.
SET ROLE and SET TRANSACTION are SQL commands (see the Oracle Database SQL Reference for more information). When not followed by the keywords TRANSACTION or ROLE, SET is assumed to be a SQL*Plus command.
SET System Variable Summary
System Variable Description
SET APPI[NFO]{ON | OFF | text}
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.
SET ARRAY[SIZE] {15 | n}
Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time.
SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}
Controls when Oracle Database commits pending changes to the database.
SET AUTOP[RINT] {ON | OFF}
Sets the automatic printing of bind variables.
SET AUTORECOVERY [ON | OFF]
ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE).
SET BLO[CKTERMINATOR] {. | c | ON | OFF}
Sets the non-alphanumeric character used to end PL/SQL blocks to c.
SET CMDS[EP] {; | c | ON | OFF}
Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.
SET COLSEP { | text}
In iSQL*Plus, SET COLSEP determines the column separator character to be printed between column output that is rendered inside <PRE> tags.
Sets the text to be printed between selected columns.
SET COM[PATIBILITY]{V7 | V8 | NATIVE}
Specifies the version of Oracle Database to which you are currently connected.
SET CON[CAT] {. | c | ON | OFF}
Sets the character you can use to terminate a substitution variable reference if you wish to immediately follow the variable with a character that SQL*Plus would otherwise interpret as a part of the substitution variable name.
SET COPYC[OMMIT] {0 | n}
Controls the number of batches after which the COPY command commits changes to the database.
SET COPYTYPECHECK {ON | OFF}
Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.
SET DEF[INE] {& | c | ON | OFF}
Sets the character used to prefix variables to c.
SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]
Sets the depth of the level to which you can recursively describe an object.
SET ECHO {ON | OFF}
Controls whether the START command lists each command in a script as the command is executed.
*SET EDITF[ILE] file_name[.ext]
Sets the default filename for the EDIT command.
SET EMB[EDDED] {ON | OFF}
Controls where on a page each report begins.
SET ESC[APE] {\ | c | ON | OFF}
Defines the character you enter as the escape character.
SET FEED[BACK] {6 | n | ON | OFF}
Displays the number of records returned by a query when a query selects at least n records.
SET FLAGGER {OFF | ENTRY | INTERMED[IATE] | FULL}
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
*SET FLU[SH] {ON | OFF}
Controls when output is sent to the user's display device.
SET HEA[DING] {ON | OFF}
Controls printing of column headings in reports.
SET HEADS[EP] { | | c | ON | OFF}
Defines the character you enter as the heading separator character.
SET INSTANCE [instance_path | LOCAL]
Changes the default instance for your session to the specified instance path.
SET LIN[ESIZE] {80 | n}
SET LIN[ESIZE] {150 | n} in iSQL*Plus
Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
SET LOBOF[FSET] {1 | n}
Sets the starting position from which CLOB and NCLOB data is retrieved and displayed.
SET LOGSOURCE [pathname]
Specifies the location from which archive logs are retrieved during recovery.
SET LONG {80 | n}
Sets maximum width (in bytes) for displaying LONG, CLOB, NCLOB and XMLType values; and for copying LONG values.
SET LONGC[HUNKSIZE] {80 | n}
Sets the size (in bytes) of the increments in which SQL*Plus retrieves a LONG, CLOB, NCLOB or XMLType value.
SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}]
Outputs HTML marked up text, which is the output used by iSQL*Plus.
SET NEWP[AGE] {1 | n | NONE}
Sets the number of blank lines to be printed from the top of each page to the top title.
SET NULL text
Sets the text that represents a null value in the result of a SQL SELECT command.
SET NUMF[ORMAT] format
Sets the default format for displaying numbers.
SET NUM[WIDTH] {10 | n}
Sets the default width for displaying numbers.
SET PAGES[IZE] {14 | n}
Sets the number of lines in each page.
SET PAU[SE] {ON | OFF | text}
Enables you to control scrolling of your terminal when running reports.
SET RECSEP {WR[APPED] | EA[CH] | OFF}
RECSEP tells SQL*Plus where to make the record separation.
SET RECSEPCHAR { | c}
Display or print record separators.
SET SERVEROUT[PUT] {ON | OFF} [SIZE n] [FOR[MAT] {WRA[PPED]
| WOR[D_WRAPPED] | TRU[NCATED]}]
Controls whether to display the output (that is, DBMS_OUTPUT PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
*SET SHIFT[INOUT] {VIS[IBLE] | INV[ISIBLE]}
Enables correct alignment for terminals that display shift characters.
*SET SHOW[MODE] {ON | OFF}
Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET.
*SET SQLBL[ANKLINES] {ON | OFF}
Controls whether SQL*Plus puts blank lines within a SQL command or script.
SET SQLC[ASE] {MIX[ED] | LO[WER] | UP[PER]}
Converts the case of SQL commands and PL/SQL blocks just prior to execution.
*SET SQLCO[NTINUE] {> | text}
Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (–).
*SET SQLN[UMBER] {ON | OFF}
Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block.
SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
Sets the behavior or output format of VARIABLE to that of the release or version specified by x.y[.z].
*SET SQLPRE[FIX] {# | c}
Sets the SQL*Plus prefix character.
*SET SQLP[ROMPT] {SQL> | text}
Sets the SQL*Plus command prompt.
SET SQLT[ERMINATOR] {; | c | ON | OFF}
Sets the character used to end and execute SQL commands to c.
*SET SUF[FIX] {SQL | text}
Sets the default file that SQL*Plus uses in commands that refer to scripts.
*SET TAB {ON | OFF}
Determines how SQL*Plus formats white space in terminal output.
*SET TERM[OUT] {ON | OFF}
Controls the display of output generated by commands executed from a script.
*SET TI[ME] {ON | OFF}
Controls the display of the current time.
SET TIMI[NG] {ON | OFF}
Controls the display of timing statistics.
*SET TRIM[OUT] {ON | OFF}
Determines whether SQL*Plus puts trailing blanks at the end of each displayed line.
*SET TRIMS[POOL] {ON | OFF}
Determines whether SQL*Plus puts trailing blanks at the end of each spooled line.
SET UND[ERLINE] {- | c | ON | OFF}
Sets the character used to underline column headings in SQL*Plus reports to c.
SET VER[IFY] {ON | OFF}
Controls whether SQL*Plus lists the text of a SQL statement or PL/SQL command before and after SQL*Plus replaces substitution variables with values.
SET WRA[P] {ON | OFF}
Controls whether SQL*Plus truncates the display of a SELECTed row if it is too long for the current line width.
*SET command not available in iSQL*Plus.
SET APPI[NFO]{ON | OFF | text}
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.
This enables the performance and resource usage of each script to be monitored by your DBA. The registered name appears in the MODULE column of the V$SESSION and V$SQLAREA virtual tables. You can also read the registered name using the DBMS_APPLICATION_INFO.READ_MODULE procedure.
ON registers scripts invoked by the @, @@ or START commands. OFF disables registering of scripts. Instead, the current value of text is registered. text specifies the text to register when no script is being run or when APPINFO is OFF, which is the default. The default for text is "SQL*Plus". If you enter multiple words for text, you must enclose them in quotes. The maximum length for text is limited by the DBMS_APPLICATION_INFO package.
The registered name has the format nn@xfilename where: nn is the depth level of script; x is '<' when the script name is truncated, otherwise, it is blank; and filename is the script name, possibly truncated to the length allowed by the DBMS_APPLICATION_INFO package interface.
For more information on the DBMS_APPLICATION_INFO package, see the Oracle Database Performance Tuning Guide.
Example
To display the value of APPINFO, as it is SET OFF by default, enter
SET APPINFO ON
SHOW APPINFO
APPINFO is ON and set to "SQL*Plus"
To change the default text, enter
SET APPINFO 'This is SQL*Plus'
To make sure that registration has taken place, enter
VARIABLE MOD VARCHAR2(50)
VARIABLE ACT VARCHAR2(40)
EXECUTE DBMS_APPLICATION_INFO.READ_MODULE(:MOD, :ACT);
PL/SQL procedure successfully completed.
PRINT MOD
MOD
This is SQL*Plus
To change APPINFO back to its default setting, enter
SET APPINFO OFF
SET ARRAY[SIZE] {15 | n}
Sets the number of rows that SQL*Plus will fetch from the database at one time.
Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency.
SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}
Controls when Oracle Database commits pending changes to the database after SQL or PL/SQL commands.
ON commits pending changes to the database after Oracle Database executes each successful INSERT, UPDATE, or DELETE, or PL/SQL block. OFF suppresses automatic committing so that you must commit changes manually (for example, with the SQL command COMMIT). IMMEDIATE functions in the same manner as ON. n commits pending changes to the database after Oracle Database executesn successful SQL INSERT, UPDATE, or DELETE commands, or PL/SQL blocks. n cannot be less than zero or greater than 2,000,000,000. The statement counter is reset to zero after successful completion of n INSERT, UPDATE or DELETE commands or PL/SQL blocks, a commit, a rollback, or a SET AUTOCOMMIT command.
SET AUTOCOMMIT does not alter the commit behavior when SQL*Plus exits. Any uncommitted data is committed by default.
Note:
For this feature, a PL/SQL block is considered one transaction, regardless of the actual number of SQL commands contained within it.
SET AUTOP[RINT] {ON | OFF}
Sets the automatic printing of bind variables.
ON or OFF controls whether SQL*Plus automatically displays bind variables (referenced in a successful PL/SQL block or used in an EXECUTE command).
See PRINT for more information about displaying bind variables.
SET AUTORECOVERY [ON | OFF]
ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.
No interaction is needed, provided the necessary files are in the expected locations with the expected names. The filenames used are derived from the values of the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT.
OFF, the default option, requires that you enter the filenames manually or accept the suggested default filename given. See RECOVER for more information about database recovery.
Example
To set the recovery mode to AUTOMATIC, enter
SET AUTORECOVERY ON
RECOVER DATABASE
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE).
The report can include execution statistics and the query execution path.
OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics. Information about EXPLAIN PLAN is documented in the Oracle Database SQL Reference.
Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.
The TRACEONLY option may be useful to suppress the query data of large queries. If STATISTICS is specified, SQL*Plus still fetches the query data from the server, however, the data is not displayed.
The AUTOTRACE report is printed after the statement has successfully completed.
Information about Execution Plans and the statistics is documented in the Oracle Database Performance Tuning Guide.
When SQL*Plus produces a STATISTICS report, a second connection to the database is automatically created. This connection is closed when the STATISTICS option is set to OFF, or you log out of SQL*Plus.
The formatting of your AUTOTRACE report may vary depending on the version of the server to which you are connected and the configuration of the server.
AUTOTRACE is not available when FIPS flagging is enabled.
See Tracing Statements for more information on AUTOTRACE.
SET BLO[CKTERMINATOR] {. | c | ON | OFF}
Sets the character used to end PL/SQL blocks to c.
It cannot be an alphanumeric character or a whitespace. To execute the block, you must issue a RUN or / (slash) command.
OFF means that SQL*Plus recognizes no PL/SQL block terminator. ON changes the value of c back to the default period (.), not the most recently used character.
SET CMDS[EP] {; | c | ON | OFF}
Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.
ON or OFF controls whether you can enter multiple commands on a line. ON automatically sets the command separator character to a semicolon (;).
Example
To specify a title with TTITLE and format a column with COLUMN, both on the same line, enter
SET CMDSEP +
TTITLE LEFT 'SALARIES' + COLUMN SALARY FORMAT $99,999
SELECT LAST_NAME, SALARY FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'SH_CLERK';
SALARIES
LAST_NAME SALARY
------------------------- --------
Taylor $3,200
Fleaur $3,100
Sullivan $2,500
Geoni $2,800
Sarchand $4,200
Bull $4,100
Dellinger $3,400
Cabrio $3,000
Chung $3,800
Dilly $3,600
Gates $2,900
Perkins $2,500
Bell $4,000
Everett $3,900
McCain $3,200
Jones $2,800
SALARIES
LAST_NAME SALARY
------------------------- --------
Walsh $3,100
Feeney $3,000
OConnell $2,600
Grant $2,600
20 rows selected.
SET COLSEP { | text}
Sets the column separator character printed between columns in output.
If the COLSEP variable contains blanks or punctuation characters, you must enclose it with single quotes. The default value for text is a single space.
In multi-line rows, the column separator does not print between columns that begin on different lines. The column separator does not appear on blank lines produced by BREAK ... SKIP n and does not overwrite the record separator. See SET RECSEP {WR[APPED] | EA[CH] | OFF} for more information.
The Column Separator (SET COLSEP) is only used in iSQL*Plus when Preformatted Output is ON (SET MARKUP HTML PREFORMAT).
Example
To set the column separator to "|" enter
SET MARKUP HTML PREFORMAT ON
SET COLSEP '|'
SELECT LAST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID = 20;
LAST_NAME |JOB_ID |DEPARTMENT_ID
-------------------------|----------|-------------
Hartstein |MK_MAN | 20
Fay |MK_REP | 20
SET COM[PATIBILITY]{V7 | V8 | NATIVE}
Specifies the version of Oracle Database SQL syntax to use.
Set COMPATIBILITY to V7 for Oracle7, or to V8 for Oracle8 or later. COMPATIBILITY always defaults to NATIVE. Set COMPATIBILITY for the version of Oracle Database SQL syntax you want to use on the connected database, otherwise, you may be unable to run any SQL commands.
Example
To run a script, SALARY.SQL, created with Oracle7 SQL syntax, enter
SET COMPATIBILITY V7
START SALARY
After running the file, reset compatibility to NATIVE to run scripts created for Oracle Database 10g:
SET COMPATIBILITY NATIVE
Alternatively, you can add the command SET COMPATIBILITY V7 to the beginning of the script, and reset COMPATIBILITY to NATIVE at the end of the file.
SET CON[CAT] {. | c | ON | OFF}
Sets the character used to terminate a substitution variable reference when SQL*Plus would otherwise interpret the next character as a part of the variable name.
SQL*Plus resets the value of CONCAT to a period when you switch CONCAT on.
SET COPYC[OMMIT] {0 | n}
Controls the number of rows after which the COPY command commits changes to the database.
COPY commits rows to the destination database each time it copies n row batches. Valid values are zero to 5000. You can set the size of a batch with the ARRAYSIZE variable. If you set COPYCOMMIT to zero, COPY performs a commit only at the end of a copy operation.
SET COPYTYPECHECK {ON | OFF}
Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.
This is to facilitate copying to DB2, which requires that a CHAR be copied to a DB2 DATE.
SET DEF[INE] {& | c | ON | OFF}
Sets the character used to prefix substitution variables to c.
ON or OFF controls whether SQL*Plus will scan commands for substitution variables and replace them with their values. ON changes the value of c back to the default '&', not the most recently used character. The setting of DEFINE to OFF overrides the setting of the SCAN variable.
See SET SCAN {ON|OFF} (obsolete) for more information on the SCAN variable.
SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]
Sets the depth of the level to which you can recursively describe an object.
The valid range of the DEPTH clause is from 1 to 50. If you SET DESCRIBE DEPTH ALL, then the depth will be set to 50, which is the maximum level allowed. You can also display the line number and indentation of the attribute or column name when an object contains multiple object types. Use the SET LINESIZE command to control the width of the data displayed.
See DESCRIBE for more information about describing objects.
Example
To create an object type ADDRESS, enter
CREATE TYPE ADDRESS AS OBJECT
( STREET VARCHAR2(20),
CITY VARCHAR2(20)
);
/
Type created
To create the table EMPLOYEE that contains a nested object, EMPADDR, of type ADDRESS, enter
CREATE TABLE EMPLOYEE
(LAST_NAME VARCHAR2(30),
EMPADDR ADDRESS,
JOB_ID VARCHAR2(20),
SALARY NUMBER(7,2)
);
/
Table created
To describe the table EMPLOYEE to a depth of two levels, and to indent the output and display line numbers, enter:
SET DESCRIBE DEPTH 2 LINENUM ON INDENT ON
DESCRIBE employee
Name Null? Type
------------------------------- -------- --------------------------
1 LAST_NAME VARCHAR2(30)
2 EMPADDR ADDRESS
3 2 STREET VARCHAR2(20)
4 2 CITY VARCHAR2(20)
5 JOB_ID VARCHAR2(20)
6 SALARY NUMBER(7,2)
SET ECHO {ON | OFF}
Controls whether or not to echo commands in a script that is executed with @, @@ or START. ON displays the commands on screen. OFF suppresses the display. ECHO does not affect the display of commands you enter interactively or redirect to SQL*Plus from the operating system.
SET EDITF[ILE] file_name[.ext]
SET EDITFILE is not supported in iSQL*Plus
Sets the default filename for the EDIT command. See EDIT for more information about the EDIT command. The default filename for the EDIT command is afiedt.buf which is the SQL buffer. The buffer has no command history list and does not record SQL*Plus commands.
You can include a path and/or file extension. See SET SUF[FIX] {SQL | text} for information on changing the default extension. The default filename and maximum filename length are operating system specific.
SET EMB[EDDED] {ON | OFF}
Controls where on a page each report begins.
OFF forces each report to start at the top of a new page. ON enables a report to begin anywhere on a page. Set EMBEDDED to ON when you want a report to begin printing immediately following the end of the previously run report.
SET ESC[APE] {\ | c | ON | OFF}
Defines the character used as the escape character.
OFF undefines the escape character. ON enables the escape character. ON changes the value of c back to the default "\".
You can use the escape character before the substitution character (set through SET DEFINE) to indicate that SQL*Plus should treat the substitution character as an ordinary character rather than as a request for variable substitution.
Example
If you define the escape character as an exclamation point (!), then
SET ESCAPE !
ACCEPT v1 PROMPT 'Enter !&1:'
displays this prompt:
Enter &1:
To set the escape character back to the default value of \ (backslash), enter
SET ESCAPE ON
SET FEED[BACK] {6 | n | ON | OFF}
Displays the number of records returned by a script when a script selects at least n records.
ON or OFF turns this display on or off. Turning feedback ON sets n to 1. Setting feedback to zero is equivalent to turning it OFF.
SET FEEDBACK OFF also turns off the statement confirmation messages such as 'Table created' and 'PL/SQL procedure successfully completed' that are displayed after successful SQL or PL/SQL statements.
SET FLAGGER {OFF | ENTRY | INTERMED[IATE] | FULL}
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
If any non-standard constructs are found, the Oracle Database Server flags them as errors and displays the violating syntax. This is the equivalent of the SQL language ALTER SESSION SET FLAGGER command.
You may execute SET FLAGGER even if you are not connected to a database. FIPS flagging will remain in effect across SQL*Plus sessions until a SET FLAGGER OFF (or ALTER SESSION SET FLAGGER = OFF) command is successful or you exit SQL*Plus.
When FIPS flagging is enabled, SQL*Plus displays a warning for the CONNECT, DISCONNECT, and ALTER SESSION SET FLAGGER commands, even if they are successful.
SET FLU[SH] {ON | OFF}
SET FLUSH is not supported in iSQL*Plus
Controls when output is sent to the user's display device. OFF enables the operating system to buffer output. ON disables buffering. FLUSH only affects display output, it does not affect spooled output.
Use OFF only when you run a script non-interactively (that is, when you do not need to see output and/or prompts until the script finishes running). The use of FLUSH OFF may improve performance by reducing the amount of program I/O.
SET HEA[DING] {ON | OFF}
Controls printing of column headings in reports.
ON prints column headings in reports; OFF suppresses column headings.
The SET HEADING OFF command does not affect the column width displayed, it only suppresses the printing of the column header itself.
Example
To suppress the display of column headings in a report, enter
SET HEADING OFF
If you then run a SQL SELECT command
SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'AC_MGR';
the following output results:
Higgins 12000
To turn the display of column headings back on, enter
SET HEADING ON
SET HEADS[EP] { | | c | ON | OFF}
Defines the character used as a line break in column headings.
The heading separator character cannot be alphanumeric or white space. You can use the heading separator character in the COLUMN command and in the old forms of BTITLE and TTITLE to divide a column heading or title onto more than one line. ON or OFF turns heading separation on or off. When heading separation is OFF, SQL*Plus prints a heading separator character like any other character. ON changes the value of c back to the default "|".
The Heading Separator character (SET HEADSEP) is only supported in iSQL*Plus when the Preformatted Output preference is ON (SET MARKUP HTML PREFORMAT).
SET INSTANCE [instance_path | LOCAL]
Changes the default instance for your session to the specified instance path.
Using the SET INSTANCE command does not connect to a database. The default instance is used for commands when no instance is specified. Any commands preceding the first use of SET INSTANCE communicate with the default instance.
To reset the instance to the default value for your operating system, you can either enter SET INSTANCE with no instance_path or SET INSTANCE LOCAL.
Note, you can only change the instance when you are not currently connected to any instance. That is, you must first make sure that you have disconnected from the current instance, then set or change the instance, and reconnect to an instance in order for the new setting to be enabled.
This command may only be issued when Oracle Net is running. You can use any valid Oracle Net connect identifier as the specified instance path. See your operating system-specific Oracle Database documentation for a complete description of how your operating system specifies Oracle Net connect identifiers. The maximum length of the instance path is 64 characters.
Example
To set the default instance to "PROD1" enter
DISCONNECT
SET INSTANCE PROD1
To set the instance back to the default of local, enter
SET INSTANCE local
You must disconnect from any connected instances to change the instance.
SET LIN[ESIZE] {80 | n}
SET LIN[ESIZE] {150 | n} in iSQL*Plus
Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
It also controls the position of centered and right-aligned text in TTITLE, BTITLE, REPHEADER and REPFOOTER. Changing the linesize setting can affect text wrapping in output from the DESCRIBE command. DESCRIBE output columns are typically allocated a proportion of the linesize. Decreasing or increasing the linesize may give unexpected text wrapping in your display. You can define LINESIZE as a value from 1 to a maximum that is system dependent.
SET LOBOF[FSET] {1 | n}
Sets the starting position from which CLOB and NCLOB data is retrieved and displayed.
Example
To set the starting position from which a CLOB column's data is retrieved to the 22nd position, enter
SET LOBOFFSET 22
The CLOB data will wrap on your screen; SQL*Plus will not truncate until the 23rd character.
SET LOGSOURCE [pathname]
Specifies the location from which archive logs are retrieved during recovery.
The default value is set by the LOG_ARCHIVE_DEST initialization parameter in the Oracle Database initialization file, init.ora. Using the SET LOGSOURCE command without a pathname restores the default location.
Example
To set the default location of log files for recovery to the directory "/usr/oracle10/dbs/arch" enter
SET LOGSOURCE "/usr/oracle10/dbs/arch"
RECOVER DATABASE
SET LONG {80 | n}
Sets maximum width (in bytes) for displaying CLOB, LONG, NCLOB and XMLType values; and for copying LONG values.
The maximum value of n is 2,000,000,000 bytes.
Example
To set the maximum number of bytes to fetch for displaying and copying LONG values, to 500, enter
SET LONG 500
The LONG data will wrap on your screen; SQL*Plus will not truncate until the 501st byte. The default for LONG is 80 bytes.
SET LONGC[HUNKSIZE] {80 | n}
Sets the size (in bytes) of the increments SQL*Plus uses to retrieve a CLOB, LONG, NCLOB or XMLType value.
LONGCHUNKSIZE is not used for object relational queries such as CLOB, or NCLOB.
Example
To set the size of the increments in which SQL*Plus retrieves LONG values to 100 bytes, enter
SET LONGCHUNKSIZE 100
The LONG data will be retrieved in increments of 100 bytes until the entire value is retrieved or the value of SET LONG is reached, whichever is the smaller.
SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON |OFF}]
Outputs HTML marked up text, which is the output used by iSQL*Plus.
Beware of using options which generate invalid HTML output in iSQL*Plus as it may corrupt the browser screen. The HEAD and BODY options may be useful for dynamic reports and for reports saved to local files.
To be effective, SET MARKUP commands that change values in dynamic report output must occur before statements that produce query output. The first statement that produces query output triggers the output of information affected by SET MARKUP such as HEAD and TABLE settings. Subsequent SET MARKUP commands have no effect on the information already sent to the report.
SET MARKUP only specifies that SQL*Plus output will be HTML encoded. You must use SET MARKUP HTML ON SPOOL ON and the SQL*Plus SPOOL command to create and name a spool file, and to begin writing HMTL output to it. SET MARKUP has the same options and behavior as SQLPLUS -MARKUP.
See "MARKUP Options" for detailed information. For examples of usage, see SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON |OFF}] [PRE[FORMAT] {ON | OFF}], and "Generating HTML Reports from SQL*Plus".
Use the SHOW MARKUP command to view the status of MARKUP options.
Example
The following is a script which uses the SET MARKUP HTML command to enable HTML marked up text to be spooled to a specified file:
Note:
The SET MARKUP example command is laid out for readability using line continuation characters "–" and spacing. Command options are concatenated in normal entry.
Use your favorite text editor to enter the commands necessary to set up the HTML options and the query you want for your report.
SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title> -
STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"
SET ECHO OFF
SPOOL employee.htm
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON
As this script contains SQL*Plus commands, do not attempt to run it with / (slash) from the buffer because it will fail. Save the script in your text editor and use START to execute it:
START employee.sql
As well as writing the HTML spool file, employee.htm, the output is also displayed on screen because SET TERMOUT defaults to ON. You can view the spool file, employee.htm, in your web browser. It should appear something like the following:
Description of the illustration markup.gif
SET NEWP[AGE] {1 | n | NONE}
Sets the number of blank lines to be printed from the top of each page to the top title. A value of zero places a formfeed at the beginning of each page (including the first page) and clears the screen on most terminals. If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between the report pages.
SET NULL text
Sets the text displayed whenever a null value occurs in the result of a SQL SELECT command.
Use the NULL clause of the COLUMN command to override the setting of the NULL variable for a given column. The default output for a null is blank ("").
SET NUMF[ORMAT] format
Sets the default format for displaying numbers. Enter a number format for format. Enter
SET NUMFORMAT ""
to use the default field width and formatting model specified by SET NUMWIDTH.
SET NUM[WIDTH] {10 | n}
Sets the default width for displaying numbers. See the FORMAT clause of the COLUMN command and SET NUMF[ORMAT] format and SET NUM[WIDTH] {10 | n}. for number format descriptions.
COLUMN FORMAT settings take precedence over SET NUMFORMAT settings, which take precedence over SET NUMWIDTH settings.
SET PAGES[IZE] {14 | n}
Sets the number of rows on each page of output in iSQL*Plus, and the number of lines on each page of output in command-line and Windows GUI. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.
In iSQL*Plus, sets the number of rows displayed on each page. Error and informational messages are not counted in the page size, so pages may not always be exactly the same length. The default pagesize for iSQL*Plus is 24.
SET PAU[SE] {ON | OFF | text}
Enables you to control scrolling of your terminal when running reports. You need to first, SET PAUSE text, and then SET PAUSE ON if you want text to appear each time SQL*Plus pauses.
In command-line and Windows GUI, SET PAUSE ON pauses output at the beginning of each PAGESIZE number of lines of report output. Press Return to view more output. SET PAUSE text specifies the text to be displayed each time SQL*Plus pauses. Multiple words in text must be enclosed in single quotes.
You can embed terminal-dependent escape sequences in the PAUSE command. These sequences allow you to create inverse video messages or other effects on terminals that support such characteristics.
In iSQL*Plus, SET PAUSE ON displays the value of text, then pauses output and displays a Next Page button after PAGESIZE number of rows of report output. Click the Next Page button to view more report output. The Next Page button is not displayed on the final page of output.
SET RECSEP {WR[APPED] | EA[CH] | OFF}
RECSEP tells SQL*Plus where to make the record separation.
For example, if you set RECSEP to WRAPPED, SQL*Plus prints a record separator only after wrapped lines. If you set RECSEP to EACH, SQL*Plus prints a record separator following every row. If you set RECSEP to OFF, SQL*Plus does not print a record separator.
The Display Record Separator preference (SET RECSEP) is only supported in iSQL*Plus when Preformatted Output is On (SET MARKUP HTML PREFORMAT).
SET RECSEPCHAR { | c}
Defines the character to display or print to separate records.
A record separator consists of a single line of the RECSEPCHAR (record separating character) repeated LINESIZE times. The default is a single space.
SET SERVEROUT[PUT] {ON | OFF} [SIZE n] [FOR[MAT] {WRA[PPED]
| WOR[D_WRAPPED] | TRU[NCATED]}]
Controls whether to display output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
OFF suppresses the output of DBMS_OUTPUT.PUT_LINE; ON displays the output.
SIZE sets the number of bytes of the output that can be buffered within the Oracle Database server. The default for n is 2000. n cannot be less than 2000 or greater than 1,000,000.
Every server output line begins on a new output line.
When WRAPPED is enabled SQL*Plus wraps the server output within the line size specified by SET LINESIZE, beginning new lines when required.
When WORD_WRAPPED is enabled, each line of server output is wrapped within the line size specified by SET LINESIZE. Lines are broken on word boundaries. SQL*Plus left justifies each line, skipping all leading whitespace.
When TRUNCATED is enabled, each line of server output is truncated to the line size specified by SET LINESIZE.
For more information on DBMS_OUTPUT.PUT_LINE, see your Oracle Database Application Developer's Guide - Fundamentals.
Example
To enable text display in a PL/SQL block using DBMS_OUTPUT.PUT_LINE, enter
SET SERVEROUTPUT ON
The following example shows what happens when you execute an anonymous procedure with SET SERVEROUTPUT ON:
BEGIN
DBMS_OUTPUT.PUT_LINE('Task is complete');
END;
/
Task is complete.
PL/SQL procedure successfully completed.
The following example shows what happens when you create a trigger with SET SERVEROUTPUT ON:
CREATE TABLE SERVER_TAB (Letter CHAR);
CREATE TRIGGER SERVER_TRIG BEFORE INSERT OR UPDATE -
OR DELETE
ON SERVER_TAB
BEGIN
DBMS_OUTPUT.PUT_LINE('Task is complete.');
END;
/
Trigger Created.
INSERT INTO SERVER_TAB VALUES ('M');
DROP TABLE SERVER_TAB;
/* Remove SERVER_TAB from database */
Task is complete.
1 row created.
To set the output to WORD_WRAPPED, enter
SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
SET LINESIZE 20
BEGIN
DBMS_OUTPUT.PUT_LINE('If there is nothing left to do');
DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?');
END;
/
If there is nothing
left to do
shall we continue
with plan B?
To set the output to TRUNCATED, enter
SET SERVEROUTPUT ON FORMAT TRUNCATED
SET LINESIZE 20
BEGIN
DBMS_OUTPUT.PUT_LINE('If there is nothing left to do');
DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?');
END;
/
If there is nothing
shall we continue wi
SET SHIFT[INOUT] {VIS[IBLE] | INV[ISIBLE]}
SET SHIFTINOUT is not supported in iSQL*Plus
Enables correct alignment for terminals that display shift characters. The SET SHIFTINOUT command is useful for terminals which display shift characters together with data (for example, IBM 3270 terminals). You can only use this command with shift sensitive character sets (for example, JA16DBCS).
Use VISIBLE for terminals that display shift characters as a visible character (for example, a space or a colon). INVISIBLE is the opposite and does not display any shift characters.
Example
To enable the display of shift characters on a terminal that supports them, enter
SET SHIFTINOUT VISIBLE
SELECT LAST_NAME, JOB_ID FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000;
LAST_NAME JOB_ID
---------- ----------
:JJOO: :AABBCC:
:AA:abc :DDEE:e
where ":" = visible shift character
uppercase represents multibyte characters
lowercase represents singlebyte characters
SET SHOW[MODE] {ON | OFF}
SET SHOWMODE is not supported in iSQL*Plus
Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET. ON lists the settings; OFF suppresses the listing. SHOWMODE ON has the same behavior as the obsolete SHOWMODE BOTH.
SET SQLBL[ANKLINES] {ON | OFF}
SET SQLBLANKLINES is not supported in iSQL*Plus
Controls whether SQL*Plus puts blank lines within a SQL command or script. ON interprets blank lines and new lines as part of a SQL command or script. OFF, the default value, does not allow blank lines or new lines in a SQL command or script or script.
Enter the BLOCKTERMINATOR to stop SQL command entry without running the SQL command. Enter the SQLTERMINATOR character to stop SQL command entry and run the SQL statement.
Example
To allow blank lines in a SQL statement, enter
SET SQLBLANKLINES ON
REM Using the SQLTERMINATOR (default is ";")
REM Could have used the BLOCKTERMINATOR (default is ".")
SELECT *
FROM
DUAL
;
The following output results:
D
-
X
SET SQLC[ASE] {MIX[ED] | LO[WER] | UP[PER]}
Converts the case of SQL commands and PL/SQL blocks just prior to execution.
SQL*Plus converts all text within the command, including quoted literals and identifiers, to uppercase if SQLCASE equals UPPER, to lowercase if SQLCASE equals LOWER, and makes no changes if SQLCASE equals MIXED.
SQLCASE does not change the SQL buffer itself.
SET SQLCO[NTINUE] {> | text}
SET SQLCONTINUE is not supported in iSQL*Plus
Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (–).
Example
To set the SQL*Plus command continuation prompt to an exclamation point followed by a space, enter
SET SQLCONTINUE '! '
SQL*Plus will prompt for continuation as follows:
TTITLE 'MONTHLY INCOME' -
! RIGHT SQL.PNO SKIP 2 -
! CENTER 'PC DIVISION'
The default continuation prompt is "> ".
SET SQLN[UMBER] {ON | OFF}
SET SQLNUMBER is not supported in iSQL*Plus
Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block. ON sets the prompt to be the line number. OFF sets the prompt to the value of SQLPROMPT.
SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
Sets the behavior to that of the release or version specified by x.y[.z].
Where x is the version number, y is the release number, and z is the update number. For example, 8.1.7, 9.0.1 or 10.1. The features affected by SQLPLUSCOMPATIBILITY are tabulated in the SQL*Plus Compatibility Matrix shown. You can also set the value of SQLPLUSCOMPATIBILITY using the -C[OMPATIBILITY] argument of the SQLPLUS command when starting SQL*Plus from the command line.
The default setting for SQLPLUSCOMPATIBILITY is the value of the SQL*Plus client, which is 10.1.0.
It is recommended that you add SET SQLPLUSCOMPATIBILITY 10.1.0 to your scripts to maximize their compatibility with future versions of SQL*Plus.
SQL*Plus Compatibility Matrix
The SQL*Plus Compatibility Matrix tabulates behavior affected by each SQL*Plus compatibility setting. SQL*Plus compatibility modes can be set in three ways:
• You can include a SET SQLPLUSCOMPATIBILITY command in your site or user profile. On installation, there is no SET SQLPLUSCOMPATIBILITY setting in glogin.sql. Therefore the default compatibility is 10.1.
• You can use the SET SQLPLUSCOMPATIBILITY {x.y[.z]} command during a session to set the SQL*Plus behavior you want for that session.
The following table shows the release of SQL*Plus which introduced the behavior change, and hence the minimum value of SQLPLUSCOMPATIBILITY to obtain that behavior. For example, to obtain the earlier behavior of the VARIABLE command, you must either use a version of SQL*Plus earlier than 9.0.1, or you must use a SQLPLUSCOMPATIBILITY value of less than 9.0.1. The lowest value that can be set for SQLPLUSCOMPATIBILITY is 7.3.4
Table 13-4 Compatibility Matrix
Value Consequence When available
>=10.1 SHOW ERRORS sorts PL/SQL error messages using new columns only available in Oracle Database 10g. 10.1
>=10.1 SPOOL Options CREATE, REPLACE, SAVE were added which may affect filename parsing on some platforms. 10.1
>=10.1 SET SQLPROMPT 10.1
>=10.1 Whitespace characters are allowed in Windows file names that are enclosed in quotes. Some other special punctuation characters are now disallowed in Windows. 10.1
>=10.1 Glogin/login files are called for each reconnect. 10.1
<10.1 Uses the obsolete DOC> prompt when echoing /* comments. 10.1
>= 9.2 A wide column defined FOLD_AFTER may be displayed at the start of a new line. Otherwise it is incorrectly put at the end of the preceding line with a smaller width than expected. 9.2.
>= 9.0 Whitespace before a slash ("/") in a SQL statement is ignored and the slash is taken to mean execute the statement. Otherwise the slash is treated as part of the statement, for example, as a division sign. 9.0.1.4.
>= 9.0 The length specified for NCHAR and NVARCHAR2 types is characters. Otherwise the length may represent bytes or characters depending on the character set. 9.0.1
SET SQLPRE[FIX] {# | c}
SET SQLPREFIX is not supported in iSQL*Plus
Sets the SQL*Plus prefix character. While you are entering a SQL command or PL/SQL block, you can enter a SQL*Plus command on a separate line, prefixed by the SQL*Plus prefix character. SQL*Plus will execute the command immediately without affecting the SQL command or PL/SQL block that you are entering. The prefix character must be a non-alphanumeric character.
SET SQLP[ROMPT] {SQL> | text}
SET SQLPROMPT is not supported in iSQL*Plus
Sets the SQL*Plus command prompt. SET SQLPROMPT substitute variables dynamically. This enables the inclusion of runtime variables such as the current connection identifier. Substitution variables used in SQLPROMPT do not have to be prefixed with '&', and they can be used and accessed like any other substitution variable. Variable substitution is not attempted for 'SQL' in the default prompt.
Variable substitution occurs each time SQLPROMPT is SET. If SQLPROMPT is included in glogin.sql, then substitution variables in SQLPROMPT are refreshed with each login or connect.
Example
You need the Select Any Table privilege to successfully run the following example scripts.
To change your SQL*Plus prompt to display your connection identifier, enter:
SET SQLPROMPT "_CONNECT_IDENTIFIER > "
To set the SQL*Plus command prompt to show the current user, enter
SET SQLPROMPT "_USER > "
To change your SQL*Plus prompt to display your the current date, the current user and the users privilege level, enter:
SET SQLPROMPT "_DATE _USER _PRIVILEGE> "
To change your SQL*Plus prompt to display a variable you have defined, enter:
DEFINE mycon = Prod1
SET SQLPROMPT "mycon> "
Prod1>
Text in nested quotes is not parsed for substitution. To have a SQL*Plus prompt of your username, followed by "@", and then your connection identifier, enter:
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "
SET SQLT[ERMINATOR] {; | c | ON | OFF}
Sets the character used to end script or data entry for PL/SQL blocks or SQL statements, to execute the script and to load it into the buffer.
It cannot be an alphanumeric character or a whitespace. OFF means that SQL*Plus recognizes no command terminator; you terminate a SQL command by entering an empty line or a slash (/). If SQLBLANKLINES is set ON, you must use the BLOCKTERMINATOR to terminate a SQL command. ON resets the terminator to the default semicolon (;).
SET SUF[FIX] {SQL | text}
SET SUFFIX is not supported in iSQL*Plus
Sets the default file extension that SQL*Plus uses in commands that refer to scripts. SUFFIX does not control extensions for spool files.
Example
To change the default command-file extension from the default, .SQL to .TXT, enter
SET SUFFIX TXT
If you then enter
GET EXAMPLE
SQL*Plus will look for a file named EXAMPLE.TXT instead of EXAMPLE.SQL.
SET TAB {ON | OFF}
SET TAB is not supported in iSQL*Plus
Determines how SQL*Plus formats white space in terminal output. OFF uses spaces to format white space in the output. ON uses the TAB character. TAB settings are every eight characters. The default value for TAB is system dependent.
SET TERM[OUT] {ON | OFF}
SET TERMOUT is not supported in iSQL*Plus
Controls the display of output generated by commands in a script that is executed with @, @@ or START. OFF suppresses the display so that you can spool output to a file without displaying the output on screen. ON displays the output on screen. TERMOUT OFF does not affect output from commands you enter interactively or redirect to SQL*Plus from the operating system.
SET TI[ME] {ON | OFF}
SET TIME is not supported in iSQL*Plus
Controls the display of the current time. ON displays the current time before each command prompt. OFF suppresses the time display.
SET TIMI[NG] {ON | OFF}
Controls the display of timing statistics.
ON displays timing statistics on each SQL command or PL/SQL block run. OFF suppresses timing of each command.
See TIMING for information on timing multiple commands.
SET TRIM[OUT] {ON | OFF}
SET TRIMOUT is not supported in iSQL*Plus
Determines whether SQL*Plus puts trailing blanks at the end of each displayed line. ON removes blanks at the end of each line, improving performance especially when you access SQL*Plus from a slow communications device. OFF enables SQL*Plus to display trailing blanks. TRIMOUT ON does not affect spooled output.
SET TRIMS[POOL] {ON | OFF}
SET TRIMSPOOL is not supported in iSQL*Plus
Determines whether SQL*Plus puts trailing blanks at the end of each spooled line. ON removes blanks at the end of each line. OFF enables SQL*Plus to include trailing blanks. TRIMSPOOL ON does not affect terminal output.
SET UND[ERLINE] {- | c | ON | OFF}
Sets the character used to underline column headings in reports. The underline character cannot be an alphanumeric character or a white space. ON or OFF turns underlining on or off. ON changes the value of c back to the default "-".
SET UNDERLINE is supported in iSQL*Plus when SET MARKUP HTML PREFORMAT ON is set.
SET VER[IFY] {ON | OFF}
Controls whether to list the text of a SQL statement or PL/SQL command before and after replacing substitution variables with values. ON lists the text; OFF suppresses the listing.
SET WRA[P] {ON | OFF}
Controls whether to truncate the display of a selected row if it is too long for the current line width. OFF truncates the selected row; ON enables the selected row to wrap to the next line.
Use the WRAPPED and TRUNCATED clauses of the COLUMN command to override the setting of WRAP for specific columns.
SHOW
Syntax
SHO[W] option
where option represents one of the following terms or clauses:
system_variable
ALL
BTI[TLE]
ERR[ORS] [ { FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
| VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS } [schema.]name]
LNO
PARAMETERS [parameter_name]
PNO
RECYC[LEBIN] [original_name]
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L] (Not available in iSQL*Plus)
SQLCODE
TTI[TLE]
USER
Shows the value of a SQL*Plus system variable or the current SQL*Plus environment. SHOW SGA requires a DBA privileged login.
Terms
system_variable
Represents any system variable set by the SET command.
ALL
Lists the settings of all SHOW options, except ERRORS and SGA, in alphabetical order.
BTI[TLE]
Shows the current BTITLE definition.
ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
| VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
Shows the compilation errors of a stored procedure (includes stored functions, procedures, and packages). After you use the CREATE command to create a stored procedure, a message is displayed if the stored procedure has any compilation errors. To see the errors, you use SHOW ERRORS.
When you specify SHOW ERRORS with no arguments, SQL*Plus shows compilation errors for the most recently created or altered stored procedure. When you specify the type (function, procedure, package, package body, trigger, view, type, type body, dimension, or java class) and the name of the PL/SQL stored procedure, SQL*Plus shows errors for that stored procedure. For more information on compilation errors, see your PL/SQL User's Guide and Reference.
schema contains the named object. If you omit schema, SHOW ERRORS assumes the object is located in your current schema.
SHOW ERRORS output displays the line and column number of the error (LINE/COL) as well as the error itself (ERROR). LINE/COL and ERROR have default widths of 8 and 65, respectively. You can use the COLUMN command to alter the default widths.
LNO
Shows the current line number (the position in the current page of the display and/or spooled output).
PARAMETERS [parameter_name]
Displays the current values for one or more initialization parameters. You can use a string after the command to see a subset of parameters whose names include that string. For example, if you enter:
SHOW PARAMETERS COUNT
NAME TYPE VALUE
------------------------------ ----- -----
db_file_multiblock_read_count integer 12
spin_count integer 0
The SHOW PARAMETERS command, without any string following the command, displays all initialization parameters.
The column names and formats used in the SHOW PARAMETERS output is set in the site profile file, glogin.sql. The value column display may be truncated.
Your output may vary depending on the version and configuration of the Oracle Database server to which you are connected. You need SELECT ON V_$PARAMETER object privileges to use the PARAMETERS clause, otherwise you will receive a message
ORA-00942: table or view does not exist
PNO
Shows the current page number.
RECYC[LEBIN] [original_name]
Shows objects in the recycle bin that can be reverted with the FLASHBACK BEFORE DROP command. You do not need to remember column names, or interpret the less readable output from the query:
SELECT * FROM USER_RECYCLEBIN
The query returns four columns displayed in the following order:
Column Name Description
ORIGINAL NAME Shows the original name used when creating the object.
RECYCLEBIN NAME Shows the name used to identify the object in the recyclebin.
OBJECT TYPE Shows the type of the object.
DROP TIME Shows the time when the object was dropped.
The output columns can be formatted with the COLUMN command. The default COLUMN formatting is in the site profile, glogin.sql.
For DBAs, the command lists their own objects as they have their own user_recyclebin view.
REL[EASE]
Shows the release number of Oracle Database that SQL*Plus is accessing.
REPF[OOTER]
Shows the current REPFOOTER definition.
REPH[EADER]
Shows the current REPHEADER definition.
SPOO[L]
Shows whether output is being spooled.
SGA
Displays information about the current instance's System Global Area. You need SELECT ON V_$SGA object privileges otherwise you will receive a message
ORA-00942: table or view does not exist
SQLCODE
Shows the value of SQL.SQLCODE (the SQL return code of the most recent operation).
TTI[TLE]
Shows the current TTITLE definition.
USER
Shows the username you are currently using to access SQL*Plus. If you connect as "/ AS SYSDBA", then the SHOW USER command displays
USER is "SYS"
Examples
To display information about the SGA, enter
SHOW SGA
Total System Global Area 7629732 bytes
Fixed Size 60324 bytes
Variable Size 6627328 bytes
Database Buffers 409600 bytes
Redo Buffers 532480 bytes
The following example illustrates how to create a stored procedure and then show its compilation errors:
CONNECT SYSTEM/MANAGER
CREATE PROCEDURE HR.PROC1 AS
BEGIN
:P1 := 1;
END;
/
Warning: Procedure created with compilation errors.
SHOW ERRORS PROCEDURE PROC1
NO ERRORS.
SHOW ERRORS PROCEDURE HR.PROC1
Errors for PROCEDURE HR PROC1:
LINE/COL ERROR
3/3 PLS-00049: bad bind variable 'P1'
To show whether AUTORECOVERY is enabled, enter
SHOW AUTORECOVERY
AUTORECOVERY ON
To display the connect identifier for the default instance, enter
SHOW INSTANCE
INSTANCE "LOCAL"
To display the location for archive logs, enter
SHOW LOGSOURCE
LOGSOURCE "/usr/oracle90/dbs/arch"
To display objects that can be reverted with the FLASHBACK commands where CJ1 and ABC were objects dropped, enter:
SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-------------- ------------------ ------------ --------------------
CJ1 RB$$29458$TABLE$0 TABLE 2003-01-22:14:54:07
ABC RB$$29453$TABLE$0 TABLE 2003-01-20:18:50:29
To restore CJ1, enter
FLASHBACK TABLE CJ1 TO BEFORE DROP;
SHUTDOWN
Syntax
SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]]
Shuts down a currently running Oracle Database instance, optionally closing and dismounting a database.
Terms
ABORT
Proceeds with the fastest possible shutdown of the database without waiting for calls to complete or users to disconnect.
Uncommitted transactions are not rolled back. Client SQL statements currently being processed are terminated. All users currently connected to the database are implicitly disconnected and the next database startup will require instance recovery.
You must use this option if a background process terminates abnormally.
IMMEDIATE
Does not wait for current calls to complete or users to disconnect from the database.
Further connects are prohibited. The database is closed and dismounted. The instance is shutdown and no instance recovery is required on the next database startup.
NORMAL
NORMAL is the default option which waits for users to disconnect from the database.
Further connects are prohibited. The database is closed and dismounted. The instance is shutdown and no instance recovery is required on the next database startup.
TRANSACTIONAL [LOCAL]
Performs a planned shutdown of an instance while allowing active transactions to complete first. It prevents clients from losing work without requiring all users to log off.
No client can start a new transaction on this instance. Attempting to start a new transaction results in disconnection. After completion of all transactions, any client still connected to the instance is disconnected. Now the instance shuts down just as it would if a SHUTDOWN IMMEDIATE statement was submitted. The next startup of the database will not require any instance recovery procedures.
The LOCAL mode specifies a transactional shutdown on the local instance only, so that it only waits on local transactions to complete, not all transactions. This is useful, for example, for scheduled outage maintenance.
Usage
SHUTDOWN with no arguments is equivalent to SHUTDOWN NORMAL.
You must be connected to a database as SYSOPER, or SYSDBA. You cannot connect through a multi-threaded server. See CONNECT for more information about connecting to a database.
Examples
To shutdown the database in normal mode, enter
SHUTDOWN
Database closed.
Database dismounted.
Oracle instance shut down.
SPOOL
SPOOL is not available in iSQL*Plus.
Syntax
SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
Stores query results in a file, or optionally sends the file to a printer. In iSQL*Plus, use the preference settings to direct output to a file.
Terms
file_name[.ext]
Represents the name of the file to which you wish to spool. SPOOL followed by file_name begins spooling displayed output to the named file. If you do not specify an extension, SPOOL uses a default extension (LST or LIS on most systems).
CRE[ATE]
Creates a new file with the name specified.
REP[LACE]
Replaces the contents of an existing file. If the file does not exist, REPLACE creates the file. This is the default behavior.
APP[END]
Adds the contents of the buffer to the end of the file you specify.
OFF
Stops spooling.
OUT
Stops spooling and sends the file to your computer's standard (default) printer. This option is not available on some operating systems.
Enter SPOOL with no clauses to list the current spooling status.
Usage
To spool output generated by commands in a script without displaying the output on the screen, use SET TERMOUT OFF. SET TERMOUT OFF does not affect output from commands that run interactively.
You must use quotes around file names containing white space.
To create a valid HTML file using SPOOL APPEND commands, you must use PROMPT or a similar command to create the HTML page header and footer. The SPOOL APPEND command does not parse HTML tags.
Use SET SQLPLUSCOMPAT[IBILITY] 9.2 or earlier to use the earlier behavior. However, this will also disable other functionality that is available in SQL*Plus Release 10.1. See "SQL*Plus Compatibility Matrix" to determine what functionality is controlled by the SET SQLPLUSCOMPAT[IBILITY] command.
Examples of SPOOL Command
To record your output in the new file DIARY using the default file extension, enter
SPOOL DIARY CREATE
To append your output to the existing file DIARY, enter
SPOOL DIARY APPEND
To record your output to the file DIARY, overwriting the existing content, enter
SPOOL DIARY REPLACE
To stop spooling and print the file on your default printer, enter
SPOOL OUT
START
Syntax
STA[RT] {url | file_name[.ext] } [arg...]
Runs the SQL*Plus statements in the specified script. The script can be called from the local file system or from a web server. Only the url form is supported in iSQL*Plus. You can pass values to script variables in the usual way.
Terms
url
Specifies the Uniform Resource Locator of a script to run on the specified web server. SQL*Plus supports HTTP and FTP protocols, but not HTTPS. HTTP authentication in the form http://username:password@machine_name.domain... is not supported in this release.
file_name[.ext]
The script you wish to execute. The file can contain any command that you can run interactively.
If you do not specify an extension, SQL*Plus assumes the default command-file extension (normally SQL). See SET SUF[FIX] {SQL | text} for information on changing the default extension.
When you enter START file_name.ext, SQL*Plus searches for a file with the filename and extension you specify in the current default directory. If SQL*Plus does not find such a file, SQL*Plus will search a system-dependent path to find the file. Some operating systems may not support the path search. See the platform-specific Oracle documentation provided for your operating system for specific information related to your operating system environment.
arg ...
Data items you wish to pass to parameters in the script. If you enter one or more arguments, SQL*Plus substitutes the values into the parameters (&1, &2, and so forth) in the script. The first argument replaces each occurrence of &1, the second replaces each occurrence of &2, and so on.
The START command defines the parameters with the values of the arguments; if you START the script again in this session, you can enter new arguments or omit the arguments to use the old values.
See "Defining Substitution Variables" and "Substitution Variables in iSQL*Plus" for more information on using parameters.
Usage
All previous settings like COLUMN command settings stay in effect when the script starts. If the script changes any setting, then this new value stays in effect after the script has finished
The @ ("at" sign) and @@ (double "at" sign) commands function similarly to START. Disabling the START command in the Product User Profile also disables the @ and @@ commands. See @ ("at" sign)and @@ (double "at" sign) for further information on these commands. See "Disabling SQL*Plus, SQL, and PL/SQL Commands" for more information.
The EXIT or QUIT command in a script terminates SQL*Plus.
Examples
A file named PROMOTE with the extension SQL, used to promote employees, might contain the following command:
SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARYFROM EMP_DETAILS_VIEWWHERE JOB_ID='&1' AND SALARY>&2;
To run this script, enter
START PROMOTE ST_MAN 7000
or if it is located on a web server, enter a command in the form:
START HTTP:machine_name.domain:port/PROMOTE.SQL ST_MAN 7000
Where machine_name.domain must be replaced by the host.domain name, and port by the port number used by the web server where the script is located.
The following command is executed:
SELECT LAST_NAME, LAST_NAME
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='ST_MAN' AND SALARY>7000;
and the results displayed.
STARTUP
Syntax
STARTUP options | upgrade_options
where options has the following syntax:
[FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] |
[ OPEN [open_options] [dbname] ] | NOMOUNT ]
where open_options has the following syntax:
READ {ONLY | WRITE [RECOVER]} | RECOVER
and where upgrade_options has the following syntax:
[PFILE=filename] {UPGRADE | DOWNGRADE} [QUIET]
Starts an Oracle Database instance with several options, including mounting and opening a database.
Terms
FORCE
Shuts down the current Oracle Database instance (if it is running) with SHUTDOWN mode ABORT, before restarting it. If the current instance is running and FORCE is not specified, an error results. FORCE is useful while debugging and under abnormal circumstances. It should not normally be used.
RESTRICT
Only enables Oracle Database users with the RESTRICTED SESSION system privilege to connect to the database. Later, you can use the ALTER SYSTEM command to disable the restricted session feature.
PFILE=filename
Causes the specified parameter file to be used while starting up the instance. If PFILE is not specified, then the default STARTUP parameter file is used. The default file used is platform specific. For example, the default file is $ORACLE_HOME/dbs/init$ORACLE_SID.ora on UNIX, and %ORACLE_HOME%\database\initORCL.ora on Windows.
QUIET
Suppresses the display of System Global Area information for the starting instance.
MOUNT dbname
Mounts a database but does not open it.
dbname is the name of the database to mount or open. If no database name is specified, the database name is taken from the initialization parameter DB_NAME.
OPEN
Mounts and opens the specified database.
NOMOUNT
Causes the database not to be mounted upon instance startup.
Cannot be used with MOUNT, or OPEN.
RECOVER
Specifies that media recovery should be performed, if necessary, before starting the instance. STARTUP RECOVER has the same effect as issuing the RECOVER DATABASE command and starting an instance. Only complete recovery is possible with the RECOVER option.
Recovery proceeds, if necessary, as if AUTORECOVERY is set to ON, regardless of whether or not AUTORECOVERY is enabled. If a redo log file is not found in the expected location, recovery continues as if AUTORECOVERY is disabled, by prompting you with the suggested location and name of the subsequent log files that need to be applied.
UPGRADE
Starts the database in OPEN UPGRADE mode and sets system initialization parameters to specific values required to enable database upgrade scripts to be run. UPGRADE should only be used when a database is first started with a new version of the Oracle Database Server.
See the Oracle Database Upgrade Guide for details about preparing for, testing and implementing a database version upgrade.
When run, upgrade scripts transform an installed version or release of an Oracle database into a later version, for example, to upgrade an Oracle9i database to Oracle Database 10g. Once the upgrade completes, the database should be shut down and restarted normally.
DOWNGRADE
Starts the database in OPEN DOWNGRADE mode and sets system initialization parameters to specific values required to enable database downgrade scripts to be run.
See the Oracle Database Upgrade Guide for details about preparing for, testing and implementing a database version downgrade.
When run, downgrade scripts transform an installed version or release of Oracle Database into a previous version, for example, to downgrade an Oracle10g database to an Oracle9i database. Once the downgrade completes, the database should be shut down and restarted normally.
Usage
You must be connected to a database as SYSOPER, or SYSDBA. You cannot be connected through a multi-threaded server.
STARTUP with no arguments is equivalent to STARTUP OPEN.
STARTUP OPEN RECOVER mounts and opens the database even when recovery fails.
Examples
To start an instance using the standard parameter file, mount the default database, and open the database, enter
STARTUP
or enter
STARTUP OPEN database
To start an instance using the standard parameter file, mount the default database, and open the database, enter
STARTUP FORCE RESTRICT MOUNT
To start an instance using the parameter file TESTPARM without mounting the database, enter
STARTUP PFILE=testparm NOMOUNT
To shutdown a particular database, immediately restart and open it, allow access only to users with the RESTRICTED SESSION privilege, and use the parameter file MYINIT.ORA. enter
STARTUP FORCE RESTRICT PFILE=myinit.ora OPEN database
To startup an instance and mount but not open a database, enter
CONNECT / as SYSDBA
Connected to an idle instance.
STARTUP MOUNT
ORACLE instance started.
Total System Global Area 7629732 bytes
Fixed Size 60324 bytes
Variable Size 6627328 bytes
Database Buffers 409600 bytes
Redo Buffers 532480 bytes
STORE
STORE is not available in iSQL*Plus.
Syntax
STORE SET file_name[.ext] [ CRE[ATE | REP[LACE] | APP[END]]
Saves attributes of the current SQL*Plus environment in a script.
Terms
See SAVE for information on the other terms and clauses in the STORE command syntax.
SET
Saves the values of the system variables.
Usage
This command creates a script which can be executed with the START, @ ("at" sign) or @@ (double "at" sign) commands.
If you want to store a file under a name identical to a STORE command clause (that is, CREATE, REPLACE or APPEND), you must put the name in single quotes or specify a file extension.
Examples
To store the current SQL*Plus system variables in a file named DEFAULTENV with the default command-file extension, enter
STORE SET DEFAULTENV
To append the current SQL*Plus system variables to an existing file called DEFAULTENV with the extension OLD, enter
STORE SET DEFAULTENV.OLD APPEND
TIMING
Syntax
TIMI[NG] [START text | SHOW | STOP]
Records timing data for an elapsed period of time, lists the current timer's name and timing data, or lists the number of active timers.
Terms
START text
Sets up a timer and makes text the name of the timer. You can have more than one active timer by STARTing additional timers before STOPping the first; SQL*Plus nests each new timer within the preceding one. The timer most recently STARTed becomes the current timer.
SHOW
Lists the current timer's name and timing data.
STOP
Lists the current timer's name and timing data, then deletes the timer. If any other timers are active, the next most recently STARTed timer becomes the current timer.
Enter TIMING with no clauses to list the number of active timers. For other information about TIMING, see SET AUTOTRACE
Usage
You can use this data to do a performance analysis on any commands or blocks run during the period.
Refer to the SET TIMING command for information on automatically displaying timing data after each SQL command or PL/SQL block you run.
To delete all timers, use the CLEAR TIMING command.
Examples
To create a timer named SQL_TIMER, enter
TIMING START SQL_TIMER
To list the current timer's title and accumulated time, enter
TIMING SHOW
To list the current timer's title and accumulated time and to remove the timer, enter
TIMING STOP
TTITLE
Syntax
TTI[TLE] [printspec [text | variable] ...] [ON | OFF]
where printspec represents one or more of the following clauses used to place and format the text:
BOLD
CE[NTER]
COL n
FORMAT text
LE[FT]
R[IGHT]
S[KIP] [n]
TAB n
Places and formats a specified title at the top of each report page. Enter TTITLE with no clauses to list its current definition. The old form of TTITLE is used if only a single word or string in quotes follows the TTITLE command.
See TTI[TLE] text (obsolete old form) for a description of the old form of TTITLE.
Terms
These terms and clauses also apply to the BTITLE command.
text
The title text. Enter text in single quotes if you want to place more than one word on a single line.
variable
A substitution variable or any of the following system-maintained values, SQL.LNO (the current line number), SQL.PNO (the current page number), SQL.RELEASE (the current Oracle Database release number), SQL.SQLCODE (the current error code), or SQL.USER (the current username).
To print one of these values, reference the appropriate variable in the title. You can format variable with the FORMAT clause.
SQL*Plus substitution variables (& variables) are expanded before TTITLE is executed. The resulting string is stored as the TTITLE text. During subsequent execution for each page of results, the expanded value of a variable may itself be interpreted as a substitution variable with unexpected results.
You can avoid this double substitution in a TTITLE command by not using the & prefix for variables that are to be substituted on each page of results. If you want to use a substitution variable to insert unchanging text in a TTITLE, enclose it in quotes so that it is only substituted once.
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). Here "column" 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] | 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 bold 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 the COLUMN command 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 using 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.
Enter TTITLE with no clauses to list the current TTITLE definition.
Usage
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 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 them in the order you specify them, positioning and formatting each constant or variable as specified by the printspecclauses 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:
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
TTITLE LEFT 'Monthly Analysis' CENTER '01 Jan 2003' -
RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP CENTER -
'Data in Thousands'
Monthly Analysis 01 Jan 2003 Page: 1
Data in Thousands
To suppress the top title display without changing its definition, enter
TTITLE OFF
UNDEFINE
Syntax
UNDEF[INE] variable ...
where variable represents the name of the substitution variable you want to delete.
Deletes one or more substitution variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command).
Examples
To undefine a substitution variable named POS, enter
UNDEFINE POS
To undefine two substitution variables named MYVAR1 and MYVAR2, enter
UNDEFINE MYVAR1 MYVAR2
VARIABLE
Syntax
VAR[IABLE] [variable [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.
See "Using Bind Variables" for more information on bind variables. See your 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 Oracle9i 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 Oracle9i 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_DOUBLE
Creates a variable of type BINARY_DOUBLE.
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}" 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 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;
/
PL/SQL procedure successfully completed.
To display the value of the bind variable in SQL*Plus, enter:
PRINT ret_val
RET_VAL
4
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;
/
PL/SQL procedure successfully completed.
LAST_NAME CITY DEPARTMENT_ID
------------------------- ------------------------------ -------------
Hartstein Toronto 20
Russell Oxford 80
Partners Oxford 80
King Seattle 90
Kochhar Seattle 90
De Haan Seattle 90
6 rows selected.
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
variable id
datatype NUMBER
variable txt
datatype CHAR(20)
variable myvar
datatype REFCURSOR
The following example lists a single variable:
VARIABLE txt
variable txt
datatype CHAR(20)
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;
/
PL/SQL procedure successfully completed.
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
- Departmental Salary Bill ***
------------------------------ ------------------------- ------------
Executive De Haan $17,000.00
King $24,000.00
Kochhar $17,000.00
- ------------
Subtotal: $58,000.00
Marketing Hartstein $13,000.00
- ------------
Subtotal: $13,000.00
Sales Partners $13,500.00
Russell $14,000.00
- ------------
Subtotal: $27,500.00
Total: $98,500.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
VARIABLE T CLOB
BEGIN
SELECT CLOB_COL INTO :T FROM CLOB_TAB;
END;
/
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED
To print 200 characters from the column clob_col, enter
SET LINESIZE 70
SET LONG 200
PRINT T
T
Remember to run the Departmental Salary Bill report each month This r
eport contains confidential information.
To set the printing position to the 21st character, enter
SET LOBOFFSET 21
PRINT T
T
Departmental Salary Bill report each month This report contains confi
dential information.
For more information on creating CLOB columns, see your Oracle Database SQL Reference
WHENEVER OSERROR
Syntax
WHENEVER OSERROR {EXIT [SUCCESS | FAILURE | n | variable | :BindVariable]
[COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}
Performs the specified action (exits SQL*Plus by default) if an operating system error occurs (such as a file writing error).
In iSQL*Plus, performs the specified action (stops the current script by default) and returns focus to the Workspace if an operating system error occurs.
Terms
[SUCCESS | FAILURE | n | variable | :BindVariable]
Directs SQL*Plus to perform the specified action 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.
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 for more information.
CONTINUE
Turns off the EXIT option.
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
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.
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 script cause iSQL*Plus to stop processing the current script and return focus to the Input area on the Workspace:
cause SQL*Plus to exit and COMMIT any pending changes if a failure occurs when reading from the output file:
WHENEVER OSERROR EXIT
START no_such_file
OS Message: No such file or directory
Disconnected from Oracle......
WHENEVER SQLERROR
Syntax
WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}
Performs the specified action (exits SQL*Plus by default) if a SQL command or PL/SQL block generates an error.
In iSQL*Plus, performs the specified action (stops the current script by default) and returns focus to the Workspace if a SQL command or PL/SQL block generates an error.
Terms
[SUCCESS | FAILURE | WARNING | n | variable | :BindVariable]
Directs SQL*Plus to perform the specified action 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.
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 for more information.
CONTINUE
Turns off the EXIT option.
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
The WHENEVER SQLERROR command is triggered by SQL command or PL/SQL block errors, and not by SQL*Plus command errors.
Examples
The commands in the following script cause iSQL*Plus to stop processing the current script and return focus to the Input area on the Workspace:
The commands in the following script cause SQL*Plus to exit and return the SQL error code if the SQL UPDATE command fails:
WHENEVER SQLERROR EXIT SQL.SQLCODE
UPDATE EMP_DETAILS_VIEW SET SALARY = SALARY*1.1;
The following SQL command error causes iSQL*Plus to stop processing the current script and return focus to the Input area on the Workspace:
WHENEVER SQLERROR EXIT SQL.SQLCODE
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 examples show that the WHENEVER SQLERROR command is not executed after errors with SQL*Plus commands, but it is executed if SQL commands or PL/SQL blocks cause errors:
WHENEVER SQLERROR EXIT SQL.SQLCODE
column LAST_name headIing "Employee Name"
Unknown COLUMN option "headiing"
SHOW non_existed_option
The following PL/SQL block error causes SQL*Plus to exit and return the SQL error code:
WHENEVER SQLERROR EXIT SQL.SQLCODE
begin
SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
END;
/
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.....
No comments:
Post a Comment