13
SQL*Plus Command Reference

This chapter contains descriptions of the SQL*Plus commands available in command-line and iSQL*Plus interfaces listed alphabetically. Each description contains the following parts:

A summary table that lists and briefly describes SQL*Plus commands precedes the individual command descriptions.

You can continue a long SQL*Plus command by typing a hyphen at the end of the line and pressing [Return]. If you wish, you can type a space before typing the hyphen. SQL*Plus displays a right angle-bracket (>) as a prompt for each additional line.

You do not need to end a SQL*Plus command with a semicolon. When you finish entering the command, you can just press [Return]. If you wish, however, you can enter a semicolon at the end of a SQL*Plus command.

SQL*Plus Command Summary

@ ("at" sign)

@{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.

Terms

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

url

file_name[.ext]

arg...

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

In a script, you can include any command you would normally enter interactively (typically, SQL, SQL*Plus commands, or PL/SQL blocks).

An EXIT or QUIT command used in a script terminates SQL*Plus.

The @ command functions similarly to START.

If the START command is disabled (see "Disabling SQL*Plus, SQL, and PL/SQL Commands"), this will also disable the @ command. See START for information on the START command.

SQL*Plus removes the SQLTERMINATOR (a semicolon by default) before the @ command is issued. If you require a semicolon in your command, add a second SQLTERMINATOR. See the SQLTERMINATOR variable of the SET command in this chapter for more information.

Examples

To run a script named PRINTRPT with the extension SQL, enter

@PRINTRPT

To run a script named WKRPT with the extension QRY, enter

@WKRPT.QRY

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 by using:

@HTTP://machine_name.domain:port/SCRIPTSERVER?ENDOFYEAR VAL1 VAL2

@@ (double "at" sign)

@@url|file_name[.ext]

Runs a script. This command is almost identical to the @ ("at" sign) command. It is useful for running nested scripts because it has the additional functionality of looking for the specified script in the same path or url as the script from which it was called. Only the url form is supported in iSQL*Plus.

Terms

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

url

file_name[.ext]

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

You can include in a script any command you would normally enter interactively (typically, SQL or SQL*Plus commands).

An EXIT or QUIT command used in a script terminates SQL*Plus.

The @@ command functions similarly to START.

If the START command is disabled (see "Disabling SQL*Plus, SQL, and PL/SQL Commands"), this will also disable the @@ command. For more information, see the SPOOL command.

SQL*Plus removes the SQLTERMINATOR (a semicolon by default) before the @@ command is issued. A workaround for this is to add another SQLTERMINATOR. See the SQLTERMINATOR variable of the SET command for more information.

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

When you START PRINTRPT and 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 path as PRINTRPT and runs it.

Suppose that the same 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 local 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.

/ (slash)

/(slash)

Executes the SQL command or PL/SQL block currently stored in the SQL buffer.

Usage

You can enter a slash (/) at the command prompt or at a line number prompt of a multi-line command.

The slash command functions similarly to RUN, but does not list the command in the buffer on your screen.

Executing a SQL command or PL/SQL block using the slash command will not cause the current line number in the SQL buffer to change unless the command in the buffer contains an error. In that case, SQL*Plus changes the current line number to the number of the line containing the error.

Examples

Type the following SQL script:

SELECT CITY, COUNTRY_NAME
FROM EMP_DETAILS_VIEW
WHERE SALARY=12000;

Enter a slash (/) at the command prompt to re-execute the command in the buffer:

/



CITY                           COUNTRY_NAME
------------------------------ ----------------------------------------
Seattle                        United States of America
Oxford                         United Kingdom
Seattle                        United States of America

ACCEPT

ACCEPT is not available in iSQL*Plus.

ACC[EPT] variable [NUM[BER]|CHAR|DATE] [FOR[MAT] format] [DEF[AULT] default]
[PROMPT text|NOPR[OMPT]] [HIDE]

Reads a line of input and stores it in a given user variable.

Terms

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

variable

NUM[BER]

CHAR

DATE

FOR[MAT]

DEF[AULT]

PROMPT text

NOPR[OMPT]

HIDE

To display or reference variables, use the DEFINE command. See the DEFINE command in this chapter for more information.

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 named HIRED with the format "dd/mm/yyyy" and a default of "01/01/2001", enter

ACCEPT hired DATE FORMAT 'dd/mm/yyyy' DEFAULT '01/01/2001'-
PROMPT 'Enter date hired:  '

APPEND

APPEND is not available in iSQL*Plus.

A[PPEND] text

Adds specified text to the end of the current line in the SQL buffer.

Terms

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

text

Examples

To append a comma delimiter, a space and the column name CITY to the first line of the buffer, make that line the current line by listing the line as follows:

1



1* SELECT DEPARTMENT_ID

Now enter APPEND:

APPEND , CITY
1



1* SELECT DEPARTMENT_ID, CITY

To append a semicolon to the line, enter

APPEND ;;

SQL*Plus appends the first semicolon to the line and interprets the second as the terminator for the APPEND command.

ARCHIVE LOG

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.

Terms

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

LIST

ARCHIVE LOG LIST

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /vobs/oracle/dbs/arch
Oldest online log sequence     221
Next log sequence to archive   222
Current log sequence           222

Oldest online log sequence            222
Next log sequence to archive          222
Current log sequence                  225

STOP

START

NEXT

ALL

integer

destination

Usage

You must be connected to an open Oracle database as SYSOPER, or SYSDBA. For information about connecting to the database, see the CONNECT command in this chapter.

If an online redo log file group fills and none are available for reuse, database operation is suspended. The condition can be resolved by archiving a log file group.

For information about specifying archive destinations, see your platform-specific Oracle documentation.

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.

ATTRIBUTE

ATTRIBUTE [type_name.attribute_name [option ...]]

where option represents one of the following clauses:

Specifies display characteristics for a given attribute of an Object Type column, such as format for NUMBER data.

Also lists the current display characteristics for a single attribute or all attributes.

Terms

Enter ATTRIBUTE followed by type_name.attribute_name and no other clauses to list the current display characteristics for only the specified attribute. Enter ATTRIBUTE with no clauses to list all current attribute display characteristics.

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

type_name.attribute_name

ALI[AS] alias

CLE[AR]

FOR[MAT] format

LIKE {type_name.attribute_name|alias}

ON|OFF

Usage

You can enter any number of ATTRIBUTE commands for one or more attributes. All attribute characteristics set for each attribute remain in effect for the remainder of the session, until you turn the attribute OFF, or until you use the CLEAR COLUMN command. Thus, the ATTRIBUTE commands you enter can control an attribute's display characteristics for multiple SQL SELECT commands.

When you enter multiple ATTRIBUTE commands for the same attribute, SQL*Plus applies their clauses collectively. If several ATTRIBUTE commands apply the same clause to the same attribute, the last one entered will control the output.

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

BREAK

BRE[AK] [ON report_element [action [action]]] ...

where:

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
• printing computed figures each time a given column value changes or at the end of the report (see also the COMPUTE command)

Enter BREAK with no clauses to list the current BREAK definition.

Terms

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

ON column [action [action]]

BREAK ON DEPARTMENT_ID SKIP PAGE ON JOB_ID - 
SKIP 1 ON SALARY SKIP 1

SELECT DEPARTMENT_ID, JOB_ID, SALARY, LAST_NAME
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY DEPARTMENT_ID, JOB_ID, SALARY, LAST_NAME;

ON expr [action [action]]

ON ROW [action [action]]

ON REPORT [action]

Refer to the following list for a description of each action:

SKI[P] n

[SKI[P]] PAGE

NODUP[LICATES]

DUP[LICATES]

Enter BREAK with no clauses to list the current break definition.

Usage

Each new BREAK command you enter replaces the preceding one.

To remove the BREAK command, use CLEAR BREAKS.

Examples

To produce a report that prints duplicate job values, prints the average of SALARY and inserts one blank line when the value of JOB_ID changes, and additionally prints the sum of SALARY and inserts another blank line when the value of DEPARTMENT_ID changes, 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 SKIP 1 ON JOB_ID SKIP 1 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.

BTITLE

BTI[TLE] [printspec [text|variable] ...] [ON|OFF]

where printspec represents one or more of the following clauses used to place and format the text:

Places and formats a specified title at the bottom of each report page, or lists the current BTITLE definition.

For a description of the old form of BTITLE, see Appendix C, "Obsolete SQL*Plus Commands".

Terms

Refer to the TTITLE command in this chapter for information on terms and clauses in the BTITLE command syntax.

Enter BTITLE with no clauses to list the current BTITLE definition.

Usage

If you do not enter a printspec clause before the first occurrence of text, BTITLE left justifies the text. SQL*Plus interprets BTITLE in the new form if a valid printspec clause (LEFT, SKIP, COL, and so on) immediately follows the command name.

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'

CHANGE

CHANGE is not available in iSQL*Plus.

C[HANGE] sepchar old [sepchar [new [sepchar]]]

Changes the first occurrence of the specified text on the current line in the buffer.

Terms

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

sepchar

old

CHANGE /aq/aw

new

Usage

CHANGE changes the first occurrence of the existing specified text on the current line of the buffer to the new specified text. The current line is marked with an asterisk (*) in the LIST output.

You can also use CHANGE to modify a line in the buffer that has generated an Oracle error. SQL*Plus sets the buffer's current line to the line containing the error so that you can make modifications.

To reenter an entire line, you can type the line number followed by the new contents of the line. If you specify a line number 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, SQL*Plus inserts the line at the beginning of the buffer (that line becomes line 1).

Examples

Enter 3 so the current line of the buffer contains the following text:

3



  3* WHERE JOB_ID IS IN ('CLERK', 'SA_MAN')

Enter the following command:

CHANGE /CLERK/SH_CLERK/

The text in the buffer changes as follows:

  3*  WHERE JOB_ID IN ('SH_CLERK', 'SA_MAN')

Or enter the following command:

CHANGE /'CLERK',... /'SH_CLERK'/

The original line changes to

  3* WHERE JOB_ID IS IN ('SH_CLERK')

Or enter the following command:

CHANGE /(...)/('SA_MAN')/

The original line changes to

  3* WHERE JOB_ID IS IN ('SA_MAN')

You can replace the contents of an entire line using the line number. This entry

3  WHERE JOB_ID IS IN ('SH_CLERK')

causes the second line of the buffer to be replaced with

WHERE JOB_ID IS IN ('SH_CLERK')

Note that entering a line number followed by a string will replace the line regardless of what text follows the line number. For example,

2  CHANGE/OLD/NEW/

will change the second line of the buffer to be

  2* C/OLD/NEW/

CLEAR

CL[EAR] option ...

where option represents one of the following clauses:

Resets or erases the current value or setting for the specified option.

Terms

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

BRE[AKS]

BUFF[ER]

COL[UMNS]

COMP[UTES]

SCR[EEN]

SQL

TIMI[NG]

Examples

To clear breaks, enter

CLEAR BREAKS

To clear column definitions, enter

CLEAR COLUMNS

COLUMN

COL[UMN] [{column|expr} [option ...]]

where option represents one of the following clauses:

Specifies display attributes for a given column, such as

• text for the column heading
• alignment of the column heading
• format for NUMBER data
• wrapping of column data

Also lists the current display attributes for a single column or all columns.

Terms

Enter COLUMN followed by column or expr and no other clauses to list the current display attributes for only the specified column or expression. Enter COLUMN with no clauses to list all current column display attributes.

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

{column|expr}

ALI[AS] alias

CLE[AR]

ENTMAP {ON|OFF}

FOLD_A[FTER]

FOLD_B[EFORE]

FOR[MAT] format

9999

0999

9990

$9999

B9999

9999MI

S9999

9999PR

99D99

9G999

C999

L999

9,999

99.99

999V99

9.999EEEE

RN

DATE

HEA[DING] text

COLUMN LAST_NAME HEADING 'Employee |Name'

JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|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]

