iSQL*Plus logo

Previous Page
Previous

Next Page
Next

Table Of Contents
Contents

Index
Index

Command Syntax

@ ("at" sign)

PRINT

@@ (double "at" sign)

PROMPT

/ (slash)

RECOVER

ARCHIVE LOG

REMARK

ATTRIBUTE

REPFOOTER

BREAK

REPHEADER

BTITLE

RUN

CLEAR

SET

COLUMN

SHOW

COMPUTE

SHUTDOWN

CONNECT

START

DEFINE

STARTUP

DESCRIBE

TIMING

DISCONNECT

TTITLE

EXECUTE

UNDEFINE

EXIT

VARIABLE

HELP

WHENEVER OSERROR

LIST

WHENEVER SQLERROR

@ ("at" sign)

@url[arg...]

Calls the specified script specified by the URL from a web server and runs the SQL*Plus statements in the script.

Examples

You can run a script named YEAREND specified by a URL, and pass values to variables referenced in YEAREND in the usual way:

@HTTP://HOST.DOMAIN/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://HOST.DOMAIN/SCRIPTSERVER?ENDOFYEAR VAL1 VAL2

@@ (double "at" sign)

@@file_name[.ext]

Runs a script. This command is 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.

Examples

Suppose that you have the following script named PRINTRPT:

SELECT DEPARTMENT_ID, CITY FROM EMP_DETAILS_VIEW WHERE SALARY>12000;
@EMPRPT
@@ WKRPT

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://HOST.DOMAIN/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 and runs it.

/ (slash)

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

Examples

Type the following SQL script:

SELECT CITY, COUNTRY_NAME
FROM EMP_DETAILS_VIEW
WHERE SALARY=12000;

Enter a slash (/) 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

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.

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.

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:

report_element

Requires the following syntax:

{column|expr|ROW|REPORT}

action

Requires the following syntax:

[SKI[P] n|[SKI[P]] PAGE][NODUP[LICATES]|DUP[LICATES]]

Specifies where and how formatting will change in a report, such as

Also lists the current BREAK definition.

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]

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

Examples

To set a bottom title with CORPORATE PLANNING DEPARTMENT on the left and a date on the right, enter

BTITLE LEFT 'CORPORATE PLANNING DEPARTMENT' -
RIGHT '1 JAN 2001'

To set a bottom title with CONFIDENTIAL in column 50, followed by six spaces and a date, enter

BTITLE COL 50 'CONFIDENTIAL' TAB 6 '1 JAN 2001'

CLEAR

CL[EAR] option ...

where option represents one of the following clauses:

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

Examples

To clear breaks, enter

CLEAR BREAKS

To clear column definitions, enter

CLEAR COLUMNS

COLUMN

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

where option represents one of the following clauses:

Specifies display attributes for a given column, such as

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

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

Check that this is correct! 
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.

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.

Examples

To subtotal the salary for the "account manager" and "salesman" 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.

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/HR@FLEETDB

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

DEFINE

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

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

Examples

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

DEFINE POS = MANAGER

If you execute a command 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.

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.

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 describe the object emp_object and then format the output using the SET DESCRIBE command, first enter

This emp_object has not been created! The following example is not very helpful, if illustrative.

DESCRIBE emp_object

SQL*Plus lists the following information:

 Name                                      Null     Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE                                           RECUR_PERSON
 DEPT                                               RECUR_DEPARTMENT
 START_DATE                                         DATE
 POSITION                                           VARCHAR2(1)
 SALARY                                                RECUR_SALARY

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.

Examples

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

CONNECT HR/HR
SELECT LAST_NAME, DEPARTMENT_NAME FROM EMP_DETAILS_VIEW;
DISCONNECT
SET INSTANCE FIN2
CONNECT HR2/HR2

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.

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]

Stops processing the current iSQL*Plus script and returns focus to the input pane in the Work screen.It does not disconnect your iSQL*Plus session from the database, but there is no way to access any returned status in iSQL*Plus.

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

HELP

HELP [topic]

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

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

LIST

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

Lists one or more lines of the SQL buffer.

Examples

To list the contents of the buffer, enter

LIST

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

PRINT

PRI[NT] [variable ...]

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

Examples

The following example illustrates a PRINT command:

VARIABLE n NUMBER
BEGIN
:n := 1;
END;
/
PL/SQL procedure successfully completed.

PRINT n
         N
----------
         1
PRINT n
         N
----------
         1

PROMPT

PRO[MPT] [text]

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

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.

Because of possible network timeouts, it is recommended that you use the Oracle Enterprise Manager for long running DBA operations such as RECOVER.

As iSQL*Plus cannot handle interactive commands, you must set AUTORECOVERY ON to use the RECOVER command. Attempting to RECOVER a database with AUTORECOVERY OFF raises the error:

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

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.

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.

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.

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.

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.

SET

Sets a system variable to alter the SQL*Plus environment settings for your current session, for example:

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

SET system_variable value

where system_variable and value represent one of the following.

