Appendix A 3 ROWIDTOCHAR

991

ROWIDTOCHAR
ROWIDTOCHAR
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also CHARTOROWID

Syntax
ROWIDTOCHAR (r)

Variables in the syntax

r:

a variable of type ROWID.

Description
The ROWIDTOCHAR function converts the ROWID datatype to an 18-digit VARCHAR2 datatype.

Examples
SQL
SELECT ROWIDTOCHAR (ROWID) VARCHAR2 TYPE FROM EMPLOYEE; VARCHAR2 TYPE -----------------00000545.0001.0005 00000546.0000.0005

992

Appendix A 3 Command Reference

ROWTYPE_MISMATCH
ROWTYPE_MISMATCH
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also NO_DATA_FOUND, TOO_MANY_ROWS

Syntax
EXCEPTION WHEN ROWTYPE_MISMATCH THEN statement_1,...,statement_n

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
If the host cursor variable and PL/SQL cursor variable are a mismatch, PL/SQL will implicitly raise a pre-defined exception of ROWTYPE_MISMATCH. The corresponding Oracle error and SQLCODE values are ORA-06504 and -6504 respectively.

Examples
PL/SQL
BEGIN OPEN loan_cur; LOOP FETCH loan_cur INTO emp_rec; EXIT WHEN loan_cur%NOTFOUND; END LOOP; EXCEPTION WHEN ROWTYPE_MISMATCH THEN UPDATE APPLICATION_ERROR_TABLE SET ERROR = ROWTYPE_MISMATCH IN CURSOR ; END;

Appendix A 3 RPAD

993

RPAD
RPAD
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 10

Section Inserting Rows

See Also LPAD, REPLACE

Syntax
RPAD (x, n [,y])

Variables in the syntax

x: n:

a character or varchar2 variable that should be specified within single quotes a positive numeric variable

y: a character or varchar2 variable that should be specified within single quotes. This variable is optional. If not specified, the default is a space character.

Description
This function returns x after padding it to length n on the right side. The function returns x right-padded to length n with the sequence of characters in y. The argument n is the total length or the number of characters in the result set. If x is longer than n, this function trims the length of x to n.

Examples
PL/SQL
Var1:= RPAD (Oracle, 1);

SQL
SELECT RPAD (Oracle is a good , 25, database) Example of Right Padding FROM DUAL; Example of Right Padding ----------------------Oracle is a good database

994

Appendix A 3 Command Reference

RTRIM
RTRIM
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 10

Section Inserting Rows

See Also LTRIM

Syntax
RTRIM (x [,y])

Variables in the syntax

x:

a character or varchar2 variable that should be specified within single quotes

y: a character or varchar2 variable that should be specified within single quotes. This variable is optional. If not specified, the default is a space character.

Description
This function returns x after trimming all the characters that are specified in the set y. This function will remove all the characters from the string x on the right side until it reaches a character that does not belong to the y character set.

Examples
PL/SQL
Var1:= RTRIM (Oracle, Or);

SQL
SELECT RTRIM (Mother Theresa, The, The) Example of Right Trimming FROM DUAL; Example of Right ---------------Mother Theresa,

Appendix A 3 SAVE

995

SAVE
SAVE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also SET, EDIT, GET