Usage

You can enter any number of COLUMN commands for one or more columns. All column attributes set for each column remain in effect for the remainder of the session, until you turn the column OFF, or until you use the CLEAR COLUMN command. Thus, the COLUMN commands you enter can control a column's display attributes for multiple SQL SELECT commands.

When you enter multiple COLUMN commands for the same column, SQL*Plus applies their clauses collectively. If several COLUMN commands apply the same clause to the same column, the last one entered will control the output.

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

If you replace WRAP with WORD_WRAP, REMARKS looks like this:

CUSTOMER   DATE        QUANTITY REMARKS
---------- ---------   -------- ---------------------
123        25-AUG-2001      144 This order must be
                                shipped by air freight
                                to ORD

If you specify TRUNCATE, REMARKS looks like this:

CUSTOMER   DATE        QUANTITY REMARKS
---------- ---------   -------- --------------------
123        25-AUG-2001      144 This order must be s

In order 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 in this case instead of EMP_DETAILS_VIEW as you have used up to now. (For details on creating a date variable, see "Displaying the Current Date in Titles" under "Defining Page and Report Titles and Dimensions".)

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;

Your two page report would look similar to the following report, with "Job Report" centered within your current linesize:

                                      Job Report                           
04/19/01

Job:     MK_MAN

LAST
NAME                 MANAGER_ID HIRE_DATE         SALARY DEPARTMENT_ID
-------------------- ---------- --------- -------------- -------------
Hartstein                   100 17-FEB-96     $13,000.00            20
                                          --------------
                                              $13,000.00

                                   Job Report                           04/19/01

