| Help Contents |
iSQL*Plus Command Syntax |
Syntax
@{url[.ext] } [arg...]
Calls and runs the script specified by the URL from a web server.
Examples
You can run a script named YEAREND specified by a URL, and pass values to variables referenced in YEAREND in the usual way:
@HTTP://machine_name.domain:port/YEAREND.SQL VAL1 VAL2 @FTP://machine_name.domain:port/YEAREND.SQL VAL1 VAL2
On a web server configured to serve SQL reports, you could request SQL*Plus to execute a dynamic script with:
@HTTP://machine_name.domain:port/SCRIPTSERVER?ENDOFYEAR VAL1 VAL2
Syntax
@@{url | file_name[.ext] } [arg...]
Runs a script. This command is almost identical to the @ ("at" sign) command. When running nested scripts it looks for nested scripts in the same url as the calling script.
Examples
Suppose that you have the following script named PRINTRPT:
SELECT DEPARTMENT_ID, CITY FROM EMP_DETAILS_VIEW WHERE SALARY>12000; @EMPRPT.SQL @@ WKRPT.SQL
Suppose the script PRINTRPT was located on a web server and you ran it with START HTTP://machine_name.domain:port/PRINTRPT. When it reaches the @ command, it looks for the script named EMPRPT in the current working directory and runs it. When PRINTRPT reaches the @@ command, it looks for the script named WKRPT in the same url as PRINTRPT, HTTP://machine_name.domain:port/WKRPT.SQL and runs it.
Syntax
/(slash)
Executes the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer.
Examples
Type the following SQL script:
SELECT CITY, COUNTRY_NAME FROM EMP_DETAILS_VIEW WHERE SALARY=12000;
Enter a slash (/) to re-execute the command in the buffer:
/
CITY COUNTRY_NAME ------------------------------ ---------------------------------------- Seattle United States of America Oxford United Kingdom Seattle United States of America |
Syntax
ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text|NOPR[OMPT]] [HIDE]
Reads a line of input and stores it in a given substitution variable.
Examples
To display the prompt "Password: ", place the reply in a CHAR variable named PSWD, and suppress the display, enter
ACCEPT pswd CHAR PROMPT 'Password: ' HIDE
To display the prompt "Enter weekly salary: " and place the reply in a NUMBER variable named SALARY with a default of 000.0, enter
ACCEPT salary NUMBER FORMAT '999.99' DEFAULT '000.0' - PROMPT 'Enter weekly salary: '
To display the prompt "Enter date hired: " and place the reply in a DATE variable, HIRED, with the format "dd/mm/yyyy" and a default of "01/01/2003", enter
ACCEPT hired DATE FORMAT 'dd/mm/yyyy' DEFAULT '01/01/2003'- PROMPT 'Enter date hired: '
To display the prompt "Enter employee lastname: " and place the reply in a CHAR variable named LASTNAME, enter
ACCEPT lastname CHAR FORMAT 'A20' - PROMPT 'Enter employee lastname: '
Syntax
ARCHIVE LOG {LIST | STOP} | {START | NEXT | ALL | integer } [TO destination]
Starts or stops automatic archiving of online redo log files, manually (explicitly) archives specified redo log files, or displays information about redo log files.
Examples
To start up the archiver process and begin automatic archiving, using the archive destination specified in LOG_ARCHIVE_DEST, enter
ARCHIVE LOG START
To stop automatic archiving, enter
ARCHIVE LOG STOP
To archive the log file group with sequence number 1001 to the destination specified, enter
ARCHIVE LOG 1001 '/vobs/oracle/dbs/arch'
'arch' specifies the prefix of the filename on the destination device; the remainder of the filename is dependent on the initialization parameter LOG_ARCHIVE_FORMAT, which specifies the filename format for archived redo log files.
Syntax
ATTRIBUTE [type_name.attribute_name [option ...]]
where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
FOR[MAT] format
LIKE {type_name.attribute_name | alias}
ON | OFF
Specifies display characteristics for a given attribute of an Object Type column, such as the format of NUMBER data. Columns and attributes should not have the same names as they share a common namespace.
Also lists the current display characteristics for a single attribute or all attributes.
Examples
To make the LAST_NAME attribute of the Object Type EMPLOYEE_TYPE 20 characters wide, enter
ATTRIBUTE EMPLOYEE_TYPE.LAST_NAME FORMAT A20
To format the SALARY attribute of the Object Type EMPLOYEE_TYPE so that it shows millions of dollars, rounds to cents, uses commas to separate thousands, and displays $0.00 when a value is zero, enter
ATTRIBUTE EMPLOYEE_TYPE.SALARY FORMAT $9,999,990.99
Syntax
BRE[AK] [ON report_element [action [action]]] ...
where report_element has the syntax {column|expr|ROW|REPORT}
and action has the syntax [SKI[P] n|[SKI[P]] PAGE] [NODUP[LICATES]|DUP[LICATES]]
Specifies where changes occur in a report and the formatting action to perform, such as:
suppressing display of duplicate values for a given column
skipping a line each time a given column value changes (In iSQL*Plus, only when Preformatted Output is ON)
printing computed figures each time a given column value changes or at the end of the report.
Enter BREAK with no clauses to list the current BREAK definition.
Examples
To produce a report that prints duplicate job values, prints the average of SALARY, and additionally prints the sum of SALARY, you could enter the following commands. (The example selects departments 50 and 80 and the jobs of clerk and salesman only.)
BREAK ON DEPARTMENT_ID ON JOB_ID DUPLICATES
COMPUTE SUM OF SALARY ON DEPARTMENT_ID
COMPUTE AVG OF SALARY ON JOB_ID
SELECT DEPARTMENT_ID, JOB_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID IN ('SH_CLERK', 'SA_MAN')
AND DEPARTMENT_ID IN (50, 80)
ORDER BY DEPARTMENT_ID, JOB_ID;
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
50 SH_CLERK Taylor 3200
SH_CLERK Fleaur 3100
.
.
.
SH_CLERK Gates 2900
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
50 SH_CLERK Perkins 2500
SH_CLERK Bell 4000
.
.
.
SH_CLERK Grant 2600
********** ----------
avg 3215
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
************* ----------
sum 64300
80 SA_MAN Russell 14000
SA_MAN Partners 13500
SA_MAN Errazuriz 12000
SA_MAN Cambrault 11000
SA_MAN Zlotkey 10500
********** ----------
avg 12200
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
************* ----------
sum 61000
25 rows selected.
|
Syntax
BTI[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 bottom of each report page, or lists the current BTITLE definition.
Examples
To set a bottom title with CORPORATE PLANNING DEPARTMENT on the left and a date on the right, enter
BTITLE LEFT 'CORPORATE PLANNING DEPARTMENT' - RIGHT '1 JAN 2001'
To set a bottom title with CONFIDENTIAL in column 50, followed by
six spaces and
a date, enter
BTITLE COL 50 'CONFIDENTIAL' TAB 6 '1 JAN 2001'
Syntax
CL[EAR] option ...
where option represents one of the following clauses:
BRE[AKS]
BUFF[ER]
COL[UMNS]
COMP[UTES]
SQL
TIMI[NG]
Resets or erases the current value or setting for the specified option.
Examples
To clear breaks, enter
CLEAR BREAKS
To clear column definitions, enter
CLEAR COLUMNS
Syntax
COL[UMN] [{column | expr} [option ...]]
where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
ENTMAP {ON | OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
LIKE {expr | alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT] | PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON | OFF
WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]
Specifies display attributes for a given column, such as
text for the column heading
format for NUMBER data
wrapping of column data
Also lists the current display attributes for a single column or all columns.
Number Formats
Table 5-1 shows the formats you can use in the COLUMN FORMAT clause, and in SET NUMFORMAT.
Number Formats
| Element | Examples | Description |
|---|---|---|
| , (comma) |
9,999 |
Displays a comma in the specified position. |
| . (period) |
99.99 |
Displays a period (decimal point) to separate the integral and fractional parts of a number. |
| $
|
$9999 |
Displays a leading dollar sign. |
| 0 |
0999 9990 |
Displays leading zeros Displays trailing zeros. |
| 9 |
9999 |
Displays a value with the number of digits specified by the number of 9s. Value has a leading space if positive, a leading minus sign if negative. Blanks are displayed for leading zeroes. A zero (0) is displayed for a value of zero. |
| B |
B9999 |
Displays blanks for the integer part of a fixed-point number when the integer part is zero, regardless of zeros in the format model. |
| C |
C999 |
Displays the ISO currency symbol in the specified position. |
| D |
99D99 |
Displays the decimal character to separate the integral and fractional parts of a number. |
| EEEE |
9.999EEEE |
Displays value in scientific notation (format must contain exactly four "E"s). |
| G |
9G999 |
Displays the group separator in the specified positions in the integral part of a number. |
| L |
L999 |
Displays the local currency symbol in the specified position. |
| MI |
9999MI |
Displays a trailing minus sign after a negative value. Display a trailing space after a positive value. |
| PR |
9999PR |
Displays a negative value in <angle brackets>. Displays a positive value with a leading and trailing space. |
| RN rn |
RN rn |
Displays uppercase Roman numerals. Displays lowercase Roman numerals. Value can be an integer between 1 and 3999. |
| S |
S9999 9999S |
Displays a leading minus or plus sign. Displays a trailing minus or plus sign. |
| TM |
TM |
Displays the smallest number of decimal characters possible. The default is TM9. Fixed notation is used for output up to 64 characters, scientific notation for more than 64 characters. Cannot precede TM with any other element. TM can only be followed by a single 9 or E |
| U |
U9999 |
Displays the dual currency symbol in the specified position. |
| V |
999V99 |
Displays value multiplied by 10n, where n is the number of 9's after the V. |
| X |
XXXX xxxx |
Displays the hexadecimal value for the rounded value of the specified number of digits. |
Examples
To make the LAST_NAME column 20 characters wide and display EMPLOYEE NAME on two lines as the column heading, enter
COLUMN LAST_NAME FORMAT A20 HEADING 'EMPLOYEE|NAME'
To format the SALARY column so that it shows millions of dollars, rounds to cents, uses commas to separate thousands, and displays $0.00 when a value is zero, enter
COLUMN SALARY FORMAT $9,999,990.99
To assign the alias NET to a column containing a long expression, to display the result in a dollar format, and to display <NULL> for null values, you might enter
COLUMN SALARY+COMMISSION_PCT+BONUS-EXPENSES-INS-TAX ALIAS NET COLUMN NET FORMAT $9,999,999.99 NULL '<NULL>'
Note that the example divides this column specification into two commands. The first defines the alias NET, and the second uses NET to define the format.
Also note that in the first command you must enter the expression exactly as you enter it in the SELECT command. Otherwise, SQL*Plus cannot match the COLUMN command to the appropriate column.
To wrap long values in a column named REMARKS, you can enter
COLUMN REMARKS FORMAT A20 WRAP
CUSTOMER DATE QUANTITY REMARKS
---------- --------- -------- --------------------
123 25-AUG-2001 144 This order must be s
hipped by air freigh
t to ORD
|
To print the current date and the name of each job in the top title, enter the following. Use the EMPLOYEES table of the HR schema instead of EMP_DETAILS_VIEW.
COLUMN JOB_ID NOPRINT NEW_VALUE JOBVAR
COLUMN TODAY NOPRINT NEW_VALUE DATEVAR
BREAK ON JOB_ID SKIP PAGE ON TODAY
TTITLE CENTER 'Job Report' RIGHT DATEVAR SKIP 2 -
LEFT 'Job: ' JOBVAR SKIP 2
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') TODAY,
LAST_NAME, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, DEPARTMENT_ID
FROM EMPLOYEES WHERE JOB_ID IN ('MK_MAN', 'SA_MAN')
ORDER BY JOB_ID, LAST_NAME;
|
To change the default format of DATE columns to 'YYYY-MM-DD', you can enter
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
Session altered. |
See the Oracle Database SQL Reference for information on the ALTER SESSION command.
Syntax
COMP[UTE] [function [LAB[EL] text] ... OF {expr | column | alias} ... ON {expr | column | alias | REPORT | ROW} ...]
In combination with the BREAK command, calculates and prints summary lines, using various standard computations on subsets of selected rows. It also lists all COMPUTE definitions.
Examples
To subtotal the salary for the "account manager", AC_MGR, and "salesman", SA_MAN, job classifications with a compute label of "TOTAL", enter
BREAK ON JOB_ID SKIP 1;
COMPUTE SUM LABEL 'TOTAL' OF SALARY ON JOB_ID;
SELECT JOB_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID IN ('AC_MGR', 'SA_MAN')
ORDER BY JOB_ID, SALARY;
JOB_ID LAST_NAME SALARY
---------- ------------------------- ----------
AC_MGR Higgins 12000
********** ----------
TOTAL 12000
SA_MAN Zlotkey 10500
Cambrault 11000
Errazuriz 12000
Partners 13500
Russell 14000
********** ----------
TOTAL 61000
6 rows selected.
|
To calculate the average and maximum salary for the executive and accounting departments, enter
BREAK ON DEPARTMENT_NAME SKIP 1
COMPUTE AVG LABEL 'Dept Average' -
MAX LABEL 'Dept Maximum' -
OF SALARY ON DEPARTMENT_NAME
SELECT DEPARTMENT_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_NAME IN ('Executive', 'Accounting')
ORDER BY DEPARTMENT_NAME;
DEPARTMENT_NAME LAST_NAME SALARY
------------------------------ ------------------------- ----------
Accounting Higgins 12000
Gietz 8300
****************************** ----------
Dept Average 10150
Dept Maximum 12000
Executive King 24000
Kochhar 17000
De Haan 17000
****************************** ----------
Dept Average 19333.3333
Dept Maximum 24000
|
To sum salaries for departments <= 20 without printing the compute label, enter
COLUMN DUMMY NOPRINT COMPUTE SUM OF SALARY ON DUMMY BREAK ON DUMMY SKIP 1 SELECT DEPARTMENT_ID DUMMY, DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE DEPARTMENT_ID <= 20 ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
10 Whalen 4400
----------
4400
20 Hartstein 13000
20 Fay 6000
----------
19000
|
Syntax
CONN[ECT] [{ logon | / } [AS {SYSOPER | SYSDBA}]]
where logon has the syntax username[/password] [@connect_identifier]
Connects a given username to the Oracle Database. When you run a CONNECT command, the site profile, glogin.sql, is executed.
CONNECT does not reprompt for username or password if the initial connection does not succeed.
|
Warning: Including your password in plain text is a security risk. You can avoid this risk by omitting the password, and entering it only when the system prompts for it. |
Examples
To connect across Oracle Net with username HR, to the database known by the Oracle Net alias as FLEETDB, enter
CONNECT HR@FLEETDB
For more information about setting up your password file, refer to the Oracle Database Administrator's Guide.
Syntax
DEF[INE] [variable] | [variable = text]
Specifies a user or predefined variable and assigns a CHAR value to it, or lists the value and variable type of a single variable or all variables.
Examples
To assign the value MANAGER to the variable POS, type:
DEFINE POS = MANAGER
If you execute a command containing a reference to &POS, SQL*Plus substitutes the value MANAGER for &POS and will not prompt you for a POS value.
To assign the CHAR value 20 to the variable DEPARTMENT_ID, type:
DEFINE DEPARTMENT_ID = 20
Even though you enter the number 20, SQL*Plus assigns a CHAR value to DEPARTMENT_ID consisting of two characters, 2 and 0.
To list the definition of DEPARTMENT_ID, enter
DEFINE DEPARTMENT_ID
DEFINE DEPARTMENT_ID = "20" (CHAR) |
This result shows that the value of DEPARTMENT_ID is 20.
Syntax
DESC[RIBE] {[schema.]object[@connect_identifier]}
Lists the column definitions for the specified table, view or synonym, or the specifications for the specified function or procedure.
Examples
To describe the view EMP_DETAILS_VIEW, enter
DESCRIBE EMP_DETAILS_VIEW
Name Null? Type ----------------------------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) JOB_ID NOT NULL VARCHAR2(10) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) LOCATION_ID NUMBER(4) COUNTRY_ID CHAR(2) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) DEPARTMENT_NAME NOT NULL VARCHAR2(30) JOB_TITLE NOT NULL VARCHAR2(35) CITY NOT NULL VARCHAR2(30) STATE_PROVINCE VARCHAR2(25) COUNTRY_NAME VARCHAR2(40) REGION_NAME VARCHAR2(25) |
To describe a procedure called CUSTOMER_LOOKUP, enter
DESCRIBE customer_lookup
PROCEDURE customer_lookup Argument Name Type In/Out Default? ---------------------- -------- -------- --------- CUST_ID NUMBER IN CUST_NAME VARCHAR2 OUT |
To create and describe the package APACK that contains the procedures aproc and bproc, enter
CREATE PACKAGE apack AS PROCEDURE aproc(P1 CHAR, P2 NUMBER); PROCEDURE bproc(P1 CHAR, P2 NUMBER); END apack; /
Package created. |
DESCRIBE apack
PROCEDURE APROC Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P1 CHAR IN P2 NUMBER IN PROCEDURE BPROC Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P1 CHAR IN P2 NUMBER IN |
For information about using the SET DESCRIBE and SHOW DESCRIBE commands, see the SET and SHOW commands.
Syntax
DISC[ONNECT]
Commits pending changes to the database and logs the current username out of Oracle Database, but does not exit iSQL*Plus.
Examples
Your script might begin with a CONNECT command and end with a DISCONNECT, as shown later.
CONNECT HR SELECT LAST_NAME, DEPARTMENT_NAME FROM EMP_DETAILS_VIEW; DISCONNECT SET INSTANCE FIN2 CONNECT HR2
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 your PL/SQL User's Guide and Reference.
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.
Syntax
{EXIT | QUIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]
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. In iSQL*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.
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
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://www.oracle.com/technology/tech/sql_plus/ and the Oracle Database Library at http://www.oracle.com/technology/documentation/.
In iSQL*Plus, click the Help icon to access the iSQL*Plus Online Help.
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
Syntax
L[IST] [n | n m | n * | n LAST | * | * n | * LAST | LAST]
Lists one or more lines of the SQL buffer.
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 |
Syntax
PAU[SE] [text]
where text represents the text you wish to display.
Displays the Next Page button which the user must click to continue.
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.
Examples
The following example illustrates a PRINT command:
VARIABLE n NUMBER BEGIN :n := 1; END; /
PL/SQL procedure successfully completed. |
PRINT n
N ---------- 1 |
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.
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:
PROMPT PROMPT Please enter a valid department PROMPT For example: 10 SELECT DEPARTMENT_NAME FROM EMP_DETAILS_VIEW WHERE 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.
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.
You must set AUTORECOVERY to ON to use the RECOVER command in iSQL*Plus.
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.
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'
Syntax
REM[ARK]
Begins a comment in a script. iSQL*Plus does not interpret the comment as a command.
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;
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.
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
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.
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
Syntax
R[UN]
Lists and executes the SQL command or PL/SQL block currently stored in the SQL buffer.
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.
|
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 Command Syntax" table following.
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.
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
Sets the number of rows that SQL*Plus will fetch from the database at one time.
Controls when Oracle Database commits pending changes to the database after SQL or PL/SQL commands.
SET AUTOCOMMIT does not alter the commit behavior when SQL*Plus exits. Any uncommitted data is committed by default.
Sets the automatic printing of bind variables.
ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.
You must set AUTORECOVERY to ON to use the RECOVER command in iSQL*Plus.
Example
To set the recovery mode to AUTOMATIC, enter
SET AUTORECOVERY ON RECOVER DATABASE
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE).
Sets the character used to end PL/SQL blocks to c.
Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.
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. |
Sets the column separator character printed between columns in output.
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 |
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.
Controls the number of rows after which the COPY command commits changes to the database.
Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.
Sets the character used to prefix substitution variables to c.
Sets the depth of the level to which you can recursively describe an object.
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)
|
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 iSQL*Plus from the operating system.
Controls where on a page each report begins.
Defines the character used as the escape character.
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
Displays the number of records returned by a script when a script selects at least n records.
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
Controls printing of column headings in reports.
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
Defines the character used as a line break in column headings.
The Heading Separator character (SET HEADSEP) is only supported in iSQL*Plus when the Preformatted Output preference is ON (SET MARKUP HTML PREFORMAT).
Changes the default instance for your session to the specified instance path.
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.
Sets the total number of characters that iSQL*Plus displays on one line before beginning a new line.
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.
Specifies the location from which archive logs are retrieved during recovery.
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
Sets maximum width (in bytes) for displaying CLOB, LONG, NCLOB and XMLType values; and for copying LONG values.
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.
Sets the size (in bytes) of the increments SQL*Plus uses to retrieve a CLOB, LONG, NCLOB or XMLType value.
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.
Outputs HTML marked up text, which is the output used by iSQL*Plus.
Sets the text displayed whenever a null value occurs in the result of a SQL SELECT command.
Sets the default format for displaying numbers. Enter a number format for format. See Number Formats in the COLUMN command for details of format syntax. Enter
SET NUMFORMAT ""
to use the default field width and formatting model specified by SET NUMWIDTH.
Sets the default width for displaying numbers. See Number Formats in the COLUMN command for details of format syntax.
COLUMN FORMAT settings take precedence over SET NUMFORMAT settings, which take precedence over SET NUMWIDTH settings.
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 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.
RECSEP tells SQL*Plus where to make the record separation.
The Display Record Separator preference (SET RECSEP) is only supported in iSQL*Plus when Preformatted Output is On (SET MARKUP HTML PREFORMAT).
Defines the character to display or print to separate records.
Controls whether to display output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
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. |
Converts the case of SQL commands and PL/SQL blocks just prior to execution.
Sets the behavior to that of the release or version specified by x.y[.z].
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.2.
You can use the SQLPLUS -C[OMPATIBILITY] {x.y[.z]} command argument at startup to set the compatibility mode of that session.
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
Compatibility Matrix
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.
Controls the display of timing statistics.
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.
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.
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.
The Wrap Lines preference (SET WRAP) is only supported in iSQL*Plus when Preformatted Output is On (SET MARKUP HTML PREFORMAT).
Specifies the base URI used to resolve relative URIs in functions. It enables the prefix of the file accessed by an XQuery to be changed.
Take care to enter valid values as no system validation is performed on the XQUERY BASEURI value.
Example
SET XQUERY BASEURI '/public/scott'
xquery for $i in doc("foo.xml") return $i
/
This is evaluated as:
select column_Value from xmltable('declare base-uri "/public/scott"; for $i in doc("foo.xml") return $i ') ;
Sets the ordering of output from an XQuery. There are three values:
UNORDERED specifies that results are sorted in the order they are retrieved from the database.
ORDERED specifies that results are sorted as defined by the XQuery.
DEFAULT specifies the database default which is UNORDERED.
Example
SET XQUERY ORDERING ORDERED
xquery for $i in doc("foo.xml") return $i
/
This is evaluated as:
select column_value from xmltable('declare ordering ordered; for $i in doc("foo.xml") return $i ');
Sets the node identity preservation mode. The preservation mode applies to all expressions that either create a new node (such as element constructors) or return an item or sequence containing nodes (such as path expressions). There are three values:
BYVALUE specifies that the node identity need not be preserved. This means that any node operation such as creation or that is returned as a result of an expression is deep copied and loses it's context from the original tree from which it came. So subsequent operations on this node that test for node identity, parent or sibling axes or ordering will be undefined.
BYREFERENCE specifies that node identities are to be preserved so subsequent operations on this node preserve the node's context and definition.
DEFAULT specifies the database default which is BYVALUE.
Example
SET XQUERY NODE BYREFERENCE
xquery for $i in doc("foo.xml") return $i
/
This is evaluated as:
select column_value from xmltable('declare node byreference; for $i in doc("foo.xml") return $i ');
Specifies an XQuery context item expression. A context item expression evaluates to the context item, which may be either a node (as in the expression fn:doc("bib.xml")//book[fn:count(./author)>1]) or an atomic value (as in the expression (1 to 100)[. mod 5 eq 0]).
Take care to enter valid values as no system validation is performed on the XQUERY CONTEXT value.
Example
SET XQUERY CONTEXT 'doc("foo.xml")'
xquery for $i in /a return $i
/
This is evaluated as,
select column_value from xmltable('for $i in /a return $i' passing XMLQuery("doc('foo.xml')"));
Syntax
SHO[W] option
where option represents one of the following terms or clauses:
system_variableALL
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
SQLCODE
TTI[TLE]
USER
XQUERY
Shows the value of a SQL*Plus system variable or the current SQL*Plus environment. SHOW SGA requires a DBA privileged login.
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 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;
Syntax
SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]]
Shuts down a currently running Oracle Database instance, optionally closing and dismounting a database.
Examples
To shutdown the database in normal mode, enter
SHUTDOWN
Database closed. Database dismounted. Oracle instance shut down. |
Syntax
STA[RT] {url[.ext] } [arg...]
Calls and runs the script specified by the URL from a web server.
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.
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.
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
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.
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
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.
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
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
Syntax
VAR[IABLE] [variable [type] ]
where type represents one of the following:
NUMBER
CHAR
CHAR (n [CHAR | BYTE])
NCHAR
NCHAR (n)
VARCHAR2 (n [CHAR | BYTE])
NVARCHAR2 (n)
CLOB
NCLOB
REFCURSOR
BINARY_FLOAT
BINARY_DOUBLE
Declares a bind variable that can be referenced in PL/SQL.
To free resources used by CLOB and NCLOB bind variables, you may need to manually free temporary LOBs with:
EXECUTE DBMS_LOB.FREETEMPORARY(:cv)
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.
Syntax
WHENEVER OSERROR {EXIT [SUCCESS | FAILURE | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}
Performs the specified action (stops the current script by default) and returns focus to the Workspace if an operating system 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:
WHENEVER OSERROR EXIT START no_such_file
Syntax
WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}
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.
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 if the SQL UPDATE command fails:
WHENEVER SQLERROR EXIT SQL.SQLCODE UPDATE EMP_DETAILS_VIEW SET SALARY = SALARY*1.1;
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 |
Syntax
XQUERY xquery_statement
The SQL*Plus XQUERY command enables you to perform an XQuery 1.0 query on a specified database. XQUERY is supported on Oracle Database 10g (Release 2) and later versions. Attempting to use XQUERY on an earlier version of the Oracle Database gives the error:
SP2-614 Server version too low
Also see the SET XQUERY commands:
Examples
The XQuery statement in the following script queries the EMP_DETAILS_VIEW view of the HR schema:
set long 160
set linesize 160
xquery for $i in ora:view("EMP_DETAILS_VIEW") return $i
/
Result Sequence ------------------------------------------------------------------------------------------- <ROW><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><HIREDATE>17- DEC-80</HIREDATE><SAL>800</SAL><DEPTNO>20</DEPTNO></ROW> 14 item(s) selected. |