Syntax
SAV[E] filename[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

Variables in the syntax

filename: ext:

the file within which you would like to save the buffers contents

the extension to the file, if applicable. The default extension is SQL.

Description
To save the buffers contents within a given file, use the SAVE command. If the file already exists and you would like to replace the files contents with the buffers contents, use the REPLACE option. If the file already exists and you would like to append the files contents with the buffers contents, use the APPEND option. If the file does not exist and you would like SQL*Plus to create the file with the given name, use the CREATE option.

Examples
SQL
SAVE LOANRPT SAVE [Link]

996

Appendix A 3 Command Reference

SAVEPOINT
SAVEPOINT
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also COMMIT, ROLLBACK, SET TRANSACTION, LOCK TABLE

Syntax
SAVEPOINT savepoint_work

Variables in the syntax

savepoint_work:

an undeclared identifier used to save the current mark within the current transaction

Description
Use the SAVEPOINT statement to save the current mark within the current transaction.

Examples
SQL
SAVEPOINT loan_changes; SAVEPOINT update_loan;

Appendix A 3 SELECT

997

SELECT
SELECT
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 9

Section Basic Query

See Also SELECT INTO, DECLARE CURSOR, FETCH

Syntax
SELECT [DISTINCT | ALL] {* | column1[, column2]...} FROM {table_1 | (subquery)} [alias] [, {table_2 | (subquery)} [alias]]... [WHERE condition] [CONNECT BY condition [START WITH condition] [GROUP BY expn] [HAVING expn] [{ UNION [ALL] | INTERSECT | MINUS } SELECT . . . ] [ ORDER BY [expn ] [ ASC | DESC] [ FOR UPDATE [OF [user.]table | view] column ] [NOWAIT]

Variables in the syntax

item_1,...,item_n:

Columns the SELECT statement will select Tables within the database from which the data will be

table_1,...,table_n:

selected
sub_query:

a SELECT statement providing values, or set of values to the SELECT

statement
alias:

typically a short name for the Table, or view referenced within the SELECT statement.
column_1,...,column_n: will be updated where_condition:

specific Columns of the Table for which the given data

a valid WHERE clause

998

Appendix A 3 Command Reference

Description
Use the SELECT command to retrieve data from one or more Tables, views, and snapshots. To issue a SELECT command, you must have the SELECT privilege on the particular Table, view, or snapshot.

Examples
PL/SQL
SELECT FROM WHERE ORDER BY employee_id, employee_name, employee_title employees employee_id = emp_id employee_id;

Appendix A 3 SELECT INTO

999

SELECT INTO
SELECT INTO
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also SELECT, DECLARE CURSOR, FETCH

Syntax
SELECT [DISTINCT | ALL] {* | item_1[, item_2]...} INTO {variable_1[, variable_2]... | record_1} FROM {table_1 | (subquery)} [alias] [, {table_2 | (subquery)} [alias]]... rest_of_select_statement;

Variables in the syntax

item_1,...,item_n: items the SELECT INTO statement will select and save into the given variables variable_1,...,variable_n: record_1:

the variables into which the data will be selected

User defined record or %ROWTYPE record into which the SELECT INTO statement will fetch the rows of values
table_1,...,table_n:

Tables within the database from which the data will be

selected
sub_query: a SELECT statement providing values, or set of values to the SELECT INTO statement alias: typically a short name for the Table, or view referenced within the SELECT INTO statement. column_1,...,column_n: will be updated sql_expression:

specific Columns of the Table for which the given data

a valid SQL expression

1000

Appendix A 3 Command Reference

WHERE search_condition:

a valid WHERE clause specifying the rows that will be

updated
CURRENT OF cursor_name: the current row processed by the FETCH statement associated with the cursor specified by cursor_name rest_of_select_statement: within the SELECT statement

any legal statement following the FROM clause

Description
Use the SELECT INTO command to retrieve data from one or more Tables, views, and snapshots and then assign them to variables or fields. To issue a SELECT INTO command, you must have the SELECT privilege on the particular Table, view, or snapshot. Alternatively, you can use the SELECT INTO command with a cursor.

Examples
PL/SQL
SELECT employee_title INTO FROM WHERE employee_name, employee_id, e_name, e_id, e_title employees employee_id = emp_id;

Appendix A 3 SET

1001

SET
SET
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also EDIT, GET, SAVE

Syntax
SET variable value

Variables in the syntax

value:

the value for the system variable

variable:

the system variable whose value you would like SQL*Plus to set. The variable will be one of the following system variables.

Table A-21 Variables for the SQL*Plus SET command

Variable Syntax

APPI[NFO] ARRAY[SIZE] AUTO[COMMIT] AUTOP[RINT] AUTOT[RACE] EXP[LAIN] BLO[CKTERMINATOR] CLOSECUR[SOR] CMDS[EP]

{ON|OFF|text} {20|n} {OFF|ON|IMM[EDIATE]|n} {OFF|ON} {OFF|ON|TRACE[ONLY]} [STAT[ISTICS]] {.|c} {OFF|ON} {;|c|OFF|ON}

(continued)

1002

Appendix A 3 Command Reference

Table A-21 (continued)

Variable Syntax

COLSEP COM[PATIBILITY] CON[CAT] COPYC[OMMIT] COPYTYPECHECK CRT DEF[INE] ECHO EDITF[ILE] EMBEDDED ESC[APE] FEED[BACK] FLAGGER FLU[SH] HEA[DING] HEADS[EP] LIN[ESIZE] LONG LONGC[HUNKSIZE] MAXD[ATA] NEWP[AGE] NULL NUMF[ORMAT] NUM[WIDTH] PAGES[IZE] PAU[SE] RECSEP RECSEPCHAR SERVEROUT[PUT]

{_|text} {V6|V7|NATIVE} {.|c|OFF|ON} {0|n} {OFF|ON} crt {&|c|OFF|ON} {OFF|ON} file_name[.ext] {OFF|ON} {\|c|OFF|ON} {6|n|OFF|ON} {OFF|ENTRY|INTERMED[IATE]|FULL} {OFF|ON} {OFF|ON} {||c|OFF|ON} {80|n} {80|n} {80|n} n {1|n} text format {10|n} {24|n} {OFF|ON|text} {WR[APPED]|EA[CH]|OFF} {_|c} {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED]| WOR[D_WRAPPED]|TRU[NCATED]}]

Appendix A 3 SET

1003

Variable

Syntax

SHOW[MODE] SQLC[ASE] SQLCO[NTINUE] SQLN[UMBER] SQLPRE[FIX] SQLP[ROMPT] SQLT[ERMINATOR] SUF[FIX] TAB TERM[OUT] TI[ME] TIMI[NG] TRIM[OUT] TRIMS[POOL] UND[ERLINE] VER[IFY] WRA[P]

{OFF|ON} {MIX[ED]|LO[WER]|UP[PER]} {> |text} {OFF|ON} {#|c} {SQL>|text} {;|c|OFF|ON} {SQL|text} {OFF|ON} {OFF|ON} {OFF|ON} {OFF|ON} {OFF|ON} {ON|OFF} {-|c|ON|OFF} {OFF|ON} {OFF|ON}

Description
To specify and change system variables, use the SET command.

Examples
SQL
SET SET SET SET SET AUTOPRINT ON AUTOCOMMIT OFF APPINFO ON PAUSE Enter the last name of the customer TIMING OFF

1004

Appendix A 3 Command Reference

SET ROLE
SET ROLE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also ALTER SESSION, ALTER SYSTEM

Syntax
SET SET SET SET ROLE ROLE ROLE ROLE role_name IDENTIFIED BY password ALL ALL EXCEPT role_name NONE

Variables in the syntax

role_name: password:

the Role you would like to set within the current session the password for the Role you would like to set

Description
To enable/disable your Role within the current session, use the SET ROLE statement and provide the password. To enable all Roles within the current session, use the SET ROLE ALL statement. To enable all Roles except a given Role within the current session, use the SET
ROLE ALL EXCEPT statement.

To disable all Roles within the current session, use the SET ROLE NONE statement.

Examples
SQL
SET SET SET SET ROLE ROLE ROLE ROLE manager IDENTIFIED BY magic; ALL; ALL EXCEPT manager; NONE;

Appendix A 3 SET TRANSACTION

1005

SET TRANSACTION
SET TRANSACTION
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also COMMIT, ROLLBACK, SAVEPOINT, LOCK TABLE

Syntax
SET TRANSACTION { READ ONLY | READ WRITE | ISOLATION LEVEL {SERIALIZABLE | READ COMMITTED} | USE ROLLBACK SEGMENT rollback_segment_name}

Variables in the syntax

rollback_segment_name:

rollback segments name

Description
Use the SET TRANSACTION statement to begin a read-only, or read-write transaction. You can also use the SET TRANSACTION statement to assign the current transaction to the given rollback segment. You can assign a read-write transaction only to the given rollback segment. The transaction cannot be read-only.

Examples
SQL
SET TRANSACTION READ ONLY; SET TRANSACTION READ WRITE;

1006

Appendix A 3 Command Reference

SHOW
SHOW
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also SET

Syntax
SHO[W] option

Variables in the syntax

option: the option will assume one of the following clauses:

3 ALL 3 BTI[TLE] 3 ERR[ORS] 3 [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE

BODY|TRIGGER|VIEW}schema.]name]

3 LABEL 3 LNO 3 PNO 3 REL[EASE] 3 REPF[OOTER] 3 REPH[EADER] 3 SPOO[L] 3 SQLCODE 3 TTI[TLE] 3 USER

Appendix A 3 SHOW

1007

Description
To show the SQL*Plus system variables value, use the SHOW command.

Examples
SQL
SHOW SHOW SHOW SHOW SHOW SHOW USER LNO BTITLE LABEL LINESIZE ALL

1008

Appendix A 3 Command Reference

SIGN
SIGN
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

See Also ABS

Syntax
SIGN (x)

Variables in the syntax

x: a numeric variable

Description
The SIGN function returns the sign of the number. The following Table shows the result value in the three possible scenarios.

Table A-22 Results of SIGN Function on Ranges of Numbers

Value of x <0 0 >0 Result -1 0 1

Examples
PL/SQL
Var1:= SIGN (3);

Appendix A 3 SIGN

1009

SQL
SELECT SIGN (-5) SIGN OF -5 FROM DUAL; SIGN OF -5 ----------1

1010

Appendix A 3 Command Reference

SIN
SIN
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also COS, COSH, SINH, TAN, TANH

Syntax
SIN (n)

Variables in the syntax

n:

a numeric variable

Description
The SIN function will return the sine value for a number. The sine is a geometric term for an angle expressed in radians. The resultant value is accurate up to 36 digits.

Examples
PL/SQL
Var1:= SIN (20);

SQL
SELECT SIN (90) Sine of 90 degrees FROM DUAL; Sine of 90 degrees -----------------.89399666