Job:     SA_MAN

LAST
NAME                 MANAGER_ID HIRE_DATE         SALARY DEPARTMENT_ID
-------------------- ---------- --------- -------------- -------------
Errazuriz                   100 10-MAR-97     $12,000.00            80
Zlotkey                     100 29-JAN-00     $10,500.00            80
Cambrault                   100 15-OCT-99     $11,000.00            80
Russell                     100 01-OCT-96     $14,000.00            80
Partners                    100 05-JAN-97     $13,500.00            80
                                          --------------

                                   Job Report                           04/19/01

Job:     SA_MAN

LAST
NAME                 MANAGER_ID HIRE_DATE         SALARY DEPARTMENT_ID
-------------------- ---------- --------- -------------- -------------
                                              $12,200.00

6 rows selected.

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.

To display the change, enter a SELECT statement, such as:

SELECT HIRE_DATE
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 206;



                                   Job Report                           
04/19/01

Job:     SA_MAN

HIRE_DATE
----------
1994-06-07

See the Oracle9i SQL Reference for information on the ALTER SESSION command.

Note that in a SELECT statement, some SQL calculations or functions, such as TO_CHAR, may cause a column to be very wide. In such cases, use the FORMAT option to alter the column width.

COMPUTE

COMP[UTE] [function [LAB[EL] text] ...
OF {expr|column|alias} ...
ON {expr|column|alias|REPORT|ROW} ...]

Calculates and prints summary lines, using various standard computations, on subsets of selected rows. It also lists all COMPUTE definitions. For details on how to create summaries, see "Clarifying Your Report with Spacing and Summary Lines".

Terms

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

function ...

AVG

COU[NT]

MIN[IMUM]

MAX[IMUM]

NUM[BER]

SUM

STD

VAR[IANCE]

LAB[EL] text

OF {expr|column|alias} ...

ON {expr|column|alias|REPORT|ROW} ...

Enter COMPUTE without clauses to list all COMPUTE definitions.

Usage

In order for the computations to occur, the following conditions must all be true:

• One or more of the expressions, columns, or column aliases you reference in the OF clause must also be in the SELECT command.
• The expression, column, or column alias you reference in the ON clause must occur in the SELECT command and in the most recent BREAK command.
• If you reference either ROW or REPORT in the ON clause, also reference ROW or REPORT in the most recent BREAK command.

To remove all COMPUTE definitions, use the CLEAR COMPUTES command.

Note that if you use the NOPRINT option for the column on which the COMPUTE is being performed, the COMPUTE result is also suppressed.

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 total of salaries greater than 12,000 on a report, enter

COMPUTE SUM OF SALARY ON REPORT
BREAK ON REPORT
COLUMN DUMMY HEADING ''
SELECT '   ' DUMMY, SALARY, EMPLOYEE_ID
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY SALARY;



        SALARY EMPLOYEE_ID
--- ---------- -----------
         13000         201
         13500         146
         14000         145
         17000         101
         17000         102
         24000         100
    ----------
sum      98500

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

To total the salary at the end of the report without printing the compute label, enter

COLUMN DUMMY NOPRINT
COMPUTE SUM OF SALARY ON DUMMY
BREAK ON DUMMY
SELECT NULL DUMMY, DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID <= 30
ORDER BY DEPARTMENT_ID;



DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           10 Whalen                          4400
           20 Hartstein                      13000
           20 Fay                             6000
           30 Raphaely                       11000
           30 Khoo                            3100
           30 Baida                           2900
           30 Tobias                          2800
           30 Himuro                          2600
           30 Colmenares                      2500
                                        ----------
                                             48300

9 rows selected.

CONNECT

CONN[ECT] { logon | / } [AS {SYSOPER|SYSDBA}]

where logon requires the following syntax:

