|
|
|
@url[arg...]
Calls the specified script specified by the URL from a web server and runs the SQL*Plus statements in the script.
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
@@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.
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.
Executes the SQL command or PL/SQL block currently stored in the SQL buffer.
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 {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.
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 [type_name.attribute_name [option ...]]
where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
FOR[MAT] format
LIKE {type_name.attribute_name|alias}
ON|OFF
Specifies display characteristics for a given attribute of an Object Type column, such as format for NUMBER data.
Also lists the current display characteristics for a single attribute or all attributes.
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
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.
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.
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.
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'
CL[EAR] option ...
where option represents one of the following clauses:
BRE[AKS]
BUFF[ER]
COL[UMNS]
COMP[UTES]
SQL
TIMI[NG]
Resets or erases the current value or setting for the specified option.
To clear breaks, enter
CLEAR BREAKS
To clear column definitions, enter
CLEAR COLUMNS
COL[UMN] [{column|expr} [option ...]]
where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
ENTMAP {ON|OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|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]
Specifies display attributes for a given column, such as
Also lists the current display attributes for a single column or all columns.
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.
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.
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.
CONN[ECT] { logon | / } [AS {SYSOPER|SYSDBA}]
where logon requires the following syntax:
username/password[@connect_identifier]
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.
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.
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.
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.
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.
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.
DISC[ONNECT]
Commits pending changes to the database and logs the current username out of Oracle, but does not exit SQL*Plus.
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
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.
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|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.
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 [topic]
Accesses the SQL*Plus command line help system. Enter HELP INDEX for a list of topics.
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
L[IST] [n|n m|n *|n LAST|*|* n|* LAST|LAST]
Lists one or more lines of the SQL buffer.
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
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.
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
PRO[MPT] [text]
Sends the specified message or a blank line to the user's screen.
RECOVER {general | managed | END BACKUP}
where the general clause has the following syntax:
[AUTOMATIC] [FROM location]
{ {full_database_recovery | partial_database_recovery |LOGFILE filename}
[ {TEST | ALLOW integer CORRUPTION } [TEST | ALLOW integer CORRUPTION ]...]
|CONTINUE [DEFAULT]|CANCEL}
where the full_database_recovery clause has the following syntax:
[STANDBY] DATABASE
[ {UNTIL {CANCEL | TIME date | CHANGE integer} | USING BACKUP CONTROLFILE}
[UNTIL {CANCEL | TIME date | CHANGE integer} | USING BACKUP CONTROLFILE]...]
where the partial_database_recovery clause has the following syntax:
{TABLESPACE tablespace [, tablespace]... | DATAFILE datafilename [, datafilename]...
| STANDBY
{TABLESPACE tablespace [, tablespace]... | DATAFILE datafilename [, datafilename]...}
UNTIL [CONSISTENT] [WITH] CONTROLFILE }
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
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'
REM[ARK]
Begins a comment in a script. SQL*Plus does not interpret the comment as a command.
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;
REPF[OOTER] [PAGE] [printspec [text|variable] ...] | [ON|OFF]
where printspec represents one or more of the following clauses used to place and format the text:
COL n
S[KIP] [n]
TAB n
LE[FT]
CE[NTER]
R[IGHT]
BOLD
FORMAT text
Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition.
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
REPH[EADER] [PAGE] [printspec [text|variable] ...] | [ON|OFF]
where printspec represents one or more of the following clauses used to place and format the text:
COL n
S[KIP] [n]
TAB n
LE[FT]
CE[NTER]
R[IGHT]
BOLD
FORMAT text
Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition.
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
R[UN]
Lists and executes the SQL command or PL/SQL block currently stored in the SQL buffer.
Assume the SQL buffer contains the following script:
SELECT DEPARTMENT_ID FROM EMP_DETAILS_VIEW WHERE SALARY>12000
To RUN the script, enter
RUN
1 SELECT DEPARTMENT_ID
2 FROM EMP_DETAILS_VIEW
3 WHERE SALARY>12000
DEPARTMENT_ID
-------------
90
90
90
80
80
20
6 rows selected.
Sets a system variable to alter the SQL*Plus environment settings for your current session, for example:
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.
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.
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
Sets the number of rows--called a batch--that SQL*Plus will fetch from the database at one time.
Controls when Oracle commits pending changes to the database.
Sets the automatic printing of bind variables.
ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.
To set the recovery mode to AUTOMATIC, enter
SET AUTORECOVERY ON RECOVER DATABASE
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE).
Sets the character used to end PL/SQL blocks to c.
Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.
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.
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.
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
Specifies the version of Oracle to which you are currently connected.
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.
Sets the character used to terminate a substitution variable reference when SQL*Plus would otherwise interpret the next character as a part of the variable name.
Controls the number of batches after which the COPY command commits changes to the database.
Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.
Sets the character used to prefix substitution variables to c.
Sets the depth of the level to which you can recursively describe an object.
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)
Controls whether the START command lists each command in a script as the command is executed.
Controls where on a page each report begins.
Defines the character used as the escape character.
If you define the escape character as an exclamation point (!), then
SET ESCAPE ! ACCEPT v1 PROMPT 'Enter !&1:'
displays this prompt:
Enter &1:
To set the escape character back to the default value of \ (backslash), enter
SET ESCAPE ON
Displays the number of records returned by a script when a script selects at least n records.
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
Controls printing of column headings in reports.
To suppress the display of column headings in a report, enter
SET HEADING OFF
If you then run a SQL SELECT command
SELECT LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID = 'AC_MGR';
the following output results:
Higgins 12000
To turn the display of column headings back on, enter
SET HEADING ON
Defines the character used as the heading separator character.
Changes the default instance for your session to the specified instance path.
To set the default instance to "PROD1" enter
DISCONNECT SET INSTANCE PROD1
To set the instance back to the default of local, enter
SET INSTANCE local
You must disconnect from any connected instances to change the instance.
Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
Sets the starting position from which CLOB and NCLOB data is retrieved and displayed.
To set the starting position from which a CLOB column's data is retrieved to the 22nd position, enter
SET LOBOFFSET 22
The CLOB data will wrap on your screen; SQL*Plus will not truncate until the 23rd character.
Specifies the location from which archive logs are retrieved during recovery.
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
Sets maximum width (in bytes) for displaying LONG, CLOB and NCLOB values; and for copying LONG values.
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.
Sets the size (in bytes) of the increments SQL*Plus uses to retrieve a LONG, CLOB or NCLOB value.
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.
Outputs HTML marked up text, which is the output used by iSQL*Plus.
Sets the text that represents a null value in the result of a SQL SELECT command.
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.
Sets the default width for displaying numbers. For number format descriptions, see the FORMAT clause of the COLUMN command.
Sets the number of lines in each page.
Defines the character to display or print to separate records.
RECSEP tells SQL*Plus where to make the record separation.
Controls whether to display the output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
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
Converts the case of SQL commands and PL/SQL blocks just prior to execution.
Sets the behavior or output format of VARIABLE to that of the release or version specified by x.y[.z].
Sets the character used to end and execute SQL commands to c.
Controls the display of timing statistics.
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 "-".
Controls whether to list the text of a SQL statement or PL/SQL command before and after replacing substitution variables with values. ON lists the text; OFF suppresses the listing.
Controls whether to truncate the display of a selected row if it is too long for the current line width. OFF truncates the selected row; ON 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.
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.
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 [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL [LOCAL]]
Shuts down a currently running Oracle instance, optionally closing and dismounting a database.
To shutdown the database in normal mode, enter
SHUTDOWN Database closed. Database dismounted. Oracle instance shut down.
STA[RT] url[arg...]
Calls the script specified by the URL from a web server and runs the SQL*Plus statements in the script
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 options | migrate_options
where options has the following syntax:
[FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] |
[ OPEN [open_options] [dbname] ] |
NOMOUNT ]
where open_options has the following syntax:
READ {ONLY | WRITE [RECOVER]} | RECOVER
and where migrate_options has the following syntax:
[PFILE=filename] MIGRATE [QUIET]
Starts an Oracle instance with several options, including mounting and opening a database.
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
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.
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
TTI[TLE] [printspec [text|variable] ...] [ON|OFF]
where printspec represents one or more of the following clauses used to place and format the text:
COL n
S[KIP] [n]
TAB n
LE[FT]
CE[NTER]
R[IGHT]
BOLD
FORMAT text
Places and formats a specified 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.
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
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).
To undefine a user variable named POS, enter
UNDEFINE POS
To undefine two user variables named MYVAR1 and MYVAR2, enter
UNDEFINE MYVAR1 MYVAR2
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.
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
{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.
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
{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.
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