Appendix A 3 SINH

1011

SINH
SINH
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also COS, COSH, SIN, TAN, TANH

Syntax
SINH (n)

Variables in the syntax

n:

a numeric variable

Description
The SINH function will return the hyperbolic sine value for a number. The resultant value is accurate up to 36 digits.

Examples
PL/SQL
Var1:= SINH (20);

SQL
SELECT SINH (20) Hyperbolic Sine of 20 deg FROM DUAL; Hyperbolic Sine of 20 deg ------------------------242582598

1012

Appendix A 3 Command Reference

SOUNDEX
SOUNDEX
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also

Syntax
SOUNDEX (x)

Variables in the syntax

x: a character or varchar2 variable that should be specified within single quotes.

Description
The function SOUNDEX is one of the most popular and powerful features of Oracle. SOUNDEX provides a method of comparing words that are spelled differently, but sound alike due to their phonetic representation. The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:

Table A-23 Algorithm for Comparing Words Using SOUNDEX

Step 1 2 3 4 Description retain the first letter of the string and remove the following letters: a, e, h, i, o, w, y assign the numbers to the remaining letters as follows: 0 = a, e, h, i, o, w, y 1 = b, f, p, v 2 = c, e, g, j, k, q, s, x, z 3 = d, t = 3 4 = l 5 = m, n r = 6 if two or more of the numbers are in sequences, remove all but the first return the first four bytes padded with 0

Appendix A 3 SOUNDEX

1013

Examples
SQL
SELECT DPL_NAME FROM DENIED_PARTIES_LIST WHERE SOUNDEX(DPL_NAME) = SOUNDEX(Saddam Hussain) ; DPL_NAME ---------------------------------------------Al Husseni Sadda Al Sada

1014

Appendix A 3 Command Reference

SPOOL
SPOOL
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 14

Section

See Also EDIT, SAVE

Syntax
SPO[OL] [filename[.ext] | OFF | OUT]

Variables in the syntax

filename: ext:

name of the file to which you wish to spool

the extension to the file, if applicable. The default extension is LST (or LIS).

Description
To spool the querys contents to a given file, use the SPOOL command. To turn off spooling, use the OFF option. To turn off spooling and direct the files contents to the printer, use the OUT option.

Examples
SQL
SPOOL REPORT SPOOL REPORT OUT SPOOL REPORT OFF

Appendix A 3 SQLERRM

1015

SQLERRM
SQLERRM
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also DECLARE EXCEPTION, EXCEPTION, EXCEPTION _INIT PRAGMA

Syntax
SQLERRM [n]

Variables in the syntax

n:

an integer value denoting the SQLCODE. This is optional.

Description
If the SQLCODE is not supplied, SQLERRM returns the error message related to the current SQLCODE. However, outside of an exception handler, this function without an integer argument will always return normal, successful completion. If the SQLCODE is supplied, SQLERRM returns the error message relating to that SQLCODE.

Examples
PL/SQL
error_message := SQLERRM (1401);

1016

Appendix A 3 Command Reference

SQLPLUS
SQLPLUS
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also

Syntax
SQLPLUS [[-S[ILENT]] [logon] [start]]|

Variables in the syntax

logon:

the UserID, password, and database information in the following format:

username[/password] [@database_specification]|/|/NOLOG start:

the command file you would like SQL*Plus to execute.

Description
To start SQL*Plus from the operating system prompt, use the SQLPLUS command. To start SQL*Plus from the operating system prompt and direct SQL*Plus to execute a command file that will take arguments, use the start option with the SQLPLUS command. To suppress the SQL*Plus information and messages, use the -S option with the SQLPLUS command.

Examples
SQL
SQLPLUS SQLPLUS SQLPLUS SQLPLUS john/cage -S john/cage -S john/cage@LOAN john/cage @LOAN_RPT

Appendix A 3 SQRT

1017

SQRT
SQRT
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

See Also EXP, POWER

Syntax
SQRT (n)

Variables in the syntax

n:

a positive numeric variable

Description
The SQRT function will return the square root of a number. Since the number for which you need a square root has to be positive, the result will always be a positive number. The resultant value is accurate up to 36 digits.

Examples
PL/SQL
Var1:= SQRT (ABS(Var2));

SQL
SELECT SQRT (4) Square Root of 4 FROM DUAL; Square Root of 4 ---------------2

1018

Appendix A 3 Command Reference

START
START
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also @, DEFINE

Syntax
STA[RT] filename[.ext] [arg_1,...,arg_N]

Variables in the syntax

filename: ext:

the command file you would like SQL*Plus to execute

the extension to the command file, if applicable. The default extension is SQL. the arguments to the command file, if applicable

arg_1,...,arg_N:

Description
To execute a command file, use the START command. By default, SQL*Plus will search for the command file within the current directory. If SQL*Plus does not find the command file within the current directory, SQL*Plus, in turn, will search within the system-dependent path to find the file.

Examples
SQL
START CALCSALARY 1 START LOAN_RPT START [Link]

Appendix A 3 STDDEV

1019

STDDEV
STDDEV
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also SUM, VARIANCE

Syntax
STDDEV ( [DISTINCT | ALL] expn)

Variables in the syntax

DISTINCT:

This option causes the function to consider only distinct values of the argument. This is optional.

ALL:

This option causes the function to consider all values of the argument including duplicate values. This is optional.

expn: This can be a Column of the SQL query or a mathematical expression with the Columns.

Description
The STDDEV function will return the standard deviation expn for a group of rows. The STDDEV function will calculate the standard deviation as the square root of the variance.

Examples
PL/SQL
Var1:= STDDEV (1, 2, 3);

SQL
SELECT STDDEV (DAILY_SALES) Standard Deviation FROM SALES; Standard Deviation -----------------3416.842

1020

Appendix A 3 Command Reference

STORAGE
STORAGE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 7, 12

Section Tablespaces

See Also CREATE CLUSTER, CREATE INDEX, CREATE ROLLBACK SEGMENT, CREATE SNAPSHOT, CREATE SNAPSHOT LOG, CREATE TABLE, CREATE TABLESPACE