Connects a given username to Oracle. In iSQL*Plus you must always include your username and password in a CONNECT command as iSQL*Plus does not prompt for a missing password. CONNECT does not reprompt for username or password if the initial connection does not succeed.

Terms

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

username/password

connect_identifier

/ (slash)

AS {SYSOPER|SYSDBA}

Usage

CONNECT commits the current transaction to the database, disconnects the current username from Oracle, and reconnects with the specified username.

If you log on or connect as a user whose account has expired, SQL*Plus prompts you to change your password before you can connect.

If an account is locked, a message is displayed and connection into that account (as that user) is not permitted until the account is unlocked by your DBA.

For more information about user account management, refer to the CREATE USER, ALTER USER and the CREATE PROFILE commands in the Oracle9i SQL Reference.

Examples

To connect across Oracle Net with username HR and password HR to the database known by the Oracle Net alias as FLEETDB, enter

CONNECT HR/your_password@FLEETDB

To connect as user HR, letting SQL*Plus prompt you for the password, enter

CONNECT HR

For more information about setting up your password file, refer to the Oracle9i Database Administrator's Guide.

To use a password file to connect to an instance on the current node as a privileged user named HR with the password HR, enter

CONNECT HR/your_password AS SYSDBA

To connect to an instance on the current node as a privileged default user, enter

CONNECT / AS SYSDBA

In the last two examples, your default schema becomes SYS.

COPY

The COPY command is not being enhanced to handle datatypes or features introduced with, or after Oracle8. The COPY command is likely to be made obsolete in a future release. For COPY command details and syntax, see Appendix B, "SQL*Plus COPY Command".

DEFINE

DEF[INE] [variable]|[variable = text]

Specifies a user variable and assigns a CHAR value to it, or lists the value and variable type of a single variable or all variables.

Terms

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

variable

text

variable = text

Enter DEFINE followed by variable to list the value and type of variable. Enter DEFINE with no clauses to list the values and types of all user variables.

Usage

Defined variables retain their values until one of the following events occurs:

• you enter a new DEFINE command referencing the variable
• you enter an UNDEFINE command referencing the variable
• you enter an ACCEPT command referencing the variable
• you reference the variable in the NEW_VALUE or OLD_VALUE clause of the COLUMN command and reference the column in a subsequent SQL SELECT command
• you EXIT SQL*Plus

Whenever you run a stored query or script, SQL*Plus substitutes the value of variable for each substitution variable referencing variable (in the form &variable or &&variable). SQL*Plus will not prompt you for the value of variable in this session until you UNDEFINE variable.

Note that you can use DEFINE to define the variable, _EDITOR, which establishes the host system editor invoked by the SQL*Plus EDIT command.

If you continue the value of a defined variable on multiple lines (using the SQL*Plus command continuation character), SQL*Plus replaces each continuation character and carriage return you enter with a space in the resulting variable. For example, SQL*Plus interprets

DEFINE TEXT = 'ONE-
TWO-
THREE'

as

DEFINE TEXT = 'ONE TWO THREE'

You should avoid defining variables with names that may be identical to values that you will pass to them, as unexpected results can occur. If a value supplied for a defined variable matches a variable name, then the contents of the matching variable are used instead of the supplied value.

Some variables are predefined when SQL*Plus starts. Enter DEFINE to see their definitions.

Examples

To assign the value MANAGER to the variable POS, type:

DEFINE POS = MANAGER

If you execute a command that contains a reference to &POS, SQL*Plus will substitute 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.

DEL

DEL is not available in iSQL*Plus.

DEL [n|n m|n *|n LAST|*|* n|* LAST|LAST]

Deletes one or more lines of the buffer.

Terms

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

Enter DEL with no clauses to delete the current line of the buffer.

Usage

DEL makes the following line of the buffer (if any) the current line. You can enter DEL several times to delete several consecutive lines.

Examples

Assume the SQL buffer contains the following query:

SELECT LAST_NAME, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'SA_MAN'
ORDER BY DEPARTMENT_ID;

To make the line containing the WHERE clause the current line, you could enter

LIST 3


  3* WHERE JOB_ID = 'SA_MAN'

followed by

DEL

The SQL buffer now contains the following lines:

SELECT LAST_NAME, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
ORDER BY DEPARTMENT_ID

To delete the third line of the buffer, enter

DEL 3

The SQL buffer now contains the following lines:

SELECT LAST_NAME, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW

DESCRIBE

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.

Terms

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

schema

object

@connect_identifier

Usage

The description for tables, views, types and synonyms contains the following information:

• each column's name
• whether or not null values are allowed (NULL or NOT NULL) for each column
• datatype of columns, for example, CHAR, DATE, LONG, LONGRAW, NUMBER, RAW, ROWID, VARCHAR2 (VARCHAR), or XMLType
• precision of columns (and scale, if any, for a numeric column)

When you do a DESCRIBE, VARCHAR columns are returned with a type of VARCHAR2.

The DESCRIBE command allows you to describe objects recursively to the depth level set in the SET DESCRIBE command. You can also display the line number and indentation of the attribute or column name when an object contains multiple object types. For more information, see the SET command later in this chapter.

To control the width of the data displayed, use the SET LINESIZE command.

Columns output for the DESCRIBE command are typically allocated a proportion of the linesize currently specified. Decreasing or increasing the linesize with the SET LINESIZE command usually makes each column proportionally smaller or larger. This may give unexpected text wrapping in your display. For more information, see the SET command later in this chapter.

The description for functions and procedures contains the following information:

• the type of PL/SQL object (function or procedure)
• the name of the function or procedure
• the type of value returned (for functions)
• the argument names, types, whether input or output, and default values, if any

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

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 Oracle9i SQL Reference.

For information about using the SET DESCRIBE and SHOW DESCRIBE commands, see the SET and SHOW commands later in this chapter.