SET APPI[NFO]{ON|OFF|text}

Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.

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.

SET AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}

Controls when Oracle commits pending changes to the database.

SET AUTOP[RINT] {ON|OFF}

Sets the automatic printing of bind variables.

SET AUTORECOVERY [ON|OFF]

ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.

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

SET BLO[CKTERMINATOR] {.|c|ON|OFF}

Sets the character used to end PL/SQL blocks to c.

SET CMDS[EP] {;|c|ON|OFF}

Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.

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 <PRE> tags. HTML table output is the default. To generate preformatted output you must set PREFORMAT ON with the SET MARKUP HTML PREFORMAT ON command.

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 to which you are currently connected.

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.

SET COPYC[OMMIT] {0|n}

Controls the number of batches after which the COPY command commits changes to the database.

SET COPYTYPECHECK {ON|OFF}

Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.

SET DEF[INE] {&|c|ON|OFF}

Sets the character used to prefix substitution variables to c.

SET DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]

Sets the depth of the level to which you can recursively describe an object.

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.

SET EMB[EDDED] {ON|OFF}

Controls where on a page each report begins.

SET ESC[APE] {\|c|ON|OFF}

Defines the character used as the escape character.

Example

If you define the escape character as an exclamation point (!), then

SET ESCAPE !
ACCEPT v1 PROMPT 'Enter !&1:'

displays this prompt:

Enter &1:

To set the escape character back to the default value of \ (backslash), enter

SET ESCAPE ON

SET FEED[BACK] {6|n|ON|OFF}

Displays the number of records returned by a script when a script selects at least n records.

SET FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}

Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.

SET HEA[DING] {ON|OFF}

Controls printing of column headings in reports.

Example

To suppress the display of column headings in a report, enter

SET HEADING OFF

If you then run a SQL SELECT command

SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'AC_MGR';

the following output results:

Higgins                   12000

To turn the display of column headings back on, enter

SET HEADING ON

SET HEADS[EP] {||c|ON|OFF}

Defines the character used as the heading separator character.

SET INSTANCE [instance_path|LOCAL]

Changes the default instance for your session to the specified instance path.

Example

To set the default instance to "PROD1" enter

DISCONNECT
SET INSTANCE PROD1

To set the instance back to the default of local, enter

SET INSTANCE local

You must disconnect from any connected instances to change the instance.

SET LIN[ESIZE] {150|n}

Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.

SET LOBOF[FSET] {n|1}

Sets the starting position from which CLOB and NCLOB data is retrieved and displayed.

Example

SET LOBOFFSET

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.

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 LONG, CLOB and NCLOB values; and for copying LONG values.

Example

To set the maximum number of characters 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 character. The default for LONG is 80 characters.

SET LONGC[HUNKSIZE] {80|n}

Sets the size (in bytes) of the increments SQL*Plus uses to retrieve a LONG, CLOB or NCLOB value.

Example

To set the size of the increments in which SQL*Plus retrieves LONG values to 100 characters, enter

SET LONGCHUNKSIZE 100

The LONG data will be retrieved in increments of 100 characters 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.

SET NULL text

Sets the text that represents a null value in the result of a SQL SELECT command.

SET NUMF[ORMAT] format

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

SET NUM[WIDTH] {10|n}

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

SET PAGES[IZE] {24|n}

Sets the number of lines in each page.

SET RECSEPCHAR {_|c}

Defines the character to display or print to separate records.

SET RECSEP {WR[APPED]|EA[CH]|OFF}

RECSEP tells SQL*Plus where to make the record separation.

SET SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]}]

Controls whether to display the output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.

Example

To enable the display of text within 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 SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}

Converts the case of SQL commands and PL/SQL blocks just prior to execution.

SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}

Sets the behavior or output format of VARIABLE to that of the release or version specified by x.y[.z].

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

Sets the character used to end and execute SQL commands to c.

SET TIMI[NG] {ON|OFF}

Controls the display of timing statistics.

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

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.

Examples

To shutdown the database in normal mode, enter

SHUTDOWN 

Database closed. 
Database dismounted. 
Oracle instance shut down. 

START

STA[RT] url[arg...]

Calls the script specified by the URL from a web server and runs the SQL*Plus statements in the script

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 HTTP://HOST.DOMAIN/PROMOTE.SQL ST_MAN 7000

Where HOST.DOMAIN must be replaced by the host.domain name for 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.

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

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.

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.

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

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 then be referenced in PL/SQL. 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.

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

Stops the current script and returns focus to the Input area on the Work screen if an operating system error occurs (such as a file I/O error). EXIT terminates the script currently being processed, it does not disconnect from the database or exit iSQL*Plus.

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

Stops the current script and returns focus to the Input area on the Work screen if a SQL command or PL/SQL block generates an error.

Examples

The commands in the following script cause iSQL*Plus to stop processing the current script and return focus to the Input area on the Work screen:

WHENEVER SQLERROR EXIT
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

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

Previous Page
Previous

Next Page
Next

Table Of Contents
Contents

Index
Index