Syntax
STORAGE ( [INITIAL n [K | M] ) [NEXT n [K | M] ] [PCTINCREASE n] [MINEXTENTS n] [MAXEXTENTS n] [OPTIMAL n [K | M] | NULL ] [FREELISTS n ] [FREELIST GROUP n ]

Variables in the syntax

n:

Any valid positive integer variable

Description
The storage clause is optionally used with CREATE and ALTER commands.
INITIAL allocates the first extent of space to the Object. By default, a Object uses 5 data blocks. NEXT is the size of the extent allocated after the initial extent has been filled. By default, it extents the size of the block by 5 data blocks. PCTINCREASE controls the rate of growth of extents beyond the second. If set to 0,

all additional extent will be of the size similar to the second block.

Appendix A 3 STORAGE

1021

MINEXTENTS allows you to specify more than 1 extent at the initialization stage of

the block.
MAXENTENTS sets the maximum number of extents that can be allocated to a Object. OPTIAML sets the optimal size in bytes for a rollback segment. FREELISTS sets the number of free lists for each free list group. FREELIST GROUPS defines the number of groups of free lists.

1022

Appendix A 3 Command Reference

STORAGE_ERROR
STORAGE_ERROR
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also PROGRAM_ERROR, NO_DATA_ FOUND, TOO_MANY_ROWS

Syntax
EXCEPTION WHEN STORAGE_ERROR THEN statement_1,...,statement_n

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
If the program runs out of memory, PL/SQL will implicitly raise a pre-defined exception of STORAGE_ERROR. The corresponding Oracle error and SQLCODE values are ORA-06500 and -6500 respectively.

Examples
PL/SQL
BEGIN OPEN loan_cur; LOOP FETCH loan_cur INTO loan_rec; EXIT WHEN loan_cur%NOTFOUND; END LOOP; EXCEPTION WHEN STORAGE_ERROR THEN UPDATE APPLICATION_ERROR_TABLE SET ERROR = STORAGE ERROR ; END;

Appendix A 3 STORE

1023

STORE
STORE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also SAVE, GET

Syntax
STORE {SET} filename[.ext] [CRE[ATE]|REP[LACE]| APP[END]]

Variables in the syntax

filename: the name of the file within which you would like to store the SQL*Plus environments attributes ext:

the extension to the file, if applicable

Description
To store the SQL*Plus environments settings within an operating system file, use the STORE command. If the file already exists and you would like SQL*Plus to replace the files contents with the environments current settings, use the REPLACE option. If the file already exists and you would like SQL*Plus to append the files contents with the environments current settings, use the APPEND option. If the file does not already exist and you would like SQL*Plus to create the file, use the CREATE option.

Examples
SQL
STORE STORE STORE STORE SET SET SET SET MYENV [Link] APPEND MYENV CREATE [Link] REPLACE

1024

Appendix A 3 Command Reference

SUBSTR
SUBSTR
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also SUBSTRB

Syntax
SUBSTR (x [,y, z])

Variables in the syntax

x: y:

a character or varchar2 variable that should be specified within single quotes.

a numeric variable that refers to the starting point of replacing the string. This is optional. Also, if you specify this value as 0, it is treated as 1.

z:

a numeric variable that refers to the total number of characters that need to be replaced. This is optional.

Description
The function SUBSTR returns a portion of x beginning at y and is z characters long. If z is negative or is null, the function returns a null value. If y is negative, Oracle starts the count in the backward direction. If y and z are in decimal, they are converted to integers.

Examples
PL/SQL
Var1:= SUBSTR (Oracle, 1, 3);

SQL
SELECT SUBSTR (Oracle, 1, 3) Substr FROM DUAL; Sub --Ora

Appendix A 3 SUBSTRB

1025

SUBSTRB
SUBSTRB
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also SUBSTR

Syntax
SUBSTRB (x [,y, z])

Variables in the syntax

x: y:

a character or varchar2 variable that should be specified within single quotes.

a numeric variable that refers to the starting point of replacing the string in bytes. This is optional. Also, if you specify this value as 0, it is treated as 1.

z:

a numeric variable that refers to the total number of characters that need to be replaced in bytes. This is optional.

Description
The function SUBSTRB is identical to SUSTR except that in here, y and z are expressed in bytes. The function SUBSTR returns a portion of x beginning at y and is z characters long. If z is negative or is null, the function returns a null value. If y is negative, Oracle starts the count in the backward direction. If y and z are in decimal, they are converted to integers. For a single-byte database character set, SUBSTRB is equivalent to SUBSTR.

1026

Appendix A 3 Command Reference

Examples
PL/SQL
Var1:= SUBSTRB (Oracle, 1, 3);

SQL
SELECT SUBSTRB (Oracle, 1, 3) Substr FROM DUAL; Sub --Ora

Appendix A 3 SUM

1027

SUM
SUM
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 13

Section Group by

See Also AVG, MAX, MIN, STDDEV, VARIANCE

Syntax
SUM ( [DISTINCT | ALL] expn)

Variables in the syntax

DISTINCT:

This option causes the function to consider only distinct values of the argument. This is optional.

ALL:

This option causes the function to consider all values of the argument including duplicate values. This is optional.

expn: This can be a Column of the SQL query or a mathematical expression with the Columns.

Description
The SUM function will return the sum of all the values of expn.

Examples
PL/SQL
Var1:= SUM (1, 2, 3);

SQL
SELECT SUM (DAILY_SALES) SUM FROM SALES; SUM -------------1212.1221

1028

Appendix A 3 Command Reference

SYSDATE
SYSDATE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 14

Section

See Also PSEUDOCOLUMN

Syntax
SYSDATE

Description
The SYSDATE function is a very handy function that returns the system date and time.

Examples
SQL
SELECT SYSDATE THE DATE TODAY FROM DUAL; THE DATE --------09-NOV-97

Appendix A 3 TABLE

1029

TABLE
TABLE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also RECORD

Syntax
TYPE type_name IS TABLE OF { column_type | variable%TYPE | [Link]%TYPE } [NOT NULL] INDEX BY BINARY_INTEGER ;

Variables in the syntax

type_name:

a type specifier used within subsequent declarations of PL/SQL Tables any scalar datatype such as CHAR, DATE, or NUMBER

column_type:

Description
A PL/SQL Table is a composite datatype modeled as database Tables. PL/SQL Tables use a Primary Key for array-like access to rows. The size of a PL/SQL Table is unconstrained, (the Table size grows as rows are added to the Table). PL/SQL Tables can have one Column and a Primary Key; neither of these can be named.

Examples
PL/SQL
DECLARE TYPE CnameTabTyp IS TABLE OF CHAR(10) INDEX BY BINARY_INTEGER; Cname_tab CnameTabType; BEGIN Cname_tab (1) := LARRY; END;

1030

Appendix A 3 Command Reference

TAN
TAN
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also COS, COSH, SIN, SINH, TANH

Syntax
TAN (n)

Variables in the syntax

n:

a numeric variable

Description
The TAN function will return the tangent value for a number. The tangent is a geometric term for an angle expressed in radians. The resultant value is accurate up to 36 digits.

Examples
PL/SQL
Var1:= TAN (20);

SQL
SELECT TAN (20) Tangent of 20 degrees FROM DUAL; Tangent of 20 degrees --------------------2.2371609

Appendix A 3 TANH

1031

TANH
TANH
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also COS, COSH, SIN, SINH, TAN

Syntax
TANH (n)

Variables in the syntax

n:

a numeric variable

Description
The TANH function will return the hyperbolic tangent value for a number. The resultant value is accurate up to 36 digits.

Examples
PL/SQL
Var1:= TANH (20);

SQL
SELECT TANH (20) Hyperbolic Tangent of 20 deg FROM DUAL; Hyperbolic Tangent of 20 deg ---------------------------1

1032

Appendix A 3 Command Reference

TIMEOUT_ON_RESOURCE
TIMEOUT_ON_RESOURCE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also NO_DATA_FOUND, TOO_ MANY_ROWS

Syntax
EXCEPTION WHEN TIMEOUT_ON_RESOURCE THEN statement_1,...,statement_n

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
If a timeout occurs when Oracle is waiting for a resource, PL/SQL will implicitly raise a pre-defined exception of PROGRAM_ERROR. The corresponding Oracle error and SQLCODE values are ORA-00051 and -51 respectively.

Examples
PL/SQL
BEGIN OPEN loan_cur; LOOP FETCH loan_cur INTO loan_rec; EXIT WHEN loan_cur%NOTFOUND; END LOOP; EXCEPTION WHEN TIMEOUT_ON_RESOURCE THEN UPDATE APPLICATION_ERROR_TABLE SET ERROR = TIMEOUT ON RESOURCE ; END;

Appendix A 3 TIMING

1033

TIMING
TIMING
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also CLEAR, SET

Syntax
TIMI[NG] [START text|SHOW|STOP]

Variables in the syntax

text:

the name for the timer

Description
To start or stop a timer, use the TIMER command. To start a timer, use the START option with the text that will represent the timers name. To list the timers name and data, use the SHOW option. To list the timers name and data and delete the timer, use the STOP option.

Examples
SQL
TIMING START new_timer TIMING SHOW TIMING STOP

1034

Appendix A 3 Command Reference

TO_CHAR (date)
TO_CHAR (date)
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 14

Section

See Also TO_DATE, FORMAT Date

Syntax
TO_CHAR (d [, x, nls_parm])

Variables in the syntax

d:

a valid date variable

x: a character variable specified in quotes that denotes the format of the date returned. This is optional. nls_parm:

specifies the language in which year, month, and day names are returned. This argument is optional. By default, it would take the default date language. This argument can be specified as NLS_DATE_LANGUAGE = language.

Description
The TO_CHAR (date) function converts the date d in VARCHAR2 datatype with the format as specified by x. It is optional to provide the format. If the format is not provided, the function converts the date to a VARCHAR2 type in the default date format (DD-MON-YY).

Examples
PL/SQL
FISCAL_YEAR:= TO_CHAR (SYSDATE,YEAR);

Appendix A 3 TO_CHAR (date)

1035

SQL
SELECT TO_CHAR (SYSDATE,YEAR) CURRENT_FISCAL_YEAR FROM DUAL; CURRENT_FISCAL_YEAR ----------------------NINETEEN NINETY-SEVEN

1036

Appendix A 3 Command Reference

TO_CHAR (label)
TO_CHAR
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also TO_LABEL

Syntax
TO_CHAR (mls [, x])

Variables in the syntax

mls:

a valid MLSLABEL variable

x: a character variable specified in quotes that denotes the format of the character string returned. This is optional.

Description
The TO_CHAR (label) function converts the MLSLABEL mls in VARCHAR2 datatype with the format as specified by x. It is optional to provide the format. If the format is not provided, the function converts the MLSLABEL to a VARCHAR2 in the default format. The MLSLABEL datatype is used with Trusted Oracle.

Appendix A 3 TO_CHAR (number)

1037

TO_CHAR (number)
TO_CHAR
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 14

Section

See Also TO_NUMBER, FORMAT NUMBER

Syntax
TO_CHAR (n [, x, nls_parm])

Variables in the syntax

n:

a valid number variable

x: a character variable specified in quotes that denotes the format of the number returned. This is optional. nls_parm: specifies the language in the number is returned. This argument is optional. This argument can be specified as follows: NLS_NUMERIC_CHARACTERS = ,. NLS_CURRENCY = $

Description
The TO_CHAR (number) function converts the number n in VARCHAR2 datatype with the format as specified by x. It is optional to provide the format. If the format is not provided, the function converts the number to a VARCHAR2 type exactly as it is.

Examples
PL/SQL
FISCAL_YEAR:= TO_CHAR (786);

1038

Appendix A 3 Command Reference

SQL
SELECT TO_CHAR(786,L99G999D99MI,NLS_NUMERIC_CHARACTERS = ,. NLS_CURRENCY = $ ) Net Payable FROM DUAL; Net Payable -------------------$786,00

Appendix A 3 TO_DATE (char)

1039

TO_DATE (char)
TO_DATE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 14

Section

See Also TO_CHAR, TO_NUMBER, FORMAT DATE

Syntax
TO_DATE (s [, x, nls_parm])

Variables in the syntax

s:

a CHAR or VARCHAR2 variable specified in quotes

x: a character variable specified in quotes that denotes the format of the date returned. This is optional. nls_parm:

specifies the language in which year, month, and day names are returned. This argument is optional. By default, it would take the default date language. This argument can be specified as NLS_DATE_LANGUAGE = language.

Description
The TO_DATE (char) function converts a string to DATE datatype with the format as specified by x. It is optional to provide the format. If the format is not provided, the function converts the string to a date in the default date format, that is DD-MON-YY.

Examples
PL/SQL
FISCAL_YEAR:= TO_DATE (26-JAN-47);

1040

Appendix A 3 Command Reference

SQL
SELECT TO_DATE (01/26/47, mm/dd/yy) REPUBLIC DAY FROM DUAL; REPUBLIC --------26-JAN-47

Appendix A 3 TO_LABEL (char)

1041

TO_LABEL (char)
TO_LABEL
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also TO_CHAR

Syntax
TO_LABEL (s [, x])

Variables in the syntax

s:

a valid CHAR or VARCHAR2 variable specified in quotes

x: a character variable specified in quotes that denotes the format of the label returned. This is optional.

Description
The TO_LABEL (char) function converts the VARCHAR2 datatype to an MLSLABEL datatype with the format as specified by x. It is optional to provide the format. If the format is not provided, the function converts the VARCHAR2 to a LABEL in the default label format. The MLSLABEL datatype is used with Trusted Oracle.

1042

Appendix A 3 Command Reference

TO_MULTI_BYTE (char)
TO_MULTI_BYTE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also TO_SINGLE_BYTE

Syntax
TO_MULTI_BYTE (s)

Variables in the syntax

s: a valid CHAR or VARCHAR2 variable specified in quotes

Description
The TO_MULTI_BYTE (char) function returns the string after converting all the single-byte characters to multi-byte. Any single-byte character that does not have a multi-byte equivalent appears as it is. This function is useful in the case where you have both the single-byte and multibyte character set in your database.

Appendix A 3 TO_NUMBER (char)

1043

TO_NUMBER (char)
TO_NUMBER
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also TO_CHAR, TO_DATE, FORMAT NUMBER

Syntax
TO_NUMBER (s [, x, nls_parm])

Variables in the syntax

s:

a CHAR or VARCHAR2 variable specified in quotes

x: a character variable specified in quotes that denotes the format of the number returned. This is optional. nls_parm: specifies the characters that need to be returned with the value. This argument is optional. By default, it would take the default numeric format. This argument can be specified as follows: NLS_NUMERIC_CHARACTERS = ,. NLS_CURRENCY = $

Description
The TO_NUMBER (char) function converts a string to NUMBER datatype with the format as specified by x. It is optional to provide the format. If the format is not provided, the function converts the string to a number in the default number format.

Examples
PL/SQL
FISCAL_YEAR:= TO_NUMBER (1947);

1044

Appendix A 3 Command Reference

SQL
SELECT TO_NUMBER (1947) FISCAL_YEAR FROM DUAL; FISCAL_YEAR ----------1947

Appendix A 3 TO_SINGLE_BYTE (char)

1045

TO_SINGLE_BYTE (char)
TO_SINGLE_BYTE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also TO_MULTI_BYTE

Syntax
TO_SINGLE_BYTE (s)

Variables in the syntax

s:

a valid CHAR or VARCHAR2 variable specified in quotes

Description
The TO_SINGLE_BYTE (char) function returns the string after converting all the multi-byte characters to single-byte. Any multi-byte character that does not have a single-byte equivalent appears as it is. This function is useful in the case where you have both the single-byte and multi-byte character set in your database.

1046

Appendix A 3 Command Reference

TOO_MANY_ROWS
TOO_MANY_ROWS
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also NO_DATA_FOUND

Syntax
EXCEPTION WHEN TOO_MANY_ROWS THEN statement_1,...,statement_n

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
If the SELECT INTO statement returns more than one row, PL/SQL will implicitly raise a pre-defined exception of TOO_MANY_ROWS. The corresponding Oracle error and SQLCODE values are ORA-01422 and -1422 respectively.

Examples
PL/SQL
DECLARE total_salary NUMBER; BEGIN SELECT salary INTO total_salary FROM employee; EXCEPTION WHEN TOO_MANY_ROWS THEN UPDATE APPLICATION_ERROR_TABLE SET ERROR = MORE THAN 1 ROW FOR THE INTO VARIABLE ; END;

Appendix A 3 TRANSLATE

1047

TRANSLATE
TRANSLATE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also REPLACE

Syntax
TRANSLATE (x [,y,z])

Variables in the syntax

x: a character or varchar2 variable that should be specified within single quotes. This is the variable in which you want to replace characters. y: a character or varchar2 variable that should be specified within single quotes. This refers to the characters you want to replace. z: a character or varchar2 variable that should be specified within single quotes. This refers to the characters you want to replace.

Description
The function TRANSLATE replaces all occurrences of each character in y with the corresponding characters in z. Characters in the original string x that are not in y are not replaced. The y argument can contain more characters than in z. In this case, the TRANSLATE function will remove these extra characters from the original string x. You cannot use an empty string in z to remove the y characters. In this case, the function would assume the empty string to be null and return a null value.

Examples
PL/SQL
Var1:= TRANSLATE (Oracle, Or, Mir,);

1048

Appendix A 3 Command Reference

SQL
SELECT TRANSLATE (Oracle, Or, Mir,); Example of translating strings FROM DUAL; Example of translating strings ---------------------------Miracle

Appendix A 3 TRUNC (date)

1049

TRUNC (date)
TRUNC (date)
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also ROUND

Syntax
TRUNC (d [, x])

Variables in the syntax

d:

a valid date variable

x: a character variable specified in quotes that denotes the format of the date returned

Table A-24 Formats for the TRUNC (date) command

Format Result Century

CC, SCC

SYYYY, YYYY, YEAR, Year (rounded as on July 1) SYEAR, YYY, YY, Y IYYY, IY, IY, I Q MONTH, MON, MM, RM WW IW
ISO Year Quarter (rounded up on 16th day of the second month of the quarter) Month (rounded up on 16th day) Rounded to the day of the week as the first day of the year Rounded to the day of the week as the first day of the ISO year
(continued)

1050

Appendix A 3 Command Reference

Table A-24 (continued)

Format Result Rounded to the day of the week as the first day of the month Rounded up to the day Rounded to the start day of the week Rounded to the hour Rounded to the minute

W DDD, DD, J DAY, DY, D HH, HH12, HH24 MI

Description
The TRUNC function returns the date d in the format as specified by x. TRUNC returns the date with the time truncated to the unit specified in x. It is optional to provide the format. If the format is not provided, the function rounds off the date to the nearest day.

Examples
PL/SQL
FISCAL_YEAR:= TRUNC (SYSDATE,YEAR);

SQL
SELECT TRUNC (SYSDATE,YEAR) CURRENT_FISCAL_YEAR FROM DUAL; CURRENT_F --------01-JAN-97

Appendix A 3 TRUNC (number)

1051

TRUNC (number)
TRUNC (number)
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

See Also ROUND

Syntax
TRUNC (x [, y])

Variables in the syntax

x: y:

numeric variable numeric variable (optional)

Description
The TRUNC function will truncate the value of x to the y places right of the decimal. If y is negative, this function truncates the value left of the decimal. If y is omitted, the function will truncate the value of x to 0 decimals.

Examples
PL/SQL
Var1:= TRUNC (124.1666, 2);

SQL
SELECT TRUNC (124.16666, -2) Rounded Value FROM DUAL; Rounded Value ------------100 SELECT TRUNC (124.16666, 2) Rounded Value FROM DUAL; Rounded Value ------------124.16

1052

Appendix A 3 Command Reference

TRUNCATE
TRUNCATE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also DELETE, DROP TABLE

Syntax
TRUNCATE [TABLE | CLUSTER] schema.[table][cluster] [DROP | REUSE STORAGE]

Variables in the syntax

table:

the Table from which you would like to remove the rows the cluster from which you would like to remove the rows the Schema containing the Table, or cluster

cluster: schema:

Description
To remove all the rows from a Table or cluster, use the TRUNCATE statement. In addition, if you would like to REUSE the storage space Oracle 8 allocated for the deleted rows from the Table or cluster, use the REUSE STORAGE option. If you would like to DROP the storage space Oracle 8 allocated for the deleted rows from the Table or cluster, use the DROP STORAGE option.

Examples
SQL
TRUNCATE TABLE inventory; TRUNCATE CLUSTER marketing DROP STORAGE; TRUNCATE TABLE loan REUSE STORAGE;

Appendix A 3 TTITLE

1053

TTITLE
TTITLE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 14

Section

See Also BTITLE, REPHEADER, REPFOOTER

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

Variables in the syntax

printspec: printspec will include one of the following clauses that SQL*Plus will

use to place and format the title: 3 COL n 3 S[KIP] [n] 3 TAB n 3 LE[FT] 3 CE[NTER] 3 R[IGHT] 3 BOLD 3 FORMAT text
text:

the titles text the variable that will contain one of the following system maintained

variable:

values: 3 [Link] (current line number) 3 [Link] (current page number) 3 [Link] (current Oracle release number)

1054

Appendix A 3 Command Reference

3 [Link] (current error code) 3 [Link] (current Username)

Description
To place the given title at the top of the reports every page, use the TTITLE command. To turn the TTITLE definition off, use the OFF option. To turn the TTITLE definition on, use the ON option.

Examples
SQL
TTITLE LEFT ADVENTURE REPORT TTITLE OFF TTITLE ON

Appendix A 3 UID

1055

UID
UID
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also USER

Syntax
UID

Variables in the syntax

None.

Description
The UID function returns a unique integer that identifies the current User.

Examples
SQL
SELECT UID FROM DUAL; UID ----33

1056

Appendix A 3 Command Reference

UNDEFINE
UNDEFINE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 14

Section

See Also DEFINE, START

Syntax
UNDEF[INE] variable_1 ... variable_n

Variables in the syntax

variable_1 ... variable_n:

the list of variables you would like SQL*Plus to

delete

Description
To delete the variables you defined by using the DEFINE or START command, use the UNDEFINE command.

Examples
SQL
UNDEFINE myVar1 UNDEFINE loan_bal acct_bal UNDEFINE myVar1 myVar2 myVar3

Appendix A 3 UNION

1057

UNION
UNION
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 9

Section UNION

See Also UNION ALL, INTERSECT, MINUS

Syntax
b1 UNION b2

Variables in the syntax

b1 and b2 are SELECT statements

Description
The UNION is a set operator that combines the results of two SELECT statements. The UNION will provide a query result that has rows selected by both b1 and b2 SQL. Any duplicate rows between b1 and b2 will be eliminated.

Examples
SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE = 09-FEB-96 UNION SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE = 09-FEB-97 ; SHIPPING_T ---------699.95 122.95

1058

Appendix A 3 Command Reference

UNION ALL
UNION ALL
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 9

Section UNION ALL

See Also UNION, INTERSECT, MINUS, SELECT

Syntax
b1 UNION ALLb2

Variables in the syntax

b1 and b2 are SELECT statements

Description
The UNION ALL is a set operator that combines the results of two SELECT statements. The UNION ALL will provide a query result that has rows selected by both b1 and b2 SQL. Any duplicate rows between b1 and b2 will not be eliminated.

Examples
SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE = 09-FEB-96 UNION ALL SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE = 09-FEB-97 ; SHIPPING_T ---------699.12 122.12 111.12 112.33

Appendix A 3 UPDATE

1059

UPDATE
UPDATE
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 10

Section Update

See Also INSERT, DELETE, SELECT, SELECT INTO

Syntax
UPDATE {table | (sub_query)} [alias] SET { column_name = {sql_expression | (sub_query)} | (column_name[, column_name]...) = (subquery)} [, { column_name = {sql_expression | (subquery)} | (column_name[, column_name]...) = (subquery)}]... [WHERE {search_condition | CURRENT OF cursor_name}];

Variables in the syntax

table: a Table within the database for which the User updating the data has UPDATE privileges sub_query:

a SELECT statement providing values or set of values to the UPDATE

statement
alias:

typically a short name for the Table, or view referenced within the UPDATE statement. Specify an alias if you would like to use the alias within the WHERE clause.
column_1,...,column_n:

specific Columns of the Table for which the given data

will be updated
sql_expression:

a valid SQL expression a valid WHERE clause specifying the rows that will be

WHERE search_condition:

updated
CURRENT OF cursor_name:

the current row processed by the FETCH statement associated with the cursor specified by cursor_name

1060

Appendix A 3 Command Reference

Description
Use the UPDATE command to change the values of given Columns in one or more rows within a Table or view. You can also use a sub query in an UPDATE statement. All the UPDATE Triggers on a Table would get fired when you fire the UPDATE statement. To issue an UPDATE command, you must have the UPDATE privilege on the particular Table or view.

Examples
SQL
UPDATE salary SET employee_salary = Total_Salary WHERE employee_number:= emp_id; UPDATE employees SET employee_active_flag = N WHERE employee_id in (SELECT employee_id FROM department_head WHERE department_active_flag = N);

Appendix A 3 UPPER

1061

UPPER
UPPER
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

See Also INITCAP, LOWER

Syntax
UPPER (x)

Variables in the syntax

x:

a character or varchar2 variable that should be specified within single quotes

Description
The function converts the string into uppercase. Words are delimited by white space or characters that are not alphanumeric. The function returns either a char variable or a varchar2 variable depending on the argument passed to it.

Examples
PL/SQL
Var1:= UPPER (oracle);

SQL
SELECT UPPER (oracle is a good database) UpperCase Sentence FROM DUAL; UpperCase Sentence ------------------------ORACLE IS A GOOD DATABASE

1062

Appendix A 3 Command Reference

USER
USER
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 6,11

Section Users

See Also UID, PSEUDOCOLUMN

Syntax
USER

Variables in the syntax

None.

Description
The USER function returns the current User name.

Examples
SQL
SELECT USER FROM DUAL; USER ----FED_REG

Appendix A 3 USERENV

1063

USERENV
USERENV
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also UID, USER

Syntax
USERENV (type)

Variables in the syntax

type: This argument takes any of the following options and must be specified in quotes.

Table A-25 Option specifications for USERENV command

Option Description Returns the current User sessions entry ID. Returns the current Users session ID. Returns the operating system identifier for the current User session. Returns TRUE if the current User session has the OSDBA Role assigned to him. Returns the current User session label. Returns the language and territory used by the current User session. Returns the value of the client_info field for the current User session. This value is set by the dbms_application_info.set_client_info Procedure. Returns the language used by the current User session in the 3 character ISO abbreviation.

ENTRYID SESSIONID TERMINAL OSDBA LABEL LANGUAGE CLIENT_INFO

LANG

1064

Appendix A 3 Command Reference

Description
The USERENV function returns information about the User environment. The return value depends on the type of parameter passed to the function.

Examples
SQL
SELECT USERENV(SESSIONID) SESSION ID FROM DUAL; SESSION ID --------------21323

Appendix A 3 VALUE_ERROR

1065

VALUE_ERROR
VALUE_ERROR
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also NO_DATA_FOUND, TOO_MANY_ROWS

Syntax
EXCEPTION WHEN VALUE_ERROR THEN statement_1,...,statement_n

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
If a truncation, arithmetic, conversion, or size Constraint error occurs, PL/SQL will implicitly raise a pre-defined exception of VALUE_ERROR. The corresponding Oracle error and SQLCODE values are ORA-06502 and -6502 respectively.

Examples
PL/SQL
DECLARE name VARCHAR2(10); total_salary NUMBER; BEGIN total_salary:= total_salary * name; EXCEPTION WHEN VALUE_ERROR THEN UPDATE APPLICATION_ERROR_TABLE SET ERROR = VALUE_ERROR INVALID MULTIPLICATION ; END;

1066

Appendix A 3 Command Reference

VARIABLE
VARIABLE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also ACCEPT, DEFINE

Syntax
VAR[IABLE] [variable [NUMBER|CHAR|CHAR (n)|VARCHAR2 (n)| REFCURSOR]]

Variables in the syntax

variable:

the name of the bind variable you would like to declare

Description
To declare a bind variable, use the VARIABLE command. To declare a bind variable of type NUMBER, use the NUMBER option. To declare a bind variable of type CHAR, use the CHAR option. To declare a bind variable of type CHAR with maximum length n (up to 255 characters), use the CHAR n option. To declare a bind variable of type VARCHAR2 with maximum length n (up to 255 characters), use the VARCHAR2 n option. To declare a bind variable of type REFCURSOR, use the REFCURSOR option.

Examples
SQL
VARIABLE quota NUMBER VARIABLE last_name CHAR(35) VARIABLE r REFCURSOR

Appendix A 3 VARIABLE ASSIGNMENT

1067

VARIABLE ASSIGNMENT
VARIABLE ASSIGNMENT
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

See Also DECLARE, NULL

Syntax
a:= 5

Variables in the syntax

a:

variable to which PL/SQL will assign a value specified within the RHS of the assignment statement

Description
By using the assignment operator, you can assign values to the variables.

Examples
PL/SQL
Total_Salary:= Base_Salary + Commission + Bonus;

1068

Appendix A 3 Command Reference

VARIANCE
VARIANCE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also SUM, STDDEV, VARIANCE

Syntax
VARIANCE ( [DISTINCT | ALL] expn)

Variables in the syntax

DISTINCT:

This option causes the function to consider only distinct values of the argument. This is optional.

ALL:

This option causes the function to consider all values of the argument including duplicate values. This is optional.

expn: This can be a Column of the SQL query or a mathematical expression with the Columns.

Description
The VARIANCE function will return the variance of expn.

Examples
PL/SQL
Var1:= VARIANCE (1, 2, 3);

SQL
SELECT VARIANCE (DAILY_SALES) VARIANCE FROM SALES; VARIANCE -------------34.12

Appendix A 3 VSIZE

1069

VSIZE
VSIZE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also LENGTH

Syntax
VSIZE (v)

Variables in the syntax

v:

any variable or value

Description
The VSIZE function returns the storage size of v. In case of character values, VSIZE is equal to the length of v.

Examples
SQL
SELECT VSIZE (ORACLE) VSIZE FROM DUAL; VSIZE -------6 SELECT VSIZE (21.543) VSIZE FROM DUAL; VSIZE -------4

1070

Appendix A 3 Command Reference

RESERVED WORDS
RESERVED WORDS
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also

Description
Oracle has a list of words that are reserved and cannot be used by the programmer while declaring any Object in the database like Table, view, or even as a variable in the functions and Procedures. A list of the reserved words is as follows:

Table A-26 Alphabetical List of Reserved Words in Oracle8

Reserved Word

ACCESS AND AUDIT BETWEEN CHAR COMMENT CURRENT DATE DESC ELSE FILE GRANT HAVING

ADD ANY

ALL AS

ALTER ASC

BY CHECK COMPRESS CLUSTER CONNECT COLUMN CREATE

DECIMAL DISTINCT EXCLUSIVE FLOAT GROUP

DEFAULT DROP EXISTS FOR

DELETE

FROM

Appendix A 3 RESERVED WORDS

1071

Reserved Word

IDENTIFIED INDEX INTERSECT LEVEL MAXEXTENTS NOAUDIT NULL OF OPTION PCTFREE RAW ROW ROWS SELECT SIZE SYNONYM TABLE UID USER VALIDATE VIEW WHENEVER

IMMEDIATE INITIAL INTO LIKE MINUS NOCOMPRESS NUMBER OFFLINE OR PRIOR RENAME ROWID

IN INSERT IS LOCK MODE NOT

INCREMENT INTEGER

LONG MODIFY NOWAIT

ON ORDER PRIVILEGES RESOURCE ROWLABEL

ONLINE

PUBLIC REVOKE ROWNUM

SESSION SMALLINT SYSDATE THEN UNION

SET START

SHARE SUCCESSFUL

TO UNIQUE

TRIGGER UPDATE

VALUES

VARCHAR

VARCHAR2

WHERE

WITH

1072

Appendix A 3 Command Reference

WHENEVER OSERROR
WHENEVER OSERROR
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also WHENEVER SQLERROR

Syntax
WHENEVER OSERROR {EXIT [SUCCESS|FAILURE|n|variable] [COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}

Variables in the syntax

n: the success, or failure code you would like SQL*Plus to return in the event of an

operating system failure

variable: the success, or failure variable you would like SQL*Plus to return in the

event of an operating system failure

Description
To exit SQL*Plus whenever an operating system will occur, use the WHENEVER OSERROR command. To turn off EXIT, use the CONTINUE option. To commit the pending changes to the database before EXIT or CONTINUE, use the COMMIT option. To cancel the pending changes to the database before EXIT or CONTINUE, use the
ROLLBACK option.

To neither commit nor cancel the pending changes to the database before
CONTINUE, use the NONE option.

Appendix A 3 WHENEVER OSERROR

1073

Examples
SQL
WHENEVER OSERROR EXIT COMMIT WHENEVER OSERROR EXIT retcode ROLLBACK

1074

Appendix A 3 Command Reference

WHENEVER SQLERROR
WHENEVER SQLERROR
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also WHENEVER OSERROR

Syntax
WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|n|variable] [COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}

Variables in the syntax

n:

the success, or failure code you would like SQL*Plus to return in the event of a SQL error

variable:

the success, or failure variable you would like SQL*Plus to return in the event of a SQL error

Description
To exit SQL*Plus whenever a SQL error occurs, use the WHENEVER SQLERROR command. To turn off EXIT, use the CONTINUE option. To commit the pending changes to the database before EXIT or CONTINUE, use the COMMIT option. To cancel the pending changes to the database before EXIT or CONTINUE, use the
ROLLBACK option.

To neither commit nor cancel the pending changes to the database before
CONTINUE, use the NONE option.

Appendix A 3 WHENEVER SQLERROR

1075

Examples
SQL
WHENEVER SQLERROR EXIT COMMIT WHENEVER SQLERROR EXIT FAILURE retcode ROLLBACK

1076

Appendix A 3 Command Reference

WHILE-LOOP
WHILE-LOOP
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also LOOP, EXIT, FOR-LOOP, EXIT-WHEN

Syntax
WHILE condition1 is true LOOP statement_1,...,statement_n; END LOOP

Variables in the syntax

condition_1:

a certain condition PL/SQL will evaluate

statement_1,...,statement_n: a sequence of statements PL/SQL will execute iteratively while condition_1 is true

Description
Use the WHILE-LOOP structure to execute a sequence of statements while the given condition is true.

Examples
PL/SQL
WHILE Total_Salary > 0 LOOP Total_Salary:= Base_Salary + Commission + Bonus; UPDATE salary SET employee_salary = Total_Salary WHERE employee_number:= emp_id; END LOOP;

Appendix A 3 ZERO_DIVIDE

1077

ZERO_DIVIDE
ZERO_DIVIDE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also INVALID_NUMBER

Syntax
EXCEPTION WHEN ZERO_DIVIDE THEN statement_1,...,statement_n

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
If you divide a number by 0, PL/SQL will implicitly raise a pre-defined exception of ZERO_DIVIDE. The corresponding Oracle error and SQLCODE values are ORA-01476 and -1476 respectively.

Examples
PL/SQL
DECLARE total_salary NUMBER; BEGIN total_salary:= total_salary / 0; EXCEPTION WHEN ZERO_DIVIDE THEN UPDATE APPLICATION_ERROR_TABLE SET ERROR = DIVISION BY ZERO INVALID ; END;