DISCONNECT

DISC[ONNECT]

Commits pending changes to the database and logs the current username out of Oracle, 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 but have the user remain in SQL*Plus. Use Logout in iSQL*Plus, and EXIT or QUIT in SQL*Plus command-line to log out of Oracle and return control to your host computer's operating system.

Examples

Your script might begin with a CONNECT command and end with a DISCONNECT, as shown below.

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.

ED[IT] [file_name[.ext]]

Invokes a host operating system text editor on the contents of the specified file or on the contents of the buffer.

Terms

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

file_name[.ext]

Enter EDIT with no filename to edit the contents of the SQL buffer with the host 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 current working directory. If SQL*Plus cannot find the file in the current working directory, it creates a file with the specified name.

The user variable, _EDITOR, contains the name of the text editor invoked by EDIT. You can change the text editor by changing the value of _EDITOR. See COPY for information about changing the value of a user variable. If _EDITOR is undefined, EDIT attempts to invoke the default host operating system editor.

EDIT alone places the contents of the SQL buffer in a file by default named AFIEDT.BUF (in your current working directory) and invokes 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.

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.

Examples

To edit the file REPORT with the extension SQL using your host operating system text editor, enter

EDIT REPORT

EXECUTE

EXEC[UTE] 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.

Terms

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

statement

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.

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 in this chapter.

EXIT

{EXIT|QUIT} [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable] [COMMIT|ROLLBACK]

Commits or rolls back all pending changes, logs out of Oracle, 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. In iSQL*Plus click the Logout button to log out of Oracle.

Terms

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

{EXIT|QUIT}

SUCCESS

FAILURE

WARNING

COMMIT

n

variable

:BindVariable

ROLLBACK

EXIT with no clauses commits and exits with a value of SUCCESS.

Usage

EXIT allows you to specify an operating system return code. This allows 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. See the Oracle installation and user's manual(s) provided for your operating system for details.

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 later in this chapter.

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.

GET [FILE] file_name[.ext] [LIS[T]|NOL[IST]]

Loads a host operating system file into the SQL buffer.

Terms

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

FILE

file_name[.ext]

LIS[T]

NOL[IST]

Usage

If you do not specify a 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 the filename you specify contains the word list or the word file, the name must be in double quotes. SQL*Plus searches for the file in the current working directory.

The operating system file should contain a single SQL statement or PL/SQL block. The statement should not be terminated with a semicolon. If a SQL*Plus command or more than one SQL statement or PL/SQL block is loaded into the SQL buffer from an operating system file, an error occurs when the RUN or slash (/) command is used to execute the buffer.

The GET command can be used to load files created with the SAVE command. See the SAVE command in this chapter for more information.

Examples

To load a file called YEARENDRPT with the extension SQL into the buffer, enter

GET YEARENDRPT

HELP

HELP [topic]

Accesses the SQL*Plus command-line help system. Enter HELP INDEX for a list of topics.

Terms

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

topic

Enter HELP 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

Alternatively, to see a single column display of SQL*Plus commands for which help is available, enter

HELP TOPICS

HOST

HOST is not available in iSQL*Plus.

HO[ST] [command]

Executes a host operating system command without leaving SQL*Plus.

Terms

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

command

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 Oracle installation and user's manual(s) provided for your operating system.

Usage

With some operating systems, you can use a "$" (VMS), "!" (UNIX), or another character instead of HOST. See the Oracle installation and user's manual(s) provided for your operating system for details.

You may not have access to the HOST command, depending on your operating system. See the Oracle installation and user's manual(s) provided for your operating system or ask your DBA for more information.

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 the SQLTERMINATOR variable of the SET command in this chapter for more information on the SQLTERMINATOR.

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.

I[NPUT] [text]

Adds one or more new lines of text after the current line in the buffer.

Terms

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

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

L[IST] [n|n m|n *|n LAST|*|* n|* LAST|LAST]

Lists one or more lines of the SQL buffer.

Terms

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

Enter LIST with no clauses to list all lines. The last line or only line listed becomes the new current line (marked by an asterisk).

Examples

To list the contents of the buffer, enter

LIST

You will see a listing of all lines in the buffer, similar to the following example:

  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

PASSWORD is not available in iSQL*Plus. In iSQL*Plus use the Password screen.

PASSW[ORD] [username]

Allows you to change a password without echoing it on an input device.

Terms

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

username

Usage

To change the password of another user, you must have been granted the appropriate privilege. For more information about changing your password, see the CONNECT command in this chapter.

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

PAUSE is not available in iSQL*Plus.

PAU[SE] [text]

Displays the specified text then waits for the user to press RETURN.

Terms

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

text

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.

For information on pausing between pages of a report, see the PAUSE variable of the SET command later in this chapter.

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

PRI[NT] [variable ...]

Displays the current values of bind variables. For more information on bind variables, see your PL/SQL User's Guide and Reference.

Terms

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

variable ...

Enter PRINT with no variables to print all bind variables.

Usage

Bind variables are created using the VARIABLE command. For more information and examples, see the VARIABLE command in this chapter.

You can control the formatting of the PRINT output just as you would query output. For more information, see the formatting techniques described in Chapter 7, "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. For more information, see the SET command in this chapter.

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

PRO[MPT] [text]

Sends the specified message or a blank line to the user's screen.

Terms

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

text

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. ASKFORDEPT contains the following SQL*Plus and SQL commands:

PROMPT
PROMPT Please enter a valid department
PROMPT For example:  10, 20, 30, 40
SELECT DEPARTMENT_NAME FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID = &NEWDEPT

Assume you run the file using START or @:

@YEAREND.SQL VAL1 VAL2.SQL VAL1 VAL2x



Please enter a valid department
For example:  10, 20, 30, 40
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

RECOVER {general | managed | END BACKUP}

where the general clause has the following syntax:

where the managed clause has the following syntax:

MANAGED STANDBY DATABASE
[ {NODELAY | [TIMEOUT] integer | CANCEL [IMMEDIATE] [NOWAIT]}
| [DISCONNECT [FROM SESSION] ] [FINISH [NOWAIT] ] ]

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 Oracle9i Database Administrator's Guide, the ALTER DATABASE RECOVER command in the Oracle9i SQL Reference, the Oracle9i Backup and Recovery ConceptsOracle9i Backup and Recovery Concepts guide, and the Oracle9i User-Managed Backup and Recovery Guide.

Because of possible network timeouts, it is recommended that you use SQL*Plus command-line for long running DBA operations such as RECOVER.

You must set AUTORECOVERY ON to use the RECOVER command in iSQL*Plus. Attempting to RECOVER a database with AUTORECOVERY OFF raises the error:

SP2-0872 SET AUTORECOVERY ON must be used in iSQL*Plus

Terms

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

AUTOMATIC

FROM location

LOGFILE

TEST ALLOW integer CORRUPTION

CONTINUE

CONTINUE DEFAULT

CANCEL

STANDBY DATABASE

DATABASE

UNTIL CANCEL

UNTIL TIME

'YYYY-MM-DD:HH24:MI:SS'

UNTIL CHANGE

USING BACKUP CONTROLFILE

TABLESPACE

DATAFILE

STANDBY TABLESPACE

STANDBY DATAFILE

UNTIL CONSISTENT WITH CONTROLFILE

MANAGED STANDBY DATABASE

NODELAY

TIMEOUT

CANCEL (managed clause)

CANCEL IMMEDIATE

CANCEL NOWAIT

DISCONNECT FROM SESSION

FINISH

NOWAIT

Usage

You must have the OSDBA role enabled. You cannot use the RECOVER command when connected via 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 datafile(s) 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

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, - - ..., refer to "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

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:

Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition.

Terms

Refer to the REPHEADER command for additional information on terms and clauses in the REPFOOTER command syntax.

Enter REPFOOTER with no clauses to list the current REPFOOTER definition.

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.

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

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:

Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition.

Terms

Refer to the following list for a description of each term or clause. These terms and clauses also apply to the REPFOOTER command.

PAGE

text

variable

OFF

COL n

S[KIP] [n]

TAB n

LE[FT] CE[NTER] R[IGHT]

BOLD

FORMAT text

Enter REPHEADER with no clauses to list the current REPHEADER definition.

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

R[UN]

Lists and executes the SQL command or PL/SQL block currently stored in the SQL buffer.

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.

SAV[E] [FILE] file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

Saves the contents of the SQL buffer in a host operating system script.

Terms

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

FILE

file_name[.ext]

CREATE

REP[LACE]

APP[END]

Usage

If you do not specify an extension, SQL*Plus assumes the default command-file extension (normally SQL). For information on changing this default extension, see the SUFFIX variable of the SET command in this chapter.

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:

• display width for data
• customize HTML formatting
• enabling or disabling the printing of column headings
• the number of lines per page

You can also use the System Variables screen in iSQL*Plus to set system variables.

SET system_variable value

where system_variable and value are shown in the SET commands following.

Usage

SQL*Plus maintains system variables (also called SET command variables) to enable you to setup 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 Oracle9i SQL Reference for more information). When not followed by the keywords TRANSACTION or ROLE, SET is assumed to be a SQL*Plus command.

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 Oracle9i Database Performance Tuning Guide and Reference manual.

Example

To display the setting 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 APPI OFF

SET ARRAY[SIZE] {15|n}

Sets the number of rows--called a batch--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 commits pending changes to the database. ON commits pending changes to the database after Oracle executes each successful INSERT, UPDATE, or DELETE command 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 the ON option. n commits pending changes to the database after Oracle executes n 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 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). For more information about displaying bind variables, see the PRINT command in this chapter.

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 when AUTORECOVERY is set to ON, provided the necessary files are in the expected locations with the expected names. The filenames used when AUTORECOVERY is ON are derived from the values of the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT.

You must set AUTORECOVERY ON to use the RECOVER command in iSQL*Plus. Attempting to RECOVER a database with AUTORECOVERY OFF raises the error:

SP2-0872 SET AUTORECOVERY ON must be used in iSQL*Plus

OFF, the default option, requires that you enter the filenames manually or accept the suggested default filename given. See the RECOVER command in this chapter 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 Oracle9i SQL Reference manual.

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 Oracle9i Database Performance Tuning Guide and Reference manual.

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}

In iSQL*Plus, SET COLSEP determines the column separator character to be printed between column output that is rendered inside tags. HTML table output is the default. To generate preformatted output you must set PREFORMAT ON with the SET MARKUP HTML PREFORMAT ON command.

Sets the text to be printed between selected columns. 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 in this chapter for more information.

Example

To set the column separator to "|" enter

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 SQL syntax to use. Set COMPATIBILITY to V7 for Oracle7, or to V8 for Oracle8 or later. COMPATIBILITY always defaults to NATIVE. COMPATIBILITY should be correctly set for the version of Oracle SQL syntax you want to use on the database to which you are connected, 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 Oracle9i:

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 batches 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. For more information on the SCAN variable, see the SET SCAN command.

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.

For more information about describing objects, see DESCRIBE earlier in this chapter.

Example

To describe the view EMP_DETAILS_VIEW to a depth of two levels, and indent the output while also displaying line numbers, first describe the view as follows:

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 format EMP_DETAILS_VIEW so that the output displays with indentation and line numbers, use the SET DESCRIBE command as follows:

SET DESCRIBE DEPTH 2 LINENUM ON INDENT ON

To display the above settings, enter

DESCRIBE EMP_DETAILS_VIEW



           Name                Null?    Type
           ------------------- -------- --------------------
    1      EMPLOYEE_ID         NOT NULL NUMBER(6)
    2      JOB_ID              NOT NULL VARCHAR2(10)
    3      MANAGER_ID                   NUMBER(6)
    4      DEPARTMENT_ID                NUMBER(4)
    5      LOCATION_ID                  NUMBER(4)
    6      COUNTRY_ID                   CHAR(2)
    7      FIRST_NAME                   VARCHAR2(20)
    8      LAST_NAME           NOT NULL VARCHAR2(25)
    9      SALARY                       NUMBER(8,2)
   10      COMMISSION_PCT               NUMBER(2,2)
   11      DEPARTMENT_NAME     NOT NULL VARCHAR2(30)
   12      JOB_TITLE           NOT NULL VARCHAR2(35)
   13      CITY                NOT NULL VARCHAR2(30)
   14      STATE_PROVINCE               VARCHAR2(25)
   15      COUNTRY_NAME                 VARCHAR2(40)
   16      REGION_NAME                  VARCHAR2(25)

SET ECHO {ON|OFF}

Controls whether the START command lists each command in a script as the command is executed. ON lists the commands; OFF suppresses the listing.

SET EDITF[ILE] file_name[.ext]

SET EDITFILE is not supported in iSQL*Plus

Sets the default filename for the EDIT command. For more information about the EDIT command, see EDIT in this chapter.

You can include a path and/or file extension. For information on changing the default extension, see the SUFFIX variable of the SET command. 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 allows 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 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 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 allows the host operating system to buffer output. ON disables buffering.

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 will not affect the column width displayed, and 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 the heading separator character. 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 "|".

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. See your operating system-specific Oracle documentation for a description of how to set the initial default instance.

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 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}

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. Refer to the Oracle installation and user's manual(s) provided for your operating system.

SET LOBOF[FSET] {n|1}

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 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/oracle90/dbs/arch" enter

SET LOGSOURCE "/usr/oracle90/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 gigabytes.

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.

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.

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.

For detailed information see "MARKUP Options". For examples of usage, see SET MARKUP, and .

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:

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">/EM>, 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:

Text description of the illustration markup.gif

SET NEWP[AGE] {1|n|NONE}

SET NEWPAGE is not supported in iSQL*Plus

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 that represents a null value 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.

SET NUMF[ORMAT] format

Sets the default format for displaying numbers. Enter a number format for format. For number format descriptions, see the FORMAT clause of the COLUMN command in this chapter.

SET NUM[WIDTH] {10|n}

Sets the default width for displaying numbers. For number format descriptions, see the FORMAT clause of the COLUMN command in this chapter.

SET PAGES[IZE] {24|n}

Sets the number of lines in each page. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

SET PAU[SE] {ON|OFF|text}

SET PAUSE is not supported in iSQL*Plus

Allows you to control scrolling of your terminal when running reports. ON causes SQL*Plus to pause at the beginning of each page of report output. You must press [Return] after each pause. The text you enter specifies the text to be displayed each time SQL*Plus pauses. If you enter multiple words, you must enclose text 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.

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.

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 Oracle8i or Oracle9i database server. The default for n is 2000. n cannot be less than 2000 or greater than 1,000,000.

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 each FORMAT, every server output line begins on a new output line.

For more information on DBMS_OUTPUT.PUT_LINE, see your Oracle9i 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 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 ('TEXT');



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

Allows 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 allows 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 or output format of VARIABLE 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, 8.1.7 or 9.0.0. In later releases, SQLPLUSCOMPATIBILITY may affect features other than VARIABLE.

Setting the value of SQLPLUSCOMPATIBILITY to a version less than 9.0.0 will result in VARIABLE definition of NCHAR or NVARCHAR2 datatypes to revert to Oracle8i behavior whereby the size of the variable is in bytes or characters depending on the chosen national character set.

The default glogin.sql file contains SET SQLPLUSCOMPAT 8.1.7. It is recommended that you add SET SQLPLUSCOMPAT 9.0.0 to your scripts to maximize their compatibility with future versions of SQL*Plus.

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.

Example

You need the Select Any Table privilege to successfully run the following example scripts.

To change your SQL*Plus prompt to display your username and SID, enter:

SET SQLPROMPT '&_CONNECT_IDENTIFIER > '

To change your SQL*Plus prompt to display your SID enter:

SET TERMOUT OFF
COLUMN X NEW_VALUE Y
SELECT RTRIM(INSTANCE, CHR(0)) X FROM V$THREAD;
SET SQLPROMPT '&Y SQL>'
SET TERMOUT ON

To set the SQL*Plus command prompt to show the current user, enter

SET TERMOUT OFF
COLUMN D22 NEW_VALUE VAR
SELECT USERNAME D22 FROM USER_USERS;
SET SQLPROMPT '&&VAR>'
SET TERMOUT ON

These settings are not dynamic. You need to reset them whenever you change instances, such as when you use the connect command to log on to another instance.

SET SQLT[ERMINATOR] {;|c|ON|OFF}

Sets the character used to end and execute SQL commands to c. 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. 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 .UFI, enter

SET SUFFIX UFI

If you then enter

GET EXAMPLE

SQL*Plus will look for a file named EXAMPLE.UFI 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 executed from a script. OFF suppresses the display so that you can spool output from a script without seeing the output on the screen. ON displays the output. TERMOUT OFF does not affect output from commands you enter interactively.

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. For information about the data SET TIMING ON displays, see the Oracle installation and user's manual(s) provided for your operating system. Refer to the TIMING command for information on timing multiple commands.

SET TRIM[OUT] {ON|OFF}

SET TRIMOUT is not supported in iSQL*Plus

Determines whether SQL*Plus allows 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 allows 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 allows trailing blanks at the end of each spooled line. ON removes blanks at the end of each line. OFF allows 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 to c. Note, c 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 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 allows 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

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
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L]
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

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

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]

SHOW PARAMETERS COUNT 



NAME                             TYPE     VALUE  
------------------------------   -----    -----  
db_file_multiblock_read_count    integer  12
spin_count                       integer  0

ORA-00942: table or view does not exist

PNO

REL[EASE]

REPF[OOTER]

REPH[EADER]

SPOO[L]

SGA

ORA-00942: table or view does not exist

SQLCODE

TTI[TLE]

USER

USER is "SYS"

Examples

To list the current LINESIZE, enter

SHOW LINESIZE

If the current linesize is 80 characters, SQL*Plus will give the following response:

LINESIZE 80

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 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 

SHUTDOWN

SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL [LOCAL]]

Shuts down a currently running Oracle instance, optionally closing and dismounting a database.

Terms

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

ABORT

IMMEDIATE

NORMAL

TRANSACTIONAL [LOCAL]

Usage

SHUTDOWN with no arguments is equivalent to SHUTDOWN NORMAL.

You must be connected to a database as SYSOPER, or SYSDBA. You cannot connect via a multi-threaded server. For more information about connecting to a database, see the CONNECT command earlier in this chapter.

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. Use preference settings to output to a file.

SPO[OL] [file_name[.ext]|OFF|OUT]

Stores query results in a file, or optionally sends the file to a printer.

Terms

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

file_name[.ext]

OFF

OUT

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 run interactively.

Examples

To record your output in the file DIARY using the default file extension, enter

SPOOL DIARY

To stop spooling and print the file on your default printer, enter

SPOOL OUT

START

STA[RT] {url|file_name[.ext] } [arg...]

Runs the SQL*Plus statements in the specified script. The script can be called from a web server in iSQL*Plus, or from the local file system or a web server in SQL*Plus command-line. You can pass values to script variables in the usual way.

Terms

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

url

file_name[.ext]

arg ...

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 (see ""Disabling SQL*Plus, SQL, and PL/SQL Commands"), also disables the @ and @@ commands. See the @ ("at" sign) and @@ (double "at" sign) commands in this chapter for further information on these commands.

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, SALARY
FROM EMP_DETAILS_VIEW
WHERE 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

STARTUP options | migrate_options

where options has the following syntax:

and where migrate_options has the following syntax:

Starts an Oracle instance with several options, including mounting and opening a database.

Terms

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

FORCE

RESTRICT

PFILE=filename

QUIET

MOUNT dbname

OPEN

NOMOUNT

RECOVER

MIGRATE

Usage

You must be connected to a database as SYSOPER, or SYSDBA. You cannot be connected via 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 database administrators, 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.

STORE SET file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

Saves attributes of the current SQL*Plus environment in a host operating system script.

Terms

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

SET

Refer to the SAVE command for information on the other terms and clauses in the STORE command syntax.

Usage

This command creates a script which can be executed with the START, @ or @@ 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

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

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

START text

SHOW

STOP

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.

For information about the data TIMING displays, see the Oracle installation and user's manual(s) provided for your operating system. 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

TTI[TLE] [printspec [text|variable] ...] [ON|OFF]

where printspec represents one or more of the following clauses used to place and format the text:

Places and formats a specified title at the top of each report page or lists the current TTITLE definition. The old form of TTITLE is used if only a single word or string in quotes follows the TTITLE command.

For a description of the old form of TTITLE, see TTITLE.

Terms

Refer to the following list for a description of each term or clause. These terms and clauses also apply to the BTITLE command.

text

variable

OFF

ON

COL n

S[KIP] [n]

TAB n

LE[FT]|CE[NTER]|R[IGHT]

BOLD

FORMAT text

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 NEW_VALUE for information on printing column and DATE values in the top title.

You can use any number of constants and variables in a printspec. SQL*Plus displays them in the order you specify them, positioning and formatting each constant or variable as specified by the printspec clauses that precede it.

The length of the title you specify with TTITLE cannot exceed 2400 characters.

The continuation character (a hyphen) will not be recognized inside a single-quoted title text string. To be recognized, the continuation character must appear outside the quotes, as follows:

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 2001' -
RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP CENTER -
'Data in Thousands'



Monthly Analysis               01 Jan 2001           Page:   1
                            Data in Thousands

To suppress the top title display without changing its definition, enter

TTITLE OFF

UNDEFINE

UNDEF[INE] variable ...

Deletes one or more user variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command).

Terms

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

variable

Examples

To undefine a user variable named POS, enter

UNDEFINE POS

To undefine two user variables named MYVAR1 and MYVAR2, enter

UNDEFINE MYVAR1 MYVAR2

VARIABLE

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

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

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

Terms

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

variable

NUMBER

CHAR

CHAR (n[CHAR|BYTE])

NCHAR

NCHAR (n)

VARCHAR2 (n[CHAR|BYTE])

NVARCHAR2 (n)

CLOB

NCLOB

REFCURSOR

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. For more information, see the PRINT command in this chapter.

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

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

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

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

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

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

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

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

Examples

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

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 ***

DEPARTMENT_NAME                Employee                        Salary
------------------------------ ------------------------- ------------
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 Oracle9i SQL Reference.

WHENEVER OSERROR

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 Input area if an operating system error occurs.

Terms

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

[SUCCESS|FAILURE|n|variable|:BindVariable]

EXIT [SUCCESS|FAILURE|n|variable|:BindVariable]

CONTINUE

COMMIT

ROLLBACK

NONE

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 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

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 Input area if a SQL command or PL/SQL block generates an error.

Terms

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

[SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]

EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]

CONTINUE

COMMIT

ROLLBACK

NONE

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 Work screen:

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 Work screen:

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.....