0% found this document useful (0 votes)

18 views222 pages

SQL CREATE Statements Overview

The document provides the syntax and description for the Oracle SQL command CREATE ROLLBACK SEGMENT. CREATE ROLLBACK SEGMENT is used to create a rollback segment, which is a database object that stores undo data to rollback transactions. The syntax specifies variables like the name of the rollback segment being created. Examples are provided showing how to create basic and named rollback segments.

Uploaded by

Arsalan Ahmed

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

18 views222 pages

SQL CREATE Statements Overview

Uploaded by

Arsalan Ahmed

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

CREATE INDEX

CREATE INDEX
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 7

Section Indexes

Variables in the syntax

user: index: table:

the User where you want to create the Index the name of the Index that you want to create the Table on which you want to create the Index the cluster on which you want to create the Index

cluster: n:

any positive integer value the Tablespace you want to use for the Index

tablespace:

Description
To create an Index, use the CREATE INDEX statement. You can use the CREATE INDEX statement only if you have the CREATE ANY INDEX system privilege.

770

Appendix A 3 Command Reference

You can further specify the values for INITRANS, MAXTRANS, and STORAGE parameters. With the ASC and DESC option, you can create the Index in an ascending or descending order. Providing multiple Columns in the Index will automatically create a composite Index in the order of the Columns specified.
NOSORT is an option whose Primary value is in reducing the time to create an Index,

if any, and only if, the values in the Column being Indexed are already in ascending order.

Examples
SQL
CREATE INDEX loan_application_indx ON loan (loan_id ASC) TABLESPACE temp; CREATE INDEX customer_indx ON loan (cust_id ASC, loan_id ASC) TABLESPACE temp;

Appendix A 3 CREATE LIBRARY

771

CREATE LIBRARY
CREATE LIBRARY
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

library_name:

library name from which SQL and PL/SQL will call external 3GL functions and Procedures. physical path and file name of the external file.

filename:

Description
To create a library or Schema Object, use the CREATE LIBRARY statement. The library Object is new with Oracle8. It is a great help to Oracle8 developers as it removes the pain of individually calling one DDL (Data Definition Language) command at a time and then compiling it. Instead, you can now use the library Object, which, in turn, will call a file that can have all the Table creation and stored programs to generate a Schema. You need to have the CREATE LIBRARY system privilege to CREATE a library Object.

Examples
SQL
CREATE LIBRARY ext_lib IS /lib/[Link]; CREATE OR REPLACE LIBRARY ext_lib2 AS /lib/[Link];

772

Appendix A 3 Command Reference

CREATE PACKAGE
CREATE PACKAGE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 19

Section Packages

Variables in the syntax

user:

the User where you want to create the Package the stored Package that you want to create on the database

package:

Description
To create the stored Package, use the CREATE PACKAGE statement. A Package is a database Object that groups logically related PL/SQL types, Objects, and subprograms (read Procedures and functions). Thus, a Package is an excellent

Appendix A 3 CREATE PACKAGE

773

way of sharing data via variables, constants, and cursors across different Procedures and functions. Oracle will access the entire elements of a Package more efficiently as it considers them as a single unit rather than if they were separate. A good idea would be to use the CREATE OR REPLACE command so that if the Package already exists, Oracle will replace it with the new Package. If you replace an existing Package, Oracle invalidates the Package body and you need to recompile the Package body.

Examples
SQL
CREATE OR REPLACE PACKAGE loan_approval AS Type LoanRecTyp IS RECORD (customer_id INTEGER, loan_amount REAL); CURSOR customer_history (customer_id NUMBER) RETURN LoanRecTyp; PROCEDURE approve_loan ( customer_id CHAR, loan_type CHAR, loan_amount CHAR); PROCEDURE cumulative_loan (loan_amount REAL); END loan_approval ;

774

Appendix A 3 Command Reference

CREATE PACKAGE BODY

CREATE PACKAGE BODY
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 19

Section Packages

See Also CREATE PACKAGE, ALTER PACKAGE BODY, DROP PACKAGE BODY

Syntax
CREATE [OR REPLACE] PACKAGE BODY [user.]package AS} { variable_declaration | cursor_body | exception_declaration | record_declaration | plsql_table_declaration | procedure_body | function_body } ; [{ variable_declaration | cursor_body | exception_declaration | record_declaration | plsql_table_declaration | procedure_body | function_body } ; ] ... END [package] { IS |

Variables in the syntax

user:

the User where you created the Package the stored Package that you created on the database

package:

Description
Use the CREATE PACKAGE BODY statement to specify the body (read as the actual PL/SQL Procedures and functions) which you specified with the CREATE PACKAGE statement.

Appendix A 3 CREATE PACKAGE BODY

775

A good idea would be to use the CREATE OR REPLACE command so that if the Package body already exists, Oracle will replace it with the new Package body. If you replace an existing Package, Oracle invalidates the Package body and you need to re-compile the Package body.

Examples
SQL
CREATE OR REPLACE PACKAGE BODY loan_approval AS CURSOR customer_history (customer_id NUMBER) RETURN LoanRecTyp IS SELECT customer_id FROM customer ; PROCEDURE approve_loan ( customer_id CHAR, loan_type CHAR, loan_amount REAL) IS BEGIN IF loan_amount < 10000 THEN UPDATE customer SET loan_type = A WHERE cust_id = customer_id; END IF; END approve_loan; PROCEDURE cumulative_loan (loan_amount REAL) IS BEGIN UPDATE total_outstanding SET loan_amt = loan_amt + loan_amount; END cumulative_loan; END approve_loan ;

776

Appendix A 3 Command Reference

CREATE PROCEDURE
CREATE PROCEDURE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 19

Section Procedure

Variables in the syntax

user:

the User where you want to create the Procedure the stored Procedure that you want to create on the database any argument that you want to pass to the Procedure the datatype of the argument respectively

procedure: parameter: datatype: block:

the block refers to the series of declarations, PL/SQL program statements, and exceptions that define the behavior of the Procedure

Description
To create the stored Procedure, use the CREATE PROCEDURE statement. A Procedure is a subprogram that performs a specific action. Procedures and functions are structured alike, except that functions have a RETURN value. A Procedure has two parts: the specification and the body. The Procedure specification begins with the keyword PROCEDURE and ends with the Procedure name or parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses.

Appendix A 3 CREATE PROCEDURE

777

The Procedure body begins with the keyword IS and ends with the keyword END followed optionally by the Procedure name. The Procedure body has three parts: a declarative part, an executable part, and an optional exceptional-handling part. The declarative part contains local declarations, which are placed between the keywords IS and BEGIN. The executable part contains statements, which are placed between the keywords BEGIN and EXCEPTION (or END). At least one statement must appear in the executable part of the Procedure. The NULL statement meets the requirement. The exception-handling part contains exception handlers, which are placed between the keywords EXCEPTION and END.
Tip

A good idea would be to use the CREATE OR REPLACE command so that if the function already exists, Oracle will replace it with the new Procedure.

Examples
SQL
CREATE PROCEDURE loan_calculation (loan_amount NUMBER, no_of_years NUMBER) AS Total_loan NUMBER; BEGIN Total_loan:= loan_amount * no_of_years; END loan_calculation ; CREATE OR REPLACE PROCEDURE increment (emp_id INTEGER, increment REAL) IS current_pay REAL; missing_pay EXCEPTION; BEGIN SELECT salary INTO current_pay FROM employee WHERE empno = emp_id; IF current_pay IS NULL THEN RAISE missing_pay; ELSE UPDATE emp SET salary = salary + increment WHERE empno = emp_id; END IF; EXCEPTION WHEN missing_pay THEN INSERT INTO emp_audit VALUES (emp_id, Incomplete Details); END increment ;

778

Appendix A 3 Command Reference

CREATE ROLE
CREATE ROLE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 6

Section Roles

Variables in the syntax

role: the Role that you want to create on the database

Description
To create a Role, use the CREATE ROLE statement. A Role is a set of privileges. When you grant the Role to a User, you basically grant the User all the privileges assigned to that Role. Use the CREATE ROLE command to create a Role and then use the GRANT command to grant the Role to the Users. You can use the CREATE ROLE statement only if you have the CREATE ANY ROLE system privilege.

Examples
SQL
CREATE ROLE custom_user ;

Appendix A 3 CREATE ROLLBACK SEGMENT

779

CREATE ROLLBACK SEGMENT

CREATE ROLLBACK SEGMENT
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 10

Section Inserting Rows

Variables in the syntax

rollback_segment: tablespace:

the rollback segment you would like to create

the Tablespace where you would like to create the rollback segment

Description
To crate a rollback segment, use the CREATE ROLLBACK SEGMENT statement. One TABLESPACE can have multiple rollback segments. If you use the PUBLIC option while creating the rollback segment, such a segment can be used by any instance that requests it, else it is available to only those instances as specified in their [Link] file.

Examples
SQL
CREATE ROLLBACK SEGMENT RB_HUMANRESOURCE STORAGE (INITIAL 50K NEXT 100K OPTIMAL 150K);

780

Appendix A 3 Command Reference

CREATE SCHEMA
CREATE SCHEMA
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

schema:

the Schema you want to create

Description
To create a Schema, use the CREATE SCHEMA statement. A Schema is a collection of Tables, views, and privilege grants that you want to create as a single transaction.

Examples
SQL
CREATE SCHEMA AUTHORIZATION cust_schema CREATE TABLE customer (CUSTOMER_ID NUMBER(4) NOT NULL, CUSTOMER_NAME VARCHAR2(50) NOT NULL, STREET_ADDRESS VARCHAR2(50) NOT NULL, CITY VARCHAR2(50) NOT NULL, STATE VARCHAR2(2) NOT NULL CHECK (FL, TX, MD) CUST_TYPE VARCHAR2(1) NOT NULL LOAN_AMOUNT NUMBER(6) ) | CREATE VIEW [Link] AS SELECT customer_id, customer_name, loan_amount FROM customer WHERE customer_type = L ;

Appendix A 3 CREATE SEQUENCE

781

CREATE SEQUENCE
CREATE SEQUENCE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 8

Section Sequences

Variables in the syntax

user:

the User where the sequence was created the sequence that you want to alter from the database

sequence: n:

any positive integer value

Description
To create a sequence from the database, use the CREATE SEQUENCE statement. The CREATE SEQUENCE allows you to set the following options for the sequence:

782

Appendix A 3 Command Reference

Table A-10 Options for the CREATE SEQUENCE statement

option
START WITH MINVALUE MAXVALUE CYCLE CACHE ORDER INCREMENT

description The number with which the sequence will begin. The lowest value the sequence can generate. The highest value the sequence can generate. This will restart the sequence number after reaching the MAXVALUE specified. This allows a pre-allocated set of sequence numbers to be kept in the memory. The default value is 20. This causes the sequence number to be assigned to instances requesting them in a serial order. This value specifies the number by which the sequence is incremented every time a new value is requested.

You can use the sequence to create Unique numbers that you can use in your Tables as Primary identifiers.

Examples
SQL
CREATE SEQUENCE customer_code_seq INCREMENT BY 1 START WITH 1; CREATE SEQUENCE loan_application_seq START WITH 100 INCREMENT WITH 100 NOMAXVALUE ;

Appendix A 3 CREATE SNAPSHOT

783

CREATE SNAPSHOT
CREATE SNAPSHOT
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

user:

the User where you want to create the snapshot the snapshot you would like to alter from the database

snapshot: n:

any positive integer value the Tablespace on which you would like to create the snapshot the date to start the snapshot process the date when to refresh the snapshot

tablespace: start_date: next_date: query:

the SQL SELECT statement that will form the snapshot

784

Appendix A 3 Command Reference

Description
To create a snapshot, use the CREATE SNAPSHOT statement. You can use the CREATE SNAPSHOT statement only if you have the CREATE ANY SNAPSHOT system privilege.
CREATE SNAPSHOT creates a snapshot. A snapshot is a virtual Table that holds the

results of a query. This query can have one or more Tables in a remote database. A snapshot is a read-only copy of the data on the local database to improve the speed for the queries on the Tables in the remote location. A FAST refresh option uses the SNAPSHOT LOG to refresh the snapshot. However, a FAST refresh can be done only on single Table query snapshots. A COMPLETE refresh option re-executes the query of the snapshot.

Examples
SQL
CREATE SNAPSHOT customer.inactive_cust_snapshot AS SELECT customer_id, customer_name, customer_address FROM customer@remote;

Appendix A 3 CREATE SNAPSHOT LOG

785

CREATE SNAPSHOT LOG

CREATE SNAPSHOT LOG
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

See Also CREATE SNAPSHOT, ALTER SNAPSHOT LOG, DROP SNAPSHOT LOG

Variables in the syntax

user: table: n:

the User where you want to create the snapshot log the Table on which you want to create the snapshot log

any positive integer value the Tablespace on which you would like to create the snapshot

tablespace:

Description
To create a snapshot log, use the CREATE SNAPSHOT LOG statement.
CREATE SNAPSHOT LOG creates a Table associated with the master Table of a snapshot to help track changes to the master Tables data.

ORACLE uses the SNAPSHOT LOG to do a FAST refresh on the SNAPSHOT. However, a FAST refresh can be done only on single Table query snapshots.

786

Appendix A 3 Command Reference

Examples
SQL
CREATE SNAPSHOT LOG ON [Link]@remote TABLESPACE temp ;

Appendix A 3 CREATE SYNONYM

787

CREATE SYNONYM
CREATE SYNONYM
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 8, 13

Section Synonyms

Variables in the syntax

user: table:

the User where you want to create the Synonym the Table for which you want to create the Synonym the database link that refers to the remote database

database_link:

Description
SYNONYM is an alias name for a Table or a view. To create a Synonym, use the CREATE SYNONYM statement.

The PUBLIC option makes the Synonym available to all the Users. However, to create a SYNONYM with the PUBLIC option, you must have the DBA system privileges.

Examples
SQL
CREATE SYNONYM r_cust FOR CUSTOMER@REMOTE_SITE;

788

Appendix A 3 Command Reference

CREATE TABLE
CREATE TABLE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 7, 12

Section Tables

Variables in the syntax

user: table: column:

the User where the Table is to be created the Table you would like to create from the database name of the Column

expn: the DEFAULT values, if any for the Column. These values will be used in case the INSERT omits the value for the Column. column_constraint:

The column_constraint defines the integrity Constraint as part of the Column definition. The Column Constraint can take the following values:

Appendix A 3 CREATE TABLE

789

Table A-11 Possible values in the column_constraint of the CREATE TABLE command
Value
NULL NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY REFERENCES ON DELETE CASCASE

Description Specifies that the Column can contain null values Specifies that the Column cannot contain null values Designates a Column or combination of Columns as Unique Key Designates a Column or combination of Columns as the Tables Primary Key Designates a Column or combination of Columns as the Foreign Key Identifies the Primary or Unique Key that is referenced by a Foreign Key Specifies that ORACLE automatically delete all dependent rows from other Table where the Column from this Table forms the Primary or Foreign Key Specifies a condition that each row in the Table must satisfy

CHECK

cluster: the cluster where you want to create the Table n:

any positive integer value a SQL SELECT statement that will be used to define the new Table

query:

Description
To create a Table on the the database, use the CREATE TABLE statement. You can use the CREATE TABLE statement only if you have the CREATE ANY TABLE system privilege.

Examples
SQL
CREATE TABLE customer (CUSTOMER_ID NUMBER(4) NOT NULL, CUSTOMER_NAME VARCHAR2(50) NOT NULL, STREET_ADDRESS VARCHAR2(50) NOT NULL, CITY VARCHAR2(50) NOT NULL, STATE VARCHAR2(2) NOT NULL CHECK (FL, TX, MD) ;

790

Appendix A 3 Command Reference

CREATE TABLESPACE
CREATE TABLESPACE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 7, 12

Section Tablespaces

Variables in the syntax

tablespace:

the Tablespace you would like to create on the database the name of the file with size in K or M

file_definition:

Description
To create a new Tablespace, use the CREATE TABLESPACE statement. The ONLINE option, which is the default, makes the Tablespace available as soon as it is created. On the contrary, the OFFLINE option keeps the Tablespace offline until the ALTER TABLESPACE command changes it to ONLINE.

Examples
SQL
CREATE TABLESPACE customer (DATAFILE [Link]);

Appendix A 3 CREATE TRIGGER

791

CREATE TRIGGER
CREATE TRIGGER
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 19

Section Triggers

Variables in the syntax

user:

the User where you want to create the Trigger the stored Trigger that you want to create from the database the Column from the Table that will fire the Trigger the database Table on which the Trigger will be fired

trigger: column: table: old: new:

the method of referencing the old values. By default, it is set to OLD. the method of referencing the new values. By default, it is set to NEW. the condition when the Trigger on the Table should be fired

when_condition: block:

the PL/SQL block that should be executed

792

Appendix A 3 Command Reference

Description
To create a database Trigger, use the CREATE TRIGGER statement. A database Trigger is a set of stored PL/SQL programs associated with a specific Table. This Table name is specified in the ON clause. ORACLE automatically executes this piece of PL/SQL block whenever the specified SQL statement is executed. A database Trigger has three parts: Triggering event, Trigger Constraint, and the Trigger action. By default, the database Trigger fires once per Table. However, you can use the FOR EACH ROW clause to force ORACLE to fire the Trigger for every row affected by the Triggering operation. You can restrict the execution of the Trigger to happen only on a select set of rows of the Table by specifying the WHEN condition. ORACLE allows up to 12 simultaneous Triggers on a Table. You can use the CREATE TRIGGER statement only if you have the CREATE TRIGGER or CREATE ANY TRIGGER system privilege.

Examples
SQL
CREATE OR REPLACE TRIGGER cumulative_loan_update AFTER INSERT OR UPDATE OF loan_amount ON customer FOR EACH ROW WHEN (new. loan_type = A) BEGIN UPDATE total_outstanding SET loan_amt = loan_amt + new.loan_amount; END;

Appendix A 3 CREATE TYPE

793

CREATE TYPE
CREATE TYPE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 24

Section Types

Variables in the syntax

schema:

the database Schema where you want to create the Object type the Object type that you want to create

type_name: size:

the upper limit of the VARRAY size any datatype such as CHAR, DATE, NUMBER, and more name of the Object type referred to name of the member function

datatype:

object_type_name:

function_specification: restriction:

type of restrictions such as WNDS, WNPS, RNDS, RNPS

Description
With Oracle8, you can now create Objects per your custom needs. To use Objects with Oracle8, you need to install the Oracle Objects option. To create the Objects, use the CREATE TYPE statement.

794

Appendix A 3 Command Reference

With the CREATE TYPE command, you can create an Object type, named varying array (VARRAY), nested Table type, or an incomplete Object type by providing just the forward declaration. You can create a type AS OBJECT and define the variables (read attributes), subprograms (read methods). Use the AS TABLE option to create a named nested Table. If you create the Table with a single datatype, the nested Table type describes a Table with single Column. If you create the Table with another Object as its datatype, the Table takes the Column names and attributes from that Object type. You can create a type AS VARRAY(size) as an ordered set of elements like an array. You can only have object as VARRAY with scalar datatypes. You cannot have a nested Table or another Object as datatype within an Object of type VARRAY. Use the REF option to associate an instance of a source type with an instance of the target Object. Use the MAP MEMBER option to specify a member function or Procedure (read methods). Note you can include only one MAP method that must return a valid, predefined SQL scalar type. In addition, the method can take no arguments except the implicit SELF argument. The MAP MEMBER option implies an implicit call to the MAP method. The MAP method, in turn, will order the Object instances. Use the ORDER MEMBER option to specify a member function (read ORDER method). Note you can include only one ORDER method that must return an integer. The method must return a positive, zero, or negative integer. In addition, the method can take an implicit argument: SELF, and an explicit argument: the Objects instance. You can declare only one method, MAP or ORDER, but not both. The PRAGMA RESTRICT_REFERENCES provides compiler directives to deny member functions read or write access to database Tables, Packaged variables, or both. A good idea would be to use the CREATE OR REPLACE command so that if the Object already exists, Oracle will replace it with the new Object. If you replace an existing Object, Oracle invalidates the Object type body and you need to re-compile the Object type body. You must have the CREATE TYPE or CREATE ANY TYPE system privilege to create Object types.

Appendix A 3 CREATE TYPE

795

Examples
SQL
CREATE TYPE customer_obj AS OBJECT (name CHAR(20), address CHAR(50), age NUMBER(2)); CREATE TYPE name_type AS VARRAY(100) OF CHAR(20); CREATE TYPE loan_obj AS OBJECT (customer_name CHAR(20), loan_amount NUMBER(5), MEMBER FUNCTION get_amount RETURN NUMBER, pragma RESTRICT_REFERENCES (get_amount, WNDS));

796

Appendix A 3 Command Reference

CREATE TYPE BODY

CREATE TYPE BODY
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 24

Section Types

Variables in the syntax

schema:

the database Schema where you want to create the Object body the Object type created with the CREATE TYPE command the PL/SQL Procedure the PL/SQL function

type_name:

procedure_declaration: function_declaration:

Description
With Oracle8, you can now create Objects as per your custom needs. To use Objects with Oracle8, you need to install the Oracle Objects option. To create the Objects, use the CREATE TYPE statement. Use the CREATE TYPE BODY statement to specify the body (read as the actual PL/SQL Procedures and functions) which you specified with the CREATE TYPE statement. Use the MAP MEMBER option to specify a member function or Procedure (read methods). Note you can include only one MAP method that must return a valid,

Appendix A 3 CREATE TYPE BODY

797

predefined SQL scalar type. In addition, the method can take no arguments except the implicit SELF argument. The MAP MEMBER option implies an implicit call to the MAP method. The MAP method, in turn, will order the Object instances. Use the ORDER MEMBER option to specify a member function (read ORDER method). Note you can include only one ORDER method that must return an integer. The method must return a positive, zero, or negative integer. In addition, the method can take an implicit argument: SELF, and an explicit argument: the Objects instance. You can declare only one method, MAP or ORDER, but not both.
Tip

A good idea would be to use the CREATE OR REPLACE command so that if the Object already exists, Oracle will replace it with the new Object. If you replace an existing Object, Oracle invalidates the Object type body and you need to re-compile the Object type body. You must have the CREATE TYPE BODY or CREATE ANY TYPE BODY system privilege to create Object types.

Examples
SQL
CREATE OR REPLACE TYPE BODY loan_obj IS MAP MEMBER FUNCTION get_amount RETURN NUMBER IS BEGIN RETURN loan_amount ; END; END;

798

Appendix A 3 Command Reference

CREATE USER
CREATE USER
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 6, 11

Section Users

Variables in the syntax

user:

the User that you would like to change from the database

tablespace: the Tablespace on which you would like to create the User. A User can span across multiple Tablespaces. n:

any positive integer value the profile to which the User is attached

profile:

Description
To create a new User on the database, use the CREATE USER statement. You can use the CREATE USER statement only if you have the DBA system privileges. The DEFAULT TABLESPACE is where ORACLE creates all the Objects such as the Tables created by the User and more. The TEMPORARY TABLESPACE is where ORACLE creates all the temporary Objects used by the User.

Appendix A 3 CREATE USER

799

Examples
SQL
CREATE USER supervisor identified by master DEFAULT tablespace cust_tablespace TEMPORARY tablespace temp;

800

Appendix A 3 Command Reference

CREATE VIEW
CREATE VIEW
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 8, 13

Section Views

Variables in the syntax

user: view: query:

the User where the view was created the view that you want to create from the database the SQL SELECT statement the name you want to assign to the Constraint

constraint:

Description
To create a view, use the CREATE VIEW statement. A view is a virtual Table in the database whose contents are defined by the SQL SELECT query. You can use the CREATE VIEW statement only if you have the permission to access all the Tables referenced in the query of the view. With the CREATE VIEW command, you can assign a new name to each Column corresponding to the Columns in the query. However, the datatype, length, and other characteristics are derived from the source Columns referenced in the query. If no new names are assigned while creating the view, by default, the view takes the name of the corresponding Column in the query.

Appendix A 3 CREATE VIEW

801

With the FORCE option, you can force ORACLE to create the view regardless of existence of the base Tables or the User has the privileges on them. But to view the VIEW, the User must have sufficient privileges on the Tables referenced in the view.
WITH CHECK OPTION restricts inserts and updates performed through the view.

The insert or update to the Tables referenced in the view must meet the criteria defined by the WHERE clause of the query.

Examples
SQL
CREATE OR REPLACE VIEW [Link] AS SELECT customer_id, loan_id, loan_amount FROM customer WHERE customer_type = L; CREATE OR REPLACE VIEW employee.salary_view (EMPNO, ENAME, SALARY) AS SELECT emp_id, emp_name, grade.grade_range FROM employee, grade WHERE employee.grade_id = grade.grade_id; CREATE OR REPLACE VIEW ladies_only AS SELLECT customer_id, name, address, city, state, zip FROM sex_type = F WITH CHECK OPTION ;

802

Appendix A 3 Command Reference

CURRVAL
CURRVAL
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

user:

the User where the sequence was created the sequence whose next value you want to retrieve

sequence:

Description
Use the CURRVAL statement to retrieve the current value within the sequence from the database. You can use the sequence to create Unique numbers that you can use within your Tables as Primary identifiers.

Examples
SQL*Plus
SELECT loan_seq.CURRVAL FROM DUAL;

Appendix A 3 CURSOR_ALREADY_OPEN

803

CURSOR_ALREADY_OPEN
CURSOR_ALREADY_OPEN
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
If you try to open a cursor that is already open, PL/SQL will implicitly raise a predefined exception of CURSOR_ALREADY_OPEN. The corresponding Oracle error and SQLCODE values are ORA-6511, and -6511 respectively.

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

804

Appendix A 3 Command Reference

DATATYPE
DATATYPE
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 7

Section Table naming and concepts

Table A-12 Oracle8 Datatypes

Datatype
BFILE BLOB CHAR(n)

Parameters None None n=1 to 255

Description Large binary object (LOB) stored outside the database. Maximum size is 4GB. Large object (LOB). Maximum size is 4GB. Text string with fixed length. The default length is 1. You can specify the maximum length (n) when defining the Column. Large object (LOB). Maximum size is 4GB. Valid dates range from January 1, 4712, B.C., to December 31, 4712, A.D. Oracle8 stores DATE internally as a 7-byte number and, by definition, also includes the time in hours, minutes, and seconds.

CLOB DATE

None None

Appendix A 3 DATATYPE

805

Datatype
FLOAT(n) LONG LONG RAW MLSLABEL

Parameters p=1 to 126 None None None

Description Binary number. Specify the precision (n), which is the number of digits. Text string with variable length. Maximum length is 2GB, so LONG is for large data. Raw binary data of variable length. The maximum length is 2GB. Binary format of a label used on a secure operating system. This datatype is used only with Trusted Oracle to mediate access to information. See CHAR, which is the same except that the characters stored depend on a national character set (Chinese characters, for example). Oracle8 supports many languages this way. Large object (LOB). Maximum size is 4GB. Number. Specify the precision (p), which is the number of digits, and the scale (s), which is the number of digits to the right of the decimal place. See VARCHAR. The NVARCHAR2 has the same attributes, except that it stores characters for any language (national character set) supported by Oracle8. Raw binary data of variable length. You must specify the maximum length (n) when defining the Column. Hexadecimal format identical to the format of the pseudocolumn ROWID. Text string with variable length. Specify the maximum length (n) when defining the Column. This is an obsolete data type provided only to support older Oracle databases. Text string with variable length. Specify the maximum length (n) when defining the Column.

NCHAR(n)

n=1 to 2,000

NCLOB NUMBER(p,s)

None p=1 to 38, s=-84 to 127, or FLOAT n=1 to 4,000

NVARCHAR2(n)

RAW(n) ROWID VARCHAR(n)

n=1 to 255 None n=1 to 4,000

VARCHAR2(n)

n=1 to 4,000

The following sections describe each data type in more detail.

806

Appendix A 3 Command Reference

DATATYPE CHAR
DATATYPE CHAR
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 7

Section Table naming and concepts

Variables in the syntax

a numeric variable that denotes the size of the datatype. The minimum value is 1 and maximum is 2000. By default, the value is set to 1.

Description
The CHAR datatype is a fixed-length character string. If you insert a value in a Column of type CHAR and the length of this value is less than the one specified in the Table, Oracle will pad this value with blanks. On the other hand, you would get an error if you tried to insert a value greater than the length specified for the Column. The main difference between CHAR and VARCHAR2 is that CHAR pads the data with blank spaces, and VARCHAR2 strips off blank spaces.

Usage
The CHAR datatype is generally used with flag fields that have a value Yes or No and so on. Use this datatype only if you are absolutely sure that the Column will have a fixed length for each record of the Table.

Appendix A 3 DATATYPE DATE

807

DATATYPE DATE
DATATYPE DATE
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 7

Section Table naming and concepts

Variables in the syntax

None.

Description
The DATE datatype is a variable used to store the date and time. The valid date range in Oracle is from January 1, 4712 BC to December 31, 4712 AD.
Tip

The Oracle DATE datatype is year 2000 compliant! Oracle stores the century, year, month, date, day, hours, minutes, and seconds in a DATE datatype.

Usage
Although you can use the combination of CHAR and NUMBER datatypes to store a date, the DATE datatype has special set of properties and functions that make it easy to compare and manipulate date variables. Oracle provides special math functions for adding months to dates, finding the last day of the month, finding the difference between two dates, and more.

808

Appendix A 3 Command Reference

DATATYPE FLOAT
DATATYPE FLOAT
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 7

Section Table naming and concepts

Variables in the syntax

n: a numeric variable that denotes binary precision (the total number of digits). The minimum value is 1 and maximum is 126. To convert from binary to decimal precision, multiply n by 0.30103.

Description
The FLOAT datatype is used to store floating point numeric values. A floating point value can optionally have a decimal point anywhere between the first and last digit. You can store a value with decimal precision up to 38 or a binary precision of 126.

Usage
The FLOAT datatype is an add-on to the number datatype to hold values higher than the NUMBER datatype. The FLOAT datatype may be generally used for storing scientific data values like values from experiments and more.

Appendix A 3 DATATYPE LONG

809

DATATYPE LONG
DATATYPE LONG
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 7

Section Table naming and concepts

Variables in the syntax

None.

Description
The LONG datatype is a variable length character string. When you create a variable of type LONG, you can store up to 2 gigabytes or 2,147,483,647 bytes of data in this field. The datatype is nearly similar to VARCHAR2 in its characteristics.

Usage
You can use the LONG datatype to store long text strings like an entire HTML file, a word document, and more. Later, you can build a full text search like Yahoo! or AltaVista Search Engines using the Con Text Option of Oracle to Index and search the long Column. However, the use of LONG datatype has certain restrictions such as: 3 A Table can have only one LONG Column and it must be at the end of the Table. 3 The LONG Column cannot have any integrity Constraints expect for the NULL and NOT NULL Constraint. 3 You cannot use the LONG variable as a return value for your PL/SQL function. 3 The LONG Column cannot be used with the CREATE SNAPSHOT SQL statement.

810

Appendix A 3 Command Reference

3 The LONG Column must be located on a single database for one SQL statement. 3 You cannot use the LONG Column in the WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses of your SQL statement. Nor can you use the LONG field for any expression or condition. 3 You cannot use the LONG Column with the DISTINCT, SUBSTR, and INSTR functions within your SQL statement. 3 You cannot search for characters within a LONG datatype Column.
Tip

Use this one only when you absolutely need it for large chunks of data that do not need text searching done on them. Otherwise, the limitations of the LONG datatype get in your way. Use VARCHAR2 when you need to do text searching.
LONG is an older datatype that will eventually disappear in the wake of the large object datatypes: CLOB and NCLOB.

Caution

Appendix A 3 DATATYPE LONGRAW

811

DATATYPE LONGRAW
DATATYPE LONGRAW
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 7

Section Table naming and concepts

Variables in the syntax

None.

Description
The LONG RAW datatype is a variable length datatype to store binary data. The long raw datatype has the same features as that of the RAW datatype except for the fact that the LONG RAW can be up to two gigabytes or 2,147,483,647 bytes of data in this field.

Usage
You can use the LONG RAW datatype in a manner similar to that of the RAW datatype. You cannot Index a LONG RAW datatype whereas a RAW datatype can be Indexed. Use the LONGRAW datatype for larger graphics, formatted text files, such as Word documents, audio, video, and other nontext data. You cannot have both a LONG and a LONGRAW Column in the same Table.
Caution

LONGRAW is an older datatype that will eventually disappear, replaced by the large Object datatypes: BLOB, CLOB, NCLOB, and BFILE.

812

Appendix A 3 Command Reference

DATATYPE MLSLABEL
DATATYPE MLSLABEL
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 7

Section Table naming and concepts

See Also

Syntax
MLSLABEL

Variables in the syntax

None.

Description
It is used to store the binary format of a label used on a secure operating system.

Usage
The MLSLABEL datatype is used only with Trusted Oracle to mediate access to information.

Appendix A 3 DATATYPE NUMBER

813

DATATYPE NUMBER
DATATYPE NUMBER
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 7

Section Table naming and concepts

Variables in the syntax

p: a numeric variable that denotes precision (the total number of digits). The minimum value is 1 and maximum is 38. s:

a numeric variable that denotes scale (the number of digits to the right of the decimal point). The minimum value is -84 and maximum is 127.8.

Description
The NUMBER datatype is used to store fixed-point numeric values. You can store a value up to 1.0 x 10125. If the value exceeds p (precision), Oracle returns an error. If the value exceeds s (scale), Oracle will round off the value.
Example

As an example, you define a number with a maximum value of 999.99 as number (5,2). Oracle8 truncates data that does not fit into the scale. Your Column definition is number (5,2), for example, and you put the number 575.316 in this Column. The number that actually gets stored in the Column is 575.32, because Oracle8 automatically truncates the decimals at the hundredths, which is the scale that Oracle8 defined for that Column. If you define a Column as number (3,0) and then add a row with data 575.316 in that Column, the actual number stored is 575. If the scale is negative, the value is rounded off to the number of places left of the decimal point.

Usage
The NUMBER datatype is generally used for storing numeric data such as amount, rate, and so on. In most cases, the NUMBER datatype is large enough to hold all the necessary values.

814

Appendix A 3 Command Reference

DATATYPE RAW
DATATYPE RAW
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 7

Section Table naming and concepts

Variables in the syntax

a numeric variable that denotes the size of the datatype in bytes. The minimum value is 1 and maximum is 255.

Description
The RAW datatype is a variable length datatype to store binary data. The datatype is very similar to VARCHAR2 in its characteristics.

Usage
You can use the RAW datatype to store information that you would not like to convert or interpret when moving them between systems. You can use this datatype to store graphics files or sound files or entire scanned documents and more. The interpretation of the data will be an obligation of the client as the Oracle database server stores and retrieves binary data as it is. You cannot use the RAW datatype in Net8 application. Also, the Import and Export utilities of Oracle do not perform character conversion of RAW datatype. Use the RAW datatype for small graphics or formatted text files, such as Microsoft Word documents.
Caution

RAW is an older datatype that will eventually disappear, replaced by the large object datatypes: BLOB, CLOB, NCLOB, and BFILE.

Appendix A 3 DATATYPE ROWID

815

DATATYPE ROWID
DATATYPE ROWID
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 7

Section Table naming and concepts

Variables in the syntax

None.

Description
The ROWID datatype is a hexadecimal string that represents the address of a particular row in a Table. This is a pseudocolumn. However, you can create Columns having ROWID as their datatype.

Usage
You can use the ROWID within the WHERE clause of your SQL statement and limit the number of rows that are retrieved from the database.

816

Appendix A 3 Command Reference

DATATYPE VARCHAR
DATATYPE VARCHAR
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 7

Section Table naming and concepts

Variables in the syntax

a numeric variable that denotes the size of the datatype. The minimum value is 1 and maximum is 4000. By default, the value is set to 1.

Description
The VARCHAR datatype is a variable length character string. When you create a variable of type VARCHAR, you specify to Oracle the maximum length that the variable will hold. This datatype functions identical to the VARCHAR2 datatype.

Usage
Caution

It is recommended that you use VARCHAR2 instead of the VARCHAR datatype as Oracle may remove the VARCHAR datatype in its future upgrades. The function has been provided only for backward compatibility.

Appendix A 3 DATATYPE VARCHAR2

817

DATATYPE VARCHAR2
DATATYPE VARCHAR2
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter 7

Section Table naming and concepts

Variables in the syntax

a numeric variable that denotes the size of the datatype. The minimum value is 1 and maximum is 4000. By default, the value is set to 1.

Description
The VARCHAR2 datatype is a variable length character string. When you create a variable of type VARCHAR2, you specify to Oracle the maximum length that the variable will hold. If you insert a value in a Column of type VARCHAR2 and the length of this value is less than or equal to the one specified in the Table, Oracle will store this value as it is. Thus, if the Column value was less than the one specified by you, you can save critical database space. On the other hand, you would get an error if you tried to insert a value greater than the length specified for the Column. This datatype holds letters, numbers, and symbols in the standard ASCII text set (or EBCDIC, or whatever set is standard for your database). If your data is shorter than the maximum size, Oracle8 adjusts the length of the Column to the size of the data. If your data has trailing blanks, Oracle8 removes the trailing blanks. VARCHAR2 is a commonly used datatype.

Usage
The VARCHAR2 datatype is generally used for all string type fields like Name, Address, and so on. You can use this datatype when you know the upper range of the Column length, but it is not necessary for the User to input the Column with a value whose length is exactly the one you specified in the database. It can be less.

818

Appendix A 3 Command Reference

DECLARE
DECLARE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 19

Section Packages, Procedures, Functions

See Also DECLARE CURSOR, DECLARE DATABASE, DECLARE TABLE, CREATE PACKAGE

Syntax
DECLARE object_declaration_1,...,object_declaration_n;

Variables in the syntax

object_declaration_1,...,object_declaration_n:

Objects declared with

their types

Description
The DECLARE command constitutes a PL/SQL blocks declarative part. By using the DECLARE command, you can declare Objects and sub programs locally.

Examples
PL/SQL
DECLARE total_sales total_products unit_price NUMBER; NUMBER; CONSTANT NUMBER:= 1.0;

Appendix A 3 DECODE

819

DECODE
DECODE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 7

Section SQL Functions in Queries

Variables in the syntax

a value to decode or a Column name. This is the variable which you want to decode. the first if condition. This refers to the string that you want to replace. the first then condition. This refers to the string with which you want to

if1:

then1:

replace.
else: if the function does not find the value with any of the if conditions, it will replace the value with the one specified here.

Description
The function DECODE is a value by value substitution. For each value, DECODE checks for a match in the if series and if a match is found, replaces the same with the corresponding then condition. Although, in the syntax, there are only two if then conditions specified, you can specify any number of such conditions.

Examples
PL/SQL
Var1:= DECODE (Var2, Sybase, Oracle, );

820

Appendix A 3 Command Reference

SQL
SELECT DECODE (Microsoft, Microsoft, Macrosoft) Example FROM DUAL; Example --------Macrosoft

Appendix A 3 DEFINE

821

DEFINE
DEFINE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

variable: text:

the variable you would like to define

the text you would like to assign to the variable

Description
To define a variable and assign text to the variable, use the DEFINE command. Note you can assign only character values to the variable you declare by using the DEFINE command.

Examples
SQL
DEFINE last_name = Jordan DEFINE department_number = 160

822

Appendix A 3 Command Reference

DEL
DEL
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

the line number within the SQL buffer you would like SQL*Plus to delete lines n through m within the SQL buffer you would like SQL*Plus to delete

n m: n *:

lines n through the current line within the SQL buffer you would like SQL*Plus to delete

n LAST:

lines n through the last line within the SQL buffer you would like SQL*Plus to delete the current line within the SQL buffer you would like SQL*Plus to delete

* n:

the current line through line n within the SQL buffer you would like SQL*Plus to delete

* LAST:

the current line through the last line within the SQL buffer you would like SQL*Plus to delete the last line within the SQL buffer you would like SQL*Plus to delete

LAST:

Description
To delete one or more lines from the SQL buffer, use the DEL command.

Appendix A 3 DEL

823

Examples
SQL
D 1 DEL 5 LAST

824

Appendix A 3 Command Reference

DELETE
DELETE
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

table: a Table within the database for which the User deleting the data has DELETE privileges sub_query:

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

statement
alias:

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

a valid WHERE clause specifying the rows that will be

deleted
CURRENT OF cursor_name:

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

Description
Use the DELETE command to delete all rows of data from a Table, or view. You can also use a sub query in a DELETE statement. All the DELETE triggers on a Table would get fired when you fire the DELETE statement.

Appendix A 3 DELETE

825

To issue a DELETE command, you must have the DELETE privilege on the particular Table, or view.

Examples
SQL
DELETE FROM employees where employee_id = 124561123; DELETE FROM employees where employee_id in (SELECT employee_id FROM department_head WHERE department_active_flag = N);

826

Appendix A 3 Command Reference

DEREF
DEREF
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 9

Section Querying Objects

Variables in the syntax

expn:

any expression that returns a reference to an Object

Description
DEREF returns an Object reference to the argument passed to it.

Examples
SQL
CREATE TABLE customer_tab (customer_no NUMBER, loan REF loan_tab); SELECT DEREF (loan) FROM customer_tab;

Appendix A 3 DESCRIBE

827

DESCRIBE
DESCRIBE
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

user: table:

the Table, or Objects owner the Table, or view whose Column you would like SQL*Plus to describe the database link name for the specified Table, or view

database_link_name: column: object:

the Column you would like SQL*Plus to describe the Object you would like SQL*Plus to describe

subobject: if the Object is a Package, the sub-Object will represent the Object within the Package you would like SQL*Plus to describe

Description
To list the Column definitions for the given Table or view, use the DESCRIBE command.

Examples
SQL
DESCRIBE loan DESC acct

828

Appendix A 3 Command Reference

DROP CLUSTER
DROP CLUSTER
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 22

Section Clusters

Variables in the syntax

user:

the User where the cluster was created the cluster you would like to drop from the database

cluster:

Description
To remove a cluster from the database, use the DROP CLUSTER statement. To drop the Tables associated with the cluster, use the INCLUDING TABLES option. To drop the referential integrity Constraints from the Tables outside the cluster associated with the Tables Primary and Foreign Keys, use the CASCADE CONSTRAINTS option. You can use the DROP CLUSTER statement only if you have the DROP CLUSTER system privilege. DROP CLUSTER will commit all pending changes to the database.

Examples
SQL
DROP CLUSTER marketing; DROP CLUSTER telecommunications INCLUDING TABLES; DROP CLUSTER branch INCLUDING TABLES CASCADE CONSTRAINTS;

Appendix A 3 DROP DATABASE LINK

829

DROP DATABASE LINK

DROP DATABASE LINK
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 21

Section

See Also

Distributed Database Application CREATE DATABASE LINK

Syntax
DROP [PUBLIC] DATABASE LINK database_link

Variables in the syntax

PUBLIC: specify PUBLIC if the database link you would like to remove from the database is a PUBLIC database link database_link:

the database link you would like to remove from the database

Description
To remove a database link from the database, use the DROP DATABASE LINK statement. You can use the DROP DATABASE LINK statement only if you have the DROP DATABASE LINK system privilege. DROP DATABASE LINK will commit all pending changes to the database. You cannot use PUBLIC when dropping a private link.

Examples
SQL
DROP PUBLIC DATABASE LINK corporation; DROP DATABASE LINK tracking;

830

Appendix A 3 Command Reference

DROP DIRECTORY
DROP DIRECTORY
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 23

Section LOBs

Variables in the syntax

directory:

the directory object that you want to delete.

Description
To drop a directory Object, use the DROP DIRECTORY statement. The directory Object is new with Oracle8. It refers to the directory on the ORACLE servers physical system. This directory stores external binary files (bFiles). Dropping a directory in ORACLE will not drop the physical directory on the operating system. You need to have the DROP DIRECTORY system privilege to DROP a directory Object.

Examples
SQL
DROP DIRECTORY bfile_directory;

Appendix A 3 DROP FUNCTION

831

DROP FUNCTION
DROP FUNCTION
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 19

Section Functions

Variables in the syntax

user:

the User where the stored function was created the stored function you would like to drop from the database

function:

Description
To remove a stored function from the database, use the DROP FUNCTION statement. You can use the DROP FUNCTION statement only if you have the DROP FUNCTION system privilege. DROP FUNCTION will commit all pending changes to the database.

Examples
SQL
DROP FUNCTION acctrpt DROP FUNCTION [Link]

832

Appendix A 3 Command Reference

DROP INDEX
DROP INDEX
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 7, 12

Section Indexes

Variables in the syntax

user: index:

the User where the Index was created the Index you would like to drop from the database

Description
To remove an Index from the database, use the DROP INDEX statement. You can use the DROP INDEX statement only if you have the DROP INDEX system privilege. DROP INDEX will commit all pending changes to the database.

Examples
SQL
DROP INDEX sales; DROP INDEX resources;

Appendix A 3 DROP LIBRARY

833

DROP LIBRARY
DROP LIBRARY
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

library_name:

the name of the library Object that you want to drop from the

database.

Description
The library Object is new with Oracle8. To drop a library Object, use the DROP
LIBRARY statement.

You need to have the DROP LIBRARY system privilege to DROP a library Object.

Examples
SQL
DROP LIBRARY ext_procs;

834

Appendix A 3 Command Reference

DROP PACKAGE
DROP PACKAGE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 19

Section Packages

Variables in the syntax

body: the body Option only drops the Package body without dropping the Package specification user:

the User where the stored Package was created the stored Package you would like to drop from the database

package:

Description
To remove a stored Package from the database, use the DROP PACKAGE statement. You can use the DROP PACKAGE statement only if you have the DROP PACKAGE system privilege. DROP PACKAGE will commit all pending changes to the database.

Examples
SQL
DROP PACKAGE loan; DROP PACKAGE BODY [Link];

Appendix A 3 DROP PROCEDURE

835

DROP PROCEDURE
DROP PROCEDURE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 19

Section Procedures

Variables in the syntax

user:

the User where the stored Procedure was created the stored Procedure you would like to drop from the database

procedure:

Description
To remove a stored Procedure from the database, use the DROP PROCEDURE statement. You can use the DROP PROCEDURE statement only if you have the DROP PROCEDURE system privilege. DROP PROCEDURE will commit all pending changes to the database. When you drop a Procedure, all the Objects depending on that Procedure or calling that Procedure will automatically become invalid.

Examples
SQL
DROP PROCEDURE loanrpt; DROP PROCEDURE [Link];

836

Appendix A 3 Command Reference

DROP PROFILE
DROP PROFILE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 6, 11

Section User Profiles

Variables in the syntax

user:

the User where the profile was created the profile you would like to drop from the database

profile:

Description
To remove a profile from the database, use the DROP PROFILE statement. You can use the DROP PROFILE statement only if you have the DROP PROFILE system privilege. DROP PROFILE will commit all pending changes to the database.

Examples
SQL
DROP PROFILE accountant; DROP PROFILE my_profile;

Appendix A 3 DROP ROLE

837

DROP ROLE
DROP ROLE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 6, 11

Section Roles

Variables in the syntax

user: role:

the User where the Role was created the Role you would like to drop from the database

Description
To remove a Role from the database, use the DROP ROLE statement. You can use the DROP ROLE statement only if you have the ANY DROP ROLE system privilege. DROP ROLE will commit all pending changes to the database.

Examples
SQL
DROP ROLE manager; DROP ROLE officer;

838

Appendix A 3 Command Reference

DROP ROLLBACK SEGMENT

DROP ROLLBACK SEGMENT
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

rollback_segment:

the rollback segment you would like to drop from the

database

Description
To remove a rollback segment from the database, use the DROP ROLLBACK SEGMENT statement. The DBA_ROLLBACK_SEGS data dictionary contains all the list of rollback segments that are in use. To drop the ROLLBACK SEGMENT, you would either have to wait until it is no longer in use or SHUTDOWN the database and bring it up in the EXCLUSIVE mode.

Examples
SQL
DROP ROLLBACK SEGMENT humanresources; DROP ROLLBACK SEGMENT insurance;

Appendix A 3 DROP SEQUENCE

839

DROP SEQUENCE
DROP SEQUENCE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 8, 13

Section Sequences

Variables in the syntax

user:

the User where the sequence was created the sequence you would like to drop from the database

sequence:

Description
To remove a sequence from the database, use the DROP SEQUENCE statement. You can use the DROP SEQUENCE statement only if you have the DROP ANY SEQUENCE system privilege. DROP SEQUENCE will commit all pending changes to the database.

Examples
SQL
DROP SEQUENCE customer.new_cust_seq; DROP SEQUENCE seq1;

840

Appendix A 3 Command Reference

DROP SNAPSHOT
DROP SNAPSHOT
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

user:

the User where the snapshot was created the snapshot you would like to drop from the database

snapshot:

Description
To remove a snapshot from the database, use the DROP SNAPSHOT statement. You can use the DROP SNAPSHOT statement only if you have the DROP ANY SNAPSHOT system privilege. DROP SNAPSHOT will commit all changes to the database.

Examples
SQL
DROP SNAPSHOT customer.inactive_cust_snapshot; DROP SNAPSHOT snp1;

Appendix A 3 DROP SNAPSHOT LOG

841

DROP SNAPSHOT LOG

DROP SNAPSHOT LOG
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

user: table:

the User where the snapshot log was created

the master Table associated with snapshot log you would like to drop from the database

Description
To remove a snapshot log from the database, use the DROP SNAPSHOT LOG statement. You can use the DROP SNAPSHOT LOG statement only if you have the DROP TABLE and DROP TRIGGER system privileges on the master Table associated with the snapshot log.

Examples
SQL
DROP SNAPSHOT LOG ON inventory

842

Appendix A 3 Command Reference

DROP SYNONYM
DROP SYNONYM
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 8, 13

Section Synonyms

Variables in the syntax

user:

the User where the synonym was created the Synonym you would like to drop from the database

synonym:

Description
To remove a Synonym from the database, use the DROP SYNONYM statement. You can use the DROP SYNONYM statement only if you have the DROP ANY SYNONYM system privilege. To drop a PUBLIC Synonym, use the PUBLIC keyword with the DROP SYNONYM statement.

Examples
SQL
DROP SYNONYM PUBLIC SYNONYM computer; DROP SYNONYM advertisement;

Appendix A 3 DROP TABLE

843

DROP TABLE
DROP TABLE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 7, 12

Section Tables

Variables in the syntax

user: table:

the User where the Table was created the Table you would like to drop from the database

Description
To remove a Table from the database, use the DROP TABLE statement. You can use the DROP TABLE statement only if you have the DROP ANY TABLE system privilege. To drop the referential integrity constraints associated with the Tables Primary and Foreign Keys, use the CASCADE CONSTRAINTS option.

Examples
SQL
DROP TABLE loan; DROP TABLE acct CASCADE CONSTRAINTS;

844

Appendix A 3 Command Reference

DROP TABLESPACE
DROP TABLESPACE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 7, 12

Section Tablespaces

Variables in the syntax

tablespace:

the Tablespace you would like to drop from the database

Description
To remove a Tablespace from the database, use the DROP TABLESPACE statement. You can use the DROP TABLESPACE statement only if you have the DROP TABLESPACE system privilege. To drop the Tablespaces contents, use the INCLUDING CONTENTS option. To drop the referential integrity Constraints from Tables outside the Tablespace associated with the Tables Primary and Foreign Keys, use the CASCADE CONSTRAINTS option.

Examples
SQL
DROP TABLESPACE accounting; DROP TABLESPACE airlines INCLUDING CONTENTS; DROP TABLESPACE construction CASCADE CONSTRAINTS;

Appendix A 3 DROP TRIGGER

845

DROP TRIGGER
DROP TRIGGER
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 19

Section Triggers

Variables in the syntax

user:

the User where the Trigger was created the Trigger you would like to drop from the database

trigger:

Description
To remove a Trigger from the database, use the DROP TRIGGER statement. You can use the DROP TRIGGER statement only if you have the DROP ANY TRIGGER system privilege.

Examples
SQL*Plus
DROP TRIGGER accounting.acct_trigger_1; DROP TRIGGER loan_trigger_2;

846

Appendix A 3 Command Reference

DROP TYPE
DROP TYPE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 24

Section Types

Variables in the syntax

schema:

the Schema where the type was created specification the Object type you would like to drop

type_name:

Description
To remove an Object type from the database, use the DROP TYPE statement. Use the FORCE option to drop all Object types that reference the Object being dropped. You can use the DROP TYPE statement only if you have the DROP TYPE system privilege.

Examples
SQL
DROP TYPE customer_obj; DROP TYPE loan_obj FORCE;

Appendix A 3 DROP TYPE BODY

847

DROP TYPE BODY

DROP TYPE BODY
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 24

Section Types

Variables in the syntax

schema:

the Schema where the type was created specification the Object type whose body you would like to drop

type_name:

Description
To remove an Object body from the database, use the DROP TYPE BODY statement. You can only drop Object bodies that have no other dependencies dropped. You can use the DROP TYPE BODY statement only if you have the DROP TYPE BODY system privilege.

Examples
SQL
DROP TYPE BODY customer_obj; DROP TYPE BODY loan_obj;

848

Appendix A 3 Command Reference

DROP USER
DROP USER
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 6, 11

Section Users

Variables in the syntax

user:

the User you would like to remove from the database

Description
To remove a User from the database, use the DROP USER statement. You can use the DROP USER statement only if you have the DROP USER system privilege. To drop all Objects associated with the User before dropping the User, use the CASCADE option.

Examples
SQL
DROP USER john; DROP USER robert CASCADE;

Appendix A 3 DROP VIEW

849

DROP VIEW
DROP VIEW
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 8, 13

Section Views

Variables in the syntax

user: view:

the User where the view was created the view you would like to drop from the database

Description
To remove a view from the database, use the DROP VIEW statement. You can use the DROP VIEW statement only if you have the DROP ANY VIEW system privilege.

Examples
SQL
DROP VIEW [Link]; DROP VIEW loanview;

850

Appendix A 3 Command Reference

DUMP
DUMP
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 10

Section Inserting Rows

Variables in the syntax

a CHAR or VARCHAR2 variable specified in quotes.

fmt:

this refers to the format of the expression. It defaults to ASCII or EBCDIC, depending on your machine. The fmt can take the following code:

Table A-13 Formats allowed in the DUMP command

Code 8 Notation OCTAL DECIMAL (ASCII) HEXADECIMAL SINGLE CHARACTERS

start: length:

this refers to the starting position of the s.

a numeric variable representing the length of characters to display. The default is to return the length of s.

Appendix A 3 DUMP

851

Description
The DUMP function will return a VARCHAR2 value in the internal data format as specified by fmt. If s is NULL, the function would return NULL.

Examples
SQL
SELECT DUMP (SALES_AGENT) DUMP (Sales Agent, 10, 1, 5) FROM SALES; DUMP (Sales Agent) -------------Typ=1 Len=14: 20,4e,20,46,52

852

Appendix A 3 Command Reference

DUP_VAL_ON_INDEX
DUP_VAL_ON_INDEX
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
If you try to store duplicate values within a database Column constrained by a Unique Index, PL/SQL will implicitly raise a pre-defined exception of DUP_VAL_ ON_INDEX. The corresponding Oracle error and SQLCODE values are ORA-00001, and 1 respectively.

Examples
PL/SQL
BEGIN UPDATE STATISTICS SET ROW_COUNT = ROW_COUNT + 1; IF SQL%ROWCOUNT = 0 THEN INSERT INTO STATISTICS VALUES (1); END IF; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE STATISTICS SET ROW_COUNT = 0; END;

Appendix A 3 EDIT

853

EDIT
EDIT
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

file_name: ext:

the file you would like the editor to open

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

Description
To direct the host operating systems text editor to open the given file, use the EDIT command. If you do not specify a file, the text editor will open the contents of the buffer.

Examples
SQL
ED LOAN_RPT EDIT ACCT_RPT EDIT [Link]

854

Appendix A 3 Command Reference

EMPTY_BLOB
EMPTY_BLOB
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

None.

Description
The EMPTY_BLOB function is new with Oracle8. It returns an empty LOB (Large Binary Object) locator. You can use this function in an INSERT or an UPDATE SQL statement to initialize a LOB Column. Similarly, you can use it within a PL/SQL statement to initialize a LOB variable to EMPTY.
EMPTY means that the object is initialized but not populated with data.

Examples
PL/SQL
Var1:= EMPTY_BLOB ();

SQL
INSERT INTO lob_table VALUES (BLOB DEMO, EMPTY_BLOB()); UPDATE lob SET blob_column = EMPTY_BLOB() WHERE name = BLOB DEMO;

Appendix A 3 EMPTY_CLOB

855

EMPTY_CLOB
EMPTY_CLOB
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

None.

Description
The EMPTY_CLOB function is new with Oracle8. It returns an empty LOB (Large Binary Object) locator. You can use this function in an INSERT or an UPDATE SQL statement to initialize a LOB Column. Similarly, you can use it within a PL/SQL statement to initialize a LOB variable to EMPTY.
EMPTY means that the object is initialized but not populated with data.

Examples
PL/SQL
Var1:= EMPTY_CLOB ();

SQL
INSERT INTO lob_table VALUES (CLOB DEMO, EMPTY_CLOB()); UPDATE lob SET clob_column = EMPTY_CLOB() WHERE name = CLOB DEMO;

856

Appendix A 3 Command Reference

EXCEPTION INIT Pragma

EXCEPTION INIT Pragma
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

an integer value denoting the exception error code

Description
In Oracle, you have hundreds of standard system exceptions, such as ZERO_DIVIDE, that are referenced by name. Internally, ORACLE handles these messages by their error codes. Messages that are not named still raise an exception flag and the control transfers to the EXCEPTION block, but all such messages are caught by OTHERS EXCEPTION handler. You can provide names to such messages using the EXCEPTION_INIT command.

Examples
PL/SQL
DECLARE exception_error_occurred exception; pragma exception_init (exception_error_occurred -786); BEGIN ...... EXCEPTION WHEN exception_error_occurred THEN UPDATE APPLICATION_ERROR_TABLE SET ERROR = EXCEPTION ERROR ; END;

Appendix A 3 EXECUTE

857

EXECUTE
EXECUTE
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

statement:

a PL/SQL statement

Description
To execute the given PL/SQL statement like a Procedure, Trigger, and more, use the EXECUTE command.

Examples
SQL
EXECUTE sales_calculation; EXEC sales_input_trigger;

858

Appendix A 3 Command Reference

EXISTS
EXISTS
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 9

Section Joining Tables

See Also Other comparison operators like NOT IN, ANY, ALL, BETWEEN

Syntax
WHERE EXISTS (subquery)

Variables in the syntax

subquery:

a SQL SELECT statement that forms a part of the where clause

Description
The EXISTS operator can be used only with a subquery. It returns TRUE if the subquery returns one or more rows.

Examples
SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE EXISTS (SELECT * FROM MONTHLY_SALES WHERE DAILY_SALES.SALES_AMOUNT = MONTHLY_SALES.SALES_AMOUNT); SALES_AMOUNT ---------12.23

Appendix A 3 EXIT

859

EXIT
EXIT
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

statement_1,...,statement_n:

sequence of statements PL/SQL will execute

multiple times
condition_1:

a certain condition PL/SQL will evaluate

Description
Use the EXIT statement to exit from a LOOP immediately.

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

860

Appendix A 3 Command Reference

EXIT
EXIT
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

an integer you will specify as the return code a User-defined, or system variable

variable:

Description
To terminate the SQL*Plus session and return control to the host operating system, use the EXIT (or QUIT) command. Within the EXIT (or QUIT) command, you can also specify a return code or variable. If you will specify SUCCESS, SQL*Plus will exit normally. If you will specify FAILURE, SQL*Plus will exit with a return code that will indicate failure. If you will specify WARNING, SQL*Plus will exit with a return code that will indicate a warning. If you will specify COMMIT, SQL*Plus will commit all pending changes to the database before exiting. If you will specify ROLLBACK, SQL*Plus will rollback all pending changes to the database before exiting.

Appendix A 3 EXIT

861

Examples
SQL
EXIT EXIT [Link] QUIT

862

Appendix A 3 Command Reference

EXIT-WHEN
EXIT-WHEN
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

statement_1,...,statement_n:

sequence of statements PL/SQL will execute

multiple times
condition_1:

a certain condition PL/SQL will evaluate

Description
Use the EXIT-WHEN structure to exit a loop when the given condition is met.

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

Appendix A 3 EXP

863

EXP
EXP
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a numeric variable

Description
The EXP function will return the exponential value of a number. The result is e raised to the nth power where e = 2.71828183. The resultant value is accurate up to 36 digits.

Examples
PL/SQL
Var1:= EXP (12);

SQL
SELECT EXP (12) Exponential Value of 12 FROM DUAL; Exponential Value of 12 ----------------------162754.79

864

Appendix A 3 Command Reference

EXPLAIN PLAN
EXPLAIN PLAN
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

statement_name:

A name that identifies the explanation plan in the output Table. If this is not specified, a null value is stored in the output Table. the User where the Table was created the Table where the plan explanation output is stored the SQL statement for which you want the plan explanation

user: table: query:

Description
EXPLAIN PLAN is yet another powerful feature of ORACLE. It provides you with the

plan outlay that ORACLE will use for performing the query on the database. The output Table must be created before this command can be used. The start file
[Link] contains the format for the output Table and can be used to create the output Table. If you use the [Link] file, it will create PLAN_TABLE as the

output Table for all plan explanations.

Appendix A 3 EXPLAIN PLAN

865

Examples
SQL
EXPLAIN PLAN SET STATEMENT_ID = exp_plan_customer INTO all_plan FOR SELECT CUSTOMER_ID, CUSTOMER_NAME FROM CUSTOMER WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM LOAN_APPROVAL );

866

Appendix A 3 Command Reference

FETCH
FETCH
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

cursor_name:

name of cursor you would like to close, and release resources associated with the cursor

cursor_variable_name:

name of cursor variable you would like to close, and release resources associated with the cursor variable name of cursor variable declared within a PL/SQL

host_cursor_variable_name:

host environment
variable_1,...,variable_n:

a sequence of variables into which data will be

fetched one row at a time

record_name: a User-defined, or %ROWTYPE record into which data will be fetched by the FETCH statement

Description
Use the FETCH statement to retrieve data one row at a time, and store the data within variables or fields. Use the FETCH statement after you open the cursor.

Appendix A 3 FETCH

867

Examples
PL/SQL
OPEN loan_cur ; LOOP FETCH loan_cur INTO loan_rec; EXIT WHEN loan_cur%NOTFOUND; END LOOP; CLOSE loan_cur; OPEN emp_cur ; LOOP FETCH emp_cur INTO emp_id, emp_name, emp_title, emp_salary; EXIT WHEN emp_cur%NOTFOUND; END LOOP; CLOSE emp_cur;

868

Appendix A 3 Command Reference

FLOOR
FLOOR
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a numeric variable

Description
The FLOOR function will return the largest integer equal to or less than the number.

Examples
PL/SQL
Var1:= FLOOR (147.2754);

SQL
SELECT FLOOR (147.2754) Floor FROM DUAL; Floor --------147

Appendix A 3 FOR-LOOP

869

FOR-LOOP
FOR-LOOP
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

loop counter variable sequence of statements PL/SQL will execute

statement_1,...,statement_n:

multiple times

Description
Use the FOR-LOOP structure to execute a sequence of statements for the given number of times.

Examples
PL/SQL
FOR i in 1..total_employees LOOP Total_Salary:= Base_Salary + Commission + Bonus; UPDATE salary SET employee_salary = Total_Salary WHERE employee_number:= emp_id; END LOOP;

870

Appendix A 3 Command Reference

FORMAT
Oracle provides the facility of 3 format models that you can use for converting the data from one datatype to another. These formats work for conversion between CHAR, VARCHAR2, NUMBER, and DATE datatypes. Format items cannot appear twice. Also, format items that represent similar information cannot be combined. These can be used with TO_CHAR, TO_DATE, and TO_NUMBER functions.

Appendix A 3 FORMAT DATE

871

FORMAT DATE
Table A-14 Format date
Format
AD A.D. BC B.C. CC E EE D DAY Day DD DDD DY AM A.M. PM P.M. HH HH12 HH24 J MI MM MON Mon

Description AD indicator without punctuations AD indicator with punctuations BC indicator without punctuations BC indicator with punctuations Century indicator (One greater than the first two digits of the current year) Abbreviated Era name Era name fully spelled out Number of days in week Day fully spelled out in all capital letters Day fully spelled out with first letter capitalized Number of days in month Number of days in year, since Jan 1 3 letter abbreviation of the day Meridian indicator without punctuations Meridian indicator with punctuations Meridian indicator without punctuations Meridian indicator with punctuations Hours of day Hours of day Hours in the 24-hour clock format Julian - days since December 31, 4713 B.C. Minutes of hour Number of month 3 letter abbreviation of Month in all capitals 3 letter abbreviation of Month with first letter capitalized
(continued)

872

Appendix A 3 Command Reference

Table A-14 (Continued)

Format
MONTH Month RM Q RR RRRR SS SSSSS IW W WW I IY IYY IYYY SYYYY YEAR Year Y YY YYY YYYY Y,YYYY fm

Description Month fully spelled out Month fully spelled out with first letter capitalized Roman numeral month Number of Quarter Last 2 digits of year relative to current date 4 digits of the year relative to current date (this returns Year 2000 dates in the year 2000) Seconds of hour Seconds of hour past midnight Weeks in year from ISO Standard Number of weeks in month Number of weeks in year 1 digit Year from ISO Standard 2 digit Year from ISO Standard 3 digit Year from ISO Standard 4 digit Year from ISO Standard Signed Year (- for BC) Year fully spelled out in all capitals Year fully spelled out with first letters capitalized Last 1 digit of the year Last 2 digits of the year Last 3 digits of the year Full four-digit year Full four-digit year with comma punctuation Prefix to Month or Day. fm suppresses padding for Month or Day. Months and days are only as long as their count of characters. (For use with TO_CHAR function only.) Suffix to a number. Capitalization comes through the case of the number and not from the case of TH. (For use with TO_CHAR function only.)

Appendix A 3 FORMAT NUMBER

873

Format
SP

Description Suffix to a number forcing it to be spelled out. DDSP, DdSP and ddSP produce SEVEN, Seven, seven respectively. Capitalization comes through the case of the number and not from the case of SP. (For use with TO_CHAR function only.) Suffix combination of SP and TH forcing the number to be both spelled out and be given an ordinal suffix: Mmspth produces Seventh. Capitalization comes through the case of the number and not from the case of SPTH. (For use with TO_CHAR function only.) Same as SPTH. (For use with TO_CHAR function only.)

SPTH

THSP

FORMAT NUMBER
Table A-15 Format number
Format
9 0 $ B MI S PR D G C L , .

Description Value with the specified number of digits. Leading space, if positive and minus, if negative Leading zero Leading dollar sign Blank for the integer part of a fixed point number Negative value with a trailing minus sign Value with the specified number of digits. Leading plus, if positive and minus, if negative. Negative value in <angle brackets> A decimal point at the specified position A group separator at the specified position The ISO currency symbol at the specified position The local currency symbol at the specified position A comma at the specified position A decimal point at the specified position
(continued)

874

Appendix A 3 Command Reference

Table A-15 (Continued)

Format
V EEEE RN Rn FM

Description A value multiplied by 10n where n is the number of digits after V A value in scientific notation A value in uppercase Roman numerals A value in lowercase Roman numerals A value with no leading or trailing blanks

Appendix A 3 GET

875

GET
GET
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 9

Section Selecting Your Tool

Variables in the syntax

filename:

the operating system file you would like SQL*Plus to load into the SQL

buffer
ext:

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

Description
To load the given file within the SQL buffer, use the GET command. If you will specify the LIST option, SQL*Plus will display the files contents. If you will specify the NOLIST option, SQL*Plus will not display the files contents. If the files name will contain the words list or file, you must specify the files name within double quotes.

Examples
SQL
GET LOAN_RPT GET [Link]

876

Appendix A 3 Command Reference

GLB
GLB
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

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. This is a variable of type MLSLABEL.

mls:

Description
The GLB function will return the greatest lower bound of mls for a group of rows. The MLSLABEL datatype is used with Trusted Oracle.

Appendix A 3 GOTO

877

GOTO
GOTO
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

calculate_loan:

a label marker

Description
By using the GOTO command, you can execute an unconditional branch.

Examples
PL/SQL
BEGIN ... GOTO <<calculate_total_salary>>; ... <<calculate_total_salary>> Total_Salary:= Base_Salary + Commission + Bonus; END

878

Appendix A 3 Command Reference

GRANT
GRANT
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

system_privilege:

the system privilege you would like to grant to the User, or

Role
role: user:

the Role you would like to grant to the User the User to whom you would like to grant the system privilege, or Role

object_privilege:

the Object privilege you would like to grant to the User, or Role. The Object privilege will be one of the following: 3 ALTER 3 DELETE 3 EXECUTE 3 INDEX 3 INSERT 3 REFERENCES 3 SELECT 3 UPDATE

column:

the specific Column within the given Table you would like to grant the Object privilege to the given User, or Role

Appendix A 3 GRANT

879

schema:

the Schema containing the Object on which you would like to grant the Object privilege to the given User, or Role the Object on which you would like to grant the Object privilege to the given User, or Role

object:

Description
To grant a system privilege or Role to a User or Role, use the GRANT command. To grant a system privilege to a User who can grant system privileges or Roles to other Users and Roles, use WITH ADMIN OPTION with the GRANT command. To grant a system privilege or Role to all the Users, use the PUBLIC option with the GRANT command. To grant an Object privilege on the given Column within the given Object to the given User or Role, use the GRANT command. By using the WITH GRANT OPTION, you can grant the User the ability to grant Object privileges to other Users and Roles. To grant an Object privilege to all the Users, use the GRANT command with the PUBLIC option.

Examples
SQL
GRANT CREATE TABLE TO gavaskar; GRANT team_leader TO crystal; GRANT INSERT, UPDATE ON sales TO larry WITH GRANT OPTION; GRANT ALL TO PUBLIC;

880

Appendix A 3 Command Reference

GREATEST
GREATEST
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

expn:

any valid expression or Column.

Description
The GREATEST function returns greatest value amongst the list of expressions. All expressions are converted to the datatype of the first expression. In case of comparing character, a character is greater than another if it has a higher value in the database character set.

Examples
SQL
SELECT GREATEST(JOHN, JONNY, JANARTHAN) GREATEST FROM DUAL; GREAT ------------JONNY

Appendix A 3 GREATEST_LB

881

GREATEST_LB
GREATEST_LB
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

label:

a variable of type MLSLABEL or RAW MLSLABEL.

Description
The GREATEST_LB function returns a RAW MLSLABEL with the greatest lower bound value amongst the list of labels. The MLSLABEL or RAW MLSLABEL datatypes are used with Trusted Oracle.

882

Appendix A 3 Command Reference

HEXTORAW
HEXTORAW
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a character variable specified in quotes that contains hexadecimal values.

Description
The HEXTORAW function converts the hexadecimal value to a RAW datatype.

Examples
SQL
SELECT HEXTORAW(animation) Animation FROM animation; Animation ---------------------------------------3D

Appendix A 3 HOST

883

HOST
HOST
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

command: the operating system command you would like to execute from within SQL*Plus

Description
To execute the host operating systems command from within SQL*Plus, use the HOST command.

Examples
SQL
HOST ls -d HOST ls *.rpt

884

Appendix A 3 Command Reference

IF-THEN
IF-THEN
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

condition_1:

a certain condition that will be evaluated a sequence of statements PL/SQL will execute

statement_1,...,statement_n: if condition_1 evaluates to true

Description
Use the IF-THEN-ELSE control structure to execute a sequence of statements conditionally.

Examples
PL/SQL
IF sales > quota THEN Total_Salary:= Base_Salary + bonus; END IF;

Appendix A 3 IF-THEN-ELSE

885

IF-THEN-ELSE
IF-THEN-ELSE
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

condition_1:

a certain condition that will be evaluated a sequence of statements PL/SQL will execute

statement_1,....,statement_n:

if condition_1 evaluates to true

statement_11,...,statement_n1: a sequence of statements PL/SQL will execute if condition_1 evaluates to false

Description
Use the IF-THEN-ELSEIF control structure to execute a sequence of statements conditionally.

Examples
PL/SQL
IF sales > quota THEN Total_Salary:= Base_Salary + Commission + Bonus; ELSE Total_Salary:= Base_Salary + Commission; END IF;

886

Appendix A 3 Command Reference

IF-THEN-ELSEIF
IF-THEN-ELSEIF
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

condition_1: condition_2:

first condition that will be evaluated next condition that will be evaluated a sequence of statements PL/SQL will execute

statement_1,...,statement_n: if condition_1 evaluates to true

statement_11,...,statement_n1: a sequence of statements PL/SQL will execute if condition_2 evaluates to true statement_12,...,statement_n2: a sequence of statements PL/SQL will execute if both condition_1 and condition_2 evaluate to false

Description
Use the IF-THEN-ELSEIF control structure to execute a sequence of statements conditionally.

Appendix A 3 IF-THEN-ELSEIF

887

Examples
PL/SQL
IF sales > quota THEN Total_Salary:= Base_Salary + Commission + Bonus; ELSEIF sales = quota THEN Total_Salary:= Base_Salary + Commission; ELSE Total_Salary:= Base_Salary; END IF;

888

Appendix A 3 Command Reference

INITCAP
INITCAP
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a character variable that should be specified within single quotes

Description
The function converts a string into sentence case with the first letter of each word in uppercase. All the other letters of the word are in lowercase. Words are delimited by white space or characters that are not alphanumeric. The function returns a variable of datatype CHAR.

Examples
PL/SQL
Var1:= INITCAP (oracle is a good database);

SQL
SELECT INITCAP(oracle is a good database.) Sentence FROM DUAL; Sentence -------------------------Oracle Is A Good Database.

Appendix A 3 INPUT

889

INPUT
INPUT
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

text:

the text you would like to append to the SQL buffer

Description
To append a single line of text to the SQL buffer, use the INPUT command. To append more than one line of text to the SQL buffer, use the INPUT command with no text parameter. SQL*Plus, in turn, will prompt you for the text.

Examples
SQL
I WHERE state = FL INPUT ORDER BY last_name

890

Appendix A 3 Command Reference

INSERT
INSERT
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

table: a Table within the database to which the User inserting the data has INSERT privileges column_1,...,column_n:

specific Columns of the Table for which the given data

will be inserted
sql_expression_1,...,sql_expression_n:

a sequence of valid SQL

expressions

Description
Use the INSERT command to insert a new row of data into a Table, or view. By using the INSERT command, you can insert one row of data at a time. You can also use a sub query in an INSERT statement to insert multiple rows in a Table. All the INSERT Triggers on a Table would get fired when you fire the INSERT statement. To issue an INSERT command, you must have the INSERT privilege on the particular Table, or view.

Appendix A 3 INSERT

891

Examples
SQL*Plus
INSERT INTO employees VALUES (John Doe, 124561123, Manager); INSERT INTO employees (employee_name, employee_id) VALUES (John Doe, 124561123); INSERT INTO employees (SELECT * FROM department_head) ;

892

Appendix A 3 Command Reference

INSTR
INSTR
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 9

Section SQL Functions in queries

Variables in the syntax

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

a numeric variable that refers to the position of the start of search. The default is 1. This value is optional.

a positive numeric variable that refers to the number of occurrence that should be searched for. The default is 1. This value is optional.

Description
The function INSTR searches w for the zth occurrence of the string x beginning from the yth position. If y is negative, Oracle searches backwards from the end of w. If the search is unsuccessful, Oracle returns 0.

Examples
PL/SQL
Var1:= INSTR (Oracle Training, ra, 1, 2);

Appendix A 3 INSTR

893

SQL
SELECT INSTR (Oracle Training, ra, 1, 2) Instring FROM DUAL; Instring --------9

894

Appendix A 3 Command Reference

INSTRB
INSTRB
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a numeric variable that refers to the position of the start of search. The default is 1. This value is optional.

a positive numeric variable that refers to the number of occurrences that should be searched for. The default is 1. This value is optional.

Description
The function INSTRB is the same as INSTR function except for the fact that INSRT returned a character value whereas INSTRB returns a byte value. The function INSTRB searches w for the zth occurrence of the string x beginning from the yth position. If y is negative, Oracle searches backwards from the end of w. If the search is unsuccessful, Oracle returns 0.

Appendix A 3 INSTRB

895

Examples
PL/SQL
Var1:= INSTRB (Oracle Training, ra, 1, 2);

SQL
SELECT INSTRB (Oracle Training, ra, 1, 2) Instring Bytes FROM DUAL; Instring Bytes -------------9

896

Appendix A 3 Command Reference

INTERSECT
INTERSECT
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 10

Section Union, Union All, Minus

Variables in the syntax

b1 and b2 are select statements

Description
The INTERSECT is a set operator that combines the results of two SELECT statements. The INTERSECT will provide a query result that has distinct rows selected by both b1 and b2 SQL.

Examples
SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE = 09-FEB-96 INTERSECT SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE = 09-FEB-97 ; SHIPPING_T ---------299.95

Appendix A 3 INVALID_CURSOR

897

INVALID_CURSOR
INVALID_CURSOR
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
If you try to perform an illegal operation on a cursor, PL/SQL will implicitly raise a pre-defined exception of INVALID_CURSOR. For example, if you close an unopened cursor, PL/SQL will implicitly raise the exception of INVALID_CURSOR. The corresponding Oracle error and SQLCODE values are ORA-01001 and -1001 respectively.

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

898

Appendix A 3 Command Reference

INVALID_NUMBER
INVALID_NUMBER
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
If you try to convert a string into a number and the string does not represent a valid number, PL/SQL will implicitly raise a pre-defined exception of INVALID_NUMBER. The corresponding Oracle error and SQLCODE values are ORA-01722 and 1722 respectively.

Examples
PL/SQL
DECLARE total_salary NUMBER; BEGIN total_salary:= TO_NUMBER (ABC); EXCEPTION WHEN INVALID_NUMER THEN UPDATE APPLICATION_ERROR_TABLE SET ERROR = INVALID NUMBER CONVERSION ; END;

Appendix A 3 KEYWORDS

899

KEYWORDS
Oracle has a list of words that are not reserved but have been used within the Oracle syntax. These words are known as Keywords. You can use these Keywords for variable or object names but it is highly recommended that you avoid using them as it would make your code more difficult to read and understand. The following Table contains a list of Keywords.

Table A-16 Alphabetical List of Keywords

Keyword ADMIN ARCHIVE BACKUP BLOCK CACHE CHARACTER COMMIT CONTENTS CURSOR DATABASE DECLARE DUMP EACH EVENTS EXPLAIN FETCH FORCE FUNCTION GO INCLUDING INT KEY
(continued)

Keyword AFTER ARCHIVELOG BEGIN BODY CANCEL CHECKPOINT COMPILE CONTINUE CYCLE DATAFILE DISABLE

Keyword ALLOCATE AUTHORIZATION BECOME

Keyword ANALYZE AVG BEFORE

CASCADE CLOSE CONSTRAINT CONTROLFILE

CHANGE COBOL CONSTRAINTS COUNT

DBA DISMOUNT

DEC DOUBLE

ENABLE EXCEPT EXECUTE FLUSH FOREIGN

END EXCEPTIONS EXTENT FREELIST FORTRAN

ESCAPE EXEC EXTERNALLY FREELISTS FOUND

GOTO INDICATOR

GROUPS INITRANS INSTANCE

900

Appendix A 3 Command Reference

Table A-16 (Continued)

Keyword LANGUAGE LOGFILE MANAGE MAXINISTANCES MAXTRANS MINVALUE NEXT NOCYCLE NOORDER NUMERIC OFF OPEN PACKAGE PCTUSED PRIMARY QUOTA READ REFERENCING ROLE SAVEPOINT SEGMENT SOME STATEMENT_ID SUM TABLES TIME TRUNCATE UNDER USING WHEN WRITE WORK UNLIMITED UNTIL USE REAL RESETLOGS ROLES SCHEMA SEQUENCE SORT STATISTICS SWITCH TABLESPACE TRACING RECOVER RESTRICTED ROLLBACK SCN SHARED SQLCODE STOP SYSTEM TEMPORARY TRANSACTION THREAD TRIGGERS SECTION SNAPSHOT SQLERROR STORAGE REFERENCES REUSE OLD OWN PARALLEL PLAN PRIVATE PASCAL PLI PROCEDURE PCTINCREASE PRECISION PROFILE ONLY OPTIMAL MANUAL MAXLOGFILES MAXVALUE MODULE NEW NOMAXVALUE NORESETLOGS MAX MAXLOGHISTORY MIN MOUNT NOARCHIVELOG NOMINVALUE NORMAL NOCACHE NONE NOSORT MAXDATAFILES MAXLOGMEMBERS MINEXTENTS Keyword LAYER Keyword LINK Keyword LISTS

Appendix A 3 LABELS

901

LABELS
LABELS
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

my_label:

an undeclared identifier

statement_1,...,statement_n: a sequence of statements

Description
To improve code readability, use labeled loops. Especially in the case of nested loops.

Examples
PL/SQL
<<calculate_salary>> LOOP new_salary:= base_salary + bonus + commission; END LOOP calculate_salary;

902

Appendix A 3 Command Reference

LAST_DAY
LAST_DAY
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a valid date variable

Description
The LAST_DAY function returns the last day of the month of the date specified in the argument d.

Examples
PL/SQL
Days_Left:= LAST_DAY(SYSDATE) - SYSDATE;

SQL
SELECT LAST_DAY(SYSDATE) Last Day FROM DUAL; Last Day --------30-NOV-97

Appendix A 3 LEAST

903

LEAST
LEAST
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

expn:

any valid expression or Column.

Description
The LEAST function returns lowest value amongst the list of expressions. All expressions are converted to the datatype of the first expression. In case of comparing character, a character is lower than another if it has a lower value in the database character set.

Examples
SQL
SELECT LEAST(JOHN, JONNY, JANARTHAN) LEAST FROM DUAL; LEAST --------JANARTHAN

904

Appendix A 3 Command Reference

LEAST_LB
LEAST_LB
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

label:

a variable of type MLSLABEL or RAW MLSLABEL.

Description
The LEAST_LB function returns a RAW MLSLABEL with the least upper bound value amongst the list of labels. The MLSLABEL or RAW MLSLABEL datatypes are used with Trusted Oracle.

Appendix A 3 LENGTH

905

LENGTH
LENGTH
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

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

Description
The function LENGTH returns the number of characters in the argument x. If x is null, the function returns a null value. If x is a character, the length would include all trailing blanks.

Examples
PL/SQL
Var1:= LENGTH (Oracle);

SQL
SELECT LENGTH (Oracle) Length FROM DUAL; Length --------6

906

Appendix A 3 Command Reference

LENGTHB
LENGTHB
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

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

Description
The function LENGTHB returns the length of the argument x in bytes. If x is null, the function returns a null value. If x is a character, the length would include all trailing blanks. For a single-byte database character set, LENGTHB is equivalent to LENGTH.

Examples
PL/SQL
Var1:= LENGTHB (Oracle);

SQL
SELECT LENGTHB (Oracle) Length in bytes FROM DUAL; Length in bytes --------------6

Appendix A 3 LIKE

907

LIKE
LIKE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

column1: value:

a CHAR or VARCHAR2 Column where you would like to search

the values that look like the pattern you are searching for the pattern can be denoted in 3 ways:

pattern:

Table A-17 Examples of search patterns for LIKE

Pattern % _ % Example abc% __a %a%a% Searches for Values beginning with abc Values that have a in the third position Values that have two as in it

Description
The LIKE operator is an extremely powerful feature from Oracle. You can use the LIKE operator to search the database for values that look like a pattern that you describe with this operator. The % sign is known as wild card.

908

Appendix A 3 Command Reference

The _ sign is known as position marker.

Tip

The pattern matching with Oracle is case sensitive.

Examples
SQL
SELECT NAME_OF_AGENT AGENT NAME FROM SALES WHERE NAME_OF_AGENT LIKE MICHAEL%; AGENT NAME ------------------MICHAEL GEORGE MICHAEL JORDAN MICHAEL MAGNUM SELECT NAME_OF_AGENT FROM SALES WHERE NAME_OF_AGENT LIKE __C; AGENT NAME ------------------MICKY ARCHIE MICHAEL

Appendix A 3 LIST

909

LIST
LIST
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

the line number within the SQL buffer you would like SQL*Plus to list lines n through m within the SQL buffer you would like SQL*Plus to list

n m: n *:

lines n through the current line within the SQL buffer you would like SQL*Plus to list

n LAST: lines n through the last line within the SQL buffer you would like SQL*Plus to list *:

the current line within the SQL buffer you would like SQL*Plus to list

* n: the current line through line n within the SQL buffer you would like SQL*Plus to list * LAST:

the current line through the last line within the SQL buffer you would like SQL*Plus to list the last line within the SQL buffer you would like SQL*Plus to list

LAST:

Description
To list one or more lines of the SQL buffer, use the LIST command.

910

Appendix A 3 Command Reference

Examples
SQL
L 2 LIST 2 LAST

Appendix A 3 LN

911

LN
LN
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a numeric variable greater than 0.

Description
The LN function returns the logarithm of a number.

Examples
PL/SQL
Var1:= LN (12);

SQL
SELECT LN (12) Logarithm of 12 FROM DUAL; Logarithm of 12 --------------2.4849066

912

Appendix A 3 Command Reference

LOCK TABLE
LOCK TABLE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

table_1,...,table_n: a sequence of database Tables you would like to lock by using the LOCK TABLE statement lock_mode:

the type of lock you would like to set for the database Tables. You can choose from one the following lock types: 3 EXCLUSIVE 3 SHARE ROW EXCLUSIVE 3 SHARE 3 SHARE UPDATE 3 ROW SHARE 3 ROW EXCLUSIVE

NOWAIT:

Oracle will not wait to lock the given Table(s), if the Table(s) is(are) not

available

Description
Use the LOCK TABLE statement to lock one or more database Tables in the specified mode.

Appendix A 3 LOCK TABLE

913

Examples
SQL
LOCK LOCK LOCK LOCK LOCK LOCK TABLE TABLE TABLE TABLE TABLE TABLE loan IN SHARE MODE ; region IN EXCLUSIVE MODE NOWAIT; acct IN SHARE UPDATE MODE; bank IN ROW EXCLUSIVE MODE NOWAIT; user IN SHARE ROW EXCLUSIVE MODE; branch IN ROW SHARE MODE NOWAIT;

914

Appendix A 3 Command Reference

LOG
LOG
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

m: n:

any positive numeric variable other than 0 and 1. any positive numeric variable.

Description
The LOG function returns the logarithm, base m, of n.

Examples
PL/SQL
Var1:= LOG (12, 2);

SQL
SELECT LOG (12, 2) Log base 2 of 12 FROM DUAL; Log base 2 of 12 ---------------.27894295

Appendix A 3 LOGIN_DENIED

915

LOGIN_DENIED
LOGIN_DENIED
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
If the User specifies an invalid username and/or password, PL/SQL will implicitly raise a pre-defined exception of LOGIN_DENIED. The corresponding Oracle error and SQLCODE values are ORA-01017 and -1017 respectively.

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

916

Appendix A 3 Command Reference

LOOP
LOOP
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements PL/SQL will execute

multiple times

Description
Use the LOOP structure to execute a sequence of statements multiple times.

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

Appendix A 3 LOWER

917

LOWER
LOWER
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

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

Description
The function converts the string into lowercase. 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:= LOWER (ORACLE);

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

918

Appendix A 3 Command Reference

LPAD
LPAD
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

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 left side. The function returns x left-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:= LPAD (Oracle, 1);

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

Appendix A 3 LTRIM

919

LTRIM
LTRIM
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

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 left side until it reaches a character that does not belong to the y character set.

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

SQL
SELECT LTRIM (The Theresa of all mothers, The) Example of LTrim FROM DUAL; Example of LTrim ----------------------Theresa of all mothers

920

Appendix A 3 Command Reference

LUB
LUB
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

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. This is a variable of type MLSLABEL.

mls:

Description
The LUB function will return the least upper bound of mls for a group of rows. The MLSLABEL datatype is used with Trusted Oracle.

Appendix A 3 MAKE_REF

921

MAKE_REF
MAKE_REF
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 9

Section Querying Objects

Variables in the syntax

view: key:

the object view whose row you want to create a REF for the Key that the command will use as the Primary Key

Description
By using the given Key as the Primary Key, the MAKE_REF command will create a REF to a row within the given Object view.

Examples
SQL
CREATE TYPE loan_obj AS OBJECT (loan_amount NUMBER, interest_rate NUMBER); CREATE TABLE loan_table (loan_amount NUMBER, interest_rate NUMBER); CREATE VIEW loan_view OF loan_table WITH OBJECT loan_view_obj(loan_amount, interest_rate) AS SELECT * from loan_table; SELECT MAKE_REF(loan_view_obj, 1, 3) FROM DUAL;

922

Appendix A 3 Command Reference

MAX
MAX
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

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 MAX function will return the maximum value of expn for a group of rows.

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

SQL
SELECT MAX (DAILY_SALES) Maximum Sales FROM SALES; Maximum Sales -------------133

Appendix A 3 MIN

923

MIN
MIN
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

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 MIN function will return the minimum value of expn for a group of rows.

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

SQL
SELECT MIN (DAILY_SALES) Minimum Sales FROM SALES; Minimum Sales -------------12

924

Appendix A 3 Command Reference

MINUS
MINUS
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

b1 and b2 are select statements

Description
The MINUS is a set operator that combines the results of two SELECT statements. The MINUS will provide a query result that has distinct rows selected by b1 SQL but not the b2 SQL.

Examples
SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE = 09-FEB-96 MINUS SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE = 09-FEB-97 ; SHIPPING_T ---------199.95

Appendix A 3 MOD

925

MOD
MOD
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also

Syntax
MOD (x , y)

Variables in the syntax

x: y:

numeric variable numeric variable

Description
The MOD function returns the remainder of x divided by y. If y is 0, the function returns 0.

Examples
PL/SQL
Var1:= MOD (12, 5);

SQL
SELECT MOD (12, 5) Modulus Value of 12/5 FROM DUAL; Modulus Value of 12/5 --------------------2

926

Appendix A 3 Command Reference

MONTHS_BETWEEN
MONTHS_BETWEEN
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

d1 & d2:

are valid date variables

Description
The MONTHS_BETWEEN function returns number of months between the two dates i.e. d1 and d2. If d1 > d2, the result is positive. If d1 < d2, the result is negative. If d1 and d2 are in the same month or are both the last days of their respective months, the result is always an integer. In all other cases, the function will calculate the result in fractional portion based on a 31-day month and also considers the time difference.

Examples
PL/SQL
MONTHS_INBETWEEN:= SYSDATE - SALES_DATE;

SQL
SELECT MONTHS_BETWEEN (SYSDATE, 26-JAN-98) MONTHS_INBETWEEN FROM DUAL; MONTHS_INBETWEEN ----------------2.53143

Appendix A 3 NEW_TIME

927

NEW_TIME
NEW_TIME
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a valid date variable any one of the following time zones specified in quotes:

tz1 & tz2:

Table A-18 Time zone specifications for NEW_TIME

Time Zone1 AST BST CST EST GMT HST MST NST PST YST YDT PDT HDT MDT Time Zone2 ADT BDT CDT EDT Description Atlantic Standard or Daylight Time Bering Standard or Daylight Time Central Standard or Daylight Time Eastern Standard or Daylight Time Greenwich Mean Time Alaska-Hawaii Standard Time or Daylight Time Mountain Standard or Daylight Time Newfoundland Standard Time Pacific Standard or Daylight Time Yukon Standard or Daylight Time

928

Appendix A 3 Command Reference

Description
The NEW_TIME function returns the corresponding date and time in tz2 as compared to the date and time in tz1 for the argument d.

Examples
PL/SQL
LONDON_TIME:= NEW_TIME (SYSDATE, EST, GMT)

SQL
SELECT NEW_TIME (SYSDATE, EST, GMT) LONDON_TIME FROM DUAL; LONDON_TI --------09-NOV-97

Appendix A 3 NEXT_DAY

929

NEXT_DAY
NEXT_DAY
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a valid date variable

x: a character variable specified in quotes that is a particular day of the week (MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, and SUNDAY)

Description
The NEXT_DAY function returns the next date that falls on the day named by x.

Examples
PL/SQL
DATE_FOR_NEXT_WEEK:= NEXT_DAY(26-JAN-47,TUESDAY);

SQL
SELECT NEXT_DAY(26-JAN-47,TUESDAY) DATE_AFTER_TUESDAY_26JAN-47 FROM DUAL; DATE_AFTER --------28-JAN-47

930

Appendix A 3 Command Reference

NEXTVAL
NEXTVAL
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

user:

the User where the sequence was created the sequence whose next value you want to retrieve

sequence:

Description
Use the NEXTVAL statement to retrieve the next value within the sequence from the database. The NEXTVAL statement also updates the current value of the sequence. You can use the sequence to create Unique numbers that you can use within your Tables as Primary identifiers.

Examples
SQL*Plus
SELECT loan_seq.NEXTVAL FROM DUAL;

Appendix A 3 NLS_CHARSET_DECL_LEN

931

NLS_CHARSET_DECL_LEN
NLS_CHARSET_DECL_LEN
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 10

Section Inserting Rows

Variables in the syntax

len:

the length of the Column the character set ID of the Column

char_id:

Description
The NLS_CHARSET_DECL_LEN is new with Oracle8. This function returns the declaration length of an NCHAR Column. The length is returned in the number of characters.

Examples
SQL
SELECT NLS_CHARSET_DECL_LEN (130, nls_charset_id (char_cs)) FROM DUAL;

932

Appendix A 3 Command Reference

NLS_CHARSET_ID
NLS_CHARSET_ID
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a string variable specified in quotes

Description
The NLS_CHARSET_ID is new with Oracle8. It returns the NLS character set ID that corresponds to the NLS character set name. The text value CHAR_CS returns the servers database character set ID. The text value NCHAR_CS returns the servers national character set ID. An invalid character name set returns null value.

Examples
SQL
SELECT NLS_CHARSET_ID (char_cs)) FROM DUAL; SELECT NLS_CHARSET_ID (nchar_cs)) FROM DUAL;

Appendix A 3 NLS_CHARSET_NAME

933

NLS_CHARSET_NAME
NLS_CHARSET_NAME
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a valid numeric value

Description
The NLS_CHARSET_NAME is new with Oracle8. It returns the NLS character set name that corresponds to the NLS character set ID. An invalid character set ID returns null value.

Examples
SQL
SELECT NLS_CHARSET_NAME (2) FROM DUAL; SELECT NLS_CHARSET_NAME (1001) FROM DUAL;

934

Appendix A 3 Command Reference

NLS_INITCAP
NLS_INITCAP
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a character variable that should be specified within single quotes

nlsparm: this variable specifies the sort criteria. It can either be linguistic sort sequence or BINARY sort sequence. The linguistic sort sequence handles special linguistic requirements for case conversions. This parameter is optional and if not provided, this function uses the default sort sequence for your session. The value of nlsparams can have this form: NLS_SORT = sort

Description
The function NLS_INITCAP converts a string into sentence case with the first letter of each word in uppercase. All the other letters of the word are in lowercase. Words are delimited by white space or characters that are not alphanumeric. The function returns a variable of datatype char.

Examples
SQL
SELECT NLS_INITCAP(strutz, NLS_SORT = XGerman) Sentence FROM DUAL; Senten -----Strutz

Appendix A 3 NLS_LOWER

935

NLS_LOWER
NLS_LOWER
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a character variable that should be specified within single quotes

Description
The function NLS_LOWER converts the string into lowercase. 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
SQL
SELECT NLS_LOWER (strutz, NLS_SORT = XGerman) LowerCase Sentence FROM DUAL; LowerC -----strutz

936

Appendix A 3 Command Reference

NLS_UPPER
NLS_UPPER
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a character variable that should be specified within single quotes

Description
The function NLS_UPPER 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
SQL
SELECT NLS_UPPER (strutz, NLS_SORT = XGerman) UpperCase Sentence FROM DUAL; UpperC -----STRUTZ

Appendix A 3 NO_DATA_FOUND

937

NO_DATA_FOUND
NO_DATA_FOUND
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
If a SELECT INTO statement returns no rows, PL/SQL will implicitly raise a pre-defined exception of NO_DATA_FOUND. The corresponding Oracle error and SQLCODE values are ORA-01403 and +100 respectively.

Examples
PL/SQL
BEGIN OPEN loan_cur; LOOP FETCH loan_cur INTO loan_rec; EXIT WHEN loan_cur%NOTFOUND; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, No Data Found); END;

938

Appendix A 3 Command Reference

NOAUDIT
NOAUDIT
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 20

Section

Variables in the syntax

statement:

the statement for which you would like to stop auditing the system privilege for which you would like to stop

system_privilege:

auditing
user:

the User for which you would like to stop auditing

Description
To stop auditing a system privilege or statement for the given User, use the NOAUDIT command. To stop auditing a system privilege, Object operation, or statement when the system privilege, object operation, or statement is successful, use the NOAUDIT command with the WHENEVER SUCCESSFUL option. To stop auditing a system privilege, Object operation, or statement when the system privilege, object operation, or statement fails, use the NOAUDIT command with the WHENEVER NOT SUCCESSFUL option. To stop auditing a given operation on the given Object within the given Schema, use the NOAUDIT command.

Appendix A 3 NOAUDIT

939

Examples
PL/SQL
NOAUDIT SELECT TABLE BY john; NOAUDIT CREATE TABLE BY martha; NOAUDIT SELECT ON [Link] WHENEVER SUCCESSFUL;

940

Appendix A 3 Command Reference

NOT_LOGGED_ON
NOT_LOGGED_ON
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
If the program has no connection established with the Oracle database and the program tries to execute a database command, PL/SQL will implicitly raise a predefined exception of NOT_LOGGED_ON. The corresponding Oracle error and SQLCODE values are ORA-01012 and -1012 respectively.

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

Appendix A 3 NULL

941

NULL
NULL
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

condition_1:

a certain condition PL/SQL will evaluate sequence of statements PL/SQL will execute

statement_1,...,statement_n: iteratively if condition_1 is true

Description
A NULL statement will pass control to the next statement within the code. In addition, a NULL statement will provide code readability.

Examples
PL/SQL
IF sales > quota THEN Total_Salary:= Base_Salary + Commission + Bonus; ELSE NULL; END IF;

942

Appendix A 3 Command Reference

NVL
NVL
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 9

Section SQL Functions in Queries

Variables in the syntax

value: others

a variable of any datatype including CHAR, VARCHAR2, NUMBER, DATE, and

substitute: a variable of the same datatype as value. If substitute is of a different datatype than value, the function will first covert it to the datatype of value.

Description
The NVL function returns substitute, if value is NULL. If value is NOT NULL, it returns value itself.

Examples
SQL
SELECT NVL(SALES_AGENT, AGENT NAME NOT AVAILABLE) NAMES OF SALES AGENT FROM SALES; NAMES OF SALES AGENT ----------------------------------JOHN HOPKINS MICHAEL JORDAN AGENT NAME NOT AVAILABLE

Appendix A 3 OPEN

943

OPEN
OPEN
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

cursor_name:

name of the explicit cursor you would like to open parameters you will need to pass to the cursor,

parameter_1,...,parameter_n:

if applicable

Description
Use the OPEN statement to open an explicit cursor, and execute the query associated with the cursor.

Examples
PL/SQL
OPEN loan_cur; OPEN loan_cur (1, Bank Holdings, Inc., 500000)

944

Appendix A 3 Command Reference

OPEN-FOR
OPEN-FOR
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

cursor_name:

name of the explicit cursor you would like to open name of cursor variable declared within a PL/SQL

host_cursor_variable_name:

host environment
select_statement:

a valid SELECT statement that will return a set of values for

the cursor

Description
Use the OPEN-FOR statement to open a cursor, and execute the query specified within the OPEN-FOR statement.

Examples
PL/SQL
OPEN loan_cur FOR SELECT * FROM loan; OPEN:emp_cur FOR SELECT * FROM employee; OPEN salary_cur FOR SELECT * FROM salary;

Appendix A 3 OPERATOR

945

OPERATOR
Operators provide a mechanism to manipulate data. These operators are similar to the ones you use in your normal mathematical calculation like + for addition, for subtraction and more. Apart from these, you also have logical operators like AND, OR, and NOT that help you build the logic. The result of data manipulation depends critically on the order in which the operators are placed. Oracle evaluates the result based on the precedence of the operators. Operators with higher precedence are evaluated before those with lower precedence and in case of operators with the same precedence, Oracle evaluates the operators from left to right within an expression. You can use parentheses in your expression to override precedence. Expressions within parentheses are evaluated before evaluating expressions outside the parentheses. Overall, Oracle follows the Boolean theory for evaluation of expressions. The following Table displays the level of precedence in Oracle for the operators.

Table A-19 Operators in Order of Precedence from Lowest to Highest

Precedence Rank 0 1 2 3 4 5 6 Operator + * = > NOT AND OR < != Operator / Operator PRIOR

946

Appendix A 3 Command Reference

OPERATOR < >

OPERATOR < >
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

See Also Other comparison operators like =, <, <=, >, >=

Syntax
b1 <> b2

Variables in the syntax

b1, b2:

numeric variables

Description
The <> operator denotes not equal to. Use the <> operator to compare two variables that are not equal.

Examples
PL/SQL
IF DAILY_SALES <> THEN TARGET: = -1 END IF; (MONTHLY_SALES/30)

SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE <> 09-FEB-96; DISCOUNT_T ---------1222.12

Appendix A 3 OPERATOR >

947

OPERATOR >
OPERATOR >
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

See Also Other comparison operators like =, !=, <=, <, >=

Syntax
b1 > b2

Variables in the syntax

b1, b2:

numeric variables

Description
The > operator denotes greater than. Use the > operator to compare which variable is greater than the other.

Examples
PL/SQL
IF DAILY_SALES > (MONTHLY_SALES/30) THEN TARGET: = -1 END IF;

SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE > 09-FEB-96; DISCOUNT_T ---------168.5

948

Appendix A 3 Command Reference

OPERATOR > =
OPERATOR > =
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

See Also Other comparison operators like =, !=, <=, <, >

Syntax
b1 >= b2

Variables in the syntax

b1, b2:

numeric variables

Description
The >= operator denotes greater than or equal to. Use the >= operator to compare which variable is either greater than or equal to the other.

Examples
PL/SQL
IF DAILY_SALES >= (MONTHLY_SALES/30) THEN TARGET: = -1 END IF;

SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE >= 09-FEB-96; DISCOUNT_T ---------2268.5

Appendix A 3 OPERATOR ! =

949

OPERATOR ! =
OPERATOR ! =
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

See Also Other comparison operators like =, <, <=, < >, >, >=

Syntax
b1 != b2

Variables in the syntax

b1, b2:

numeric variables

Description
The != operator denotes not equal to. Use the != operator to compare two variables that are not equal.

Examples
PL/SQL
IF DAILY_SALES != (MONTHLY_SALES/30) THEN TARGET: = -1 END IF;

SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE != 09-FEB-96; DISCOUNT_T ---------1222.12

950

Appendix A 3 Command Reference

OPERATOR *
OPERATOR *
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

b1, b2:

numeric variables

Description
Use the * operator to multiply numeric values.

Examples
PL/SQL
AnnualSalary:= MonthlySalary * 12; DiscountAmount:= ProductValue * DiscountPercent; SQL*Plus SELECT SALES_AMOUNT * 10 DISCOUNT_TOTAL FROM DAILY_SALES WHERE SALES_DATE = 09-FEB-96; DISCOUNT_T ---------6850

Appendix A 3 OPERATOR +

951

OPERATOR +
OPERATOR +
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

b1, b2,..., bn:

numeric variables

Description
Use the + operator to add numeric values.

Examples
PL/SQL
NewSalary:= CurrentSalary + Bonus + Commission + Raise; TotalPayment:= Mortgage + Interest + RealEstateTaxes + Insurance;

SQL
SELECT SALES_AMOUNT + 4.95 SHIPPING_TOTAL FROM DAILY_SALES WHERE SALES_DATE = 09-FEB-96; SHIPPING_T ---------699.95

952

Appendix A 3 Command Reference

OPERATOR OPERATOR Difficulty

Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

b1, b2:

numeric variables

Description
Use the operator to subtract numeric values.

Examples
PL/SQL
NetProfit:= Revenue - Expenses; NetPay:= GrossIncome - Taxes;

SQL
SELECT SALES_AMOUNT - 10.00 DISCOUNT_TOTAL FROM DAILY_SALES WHERE SALES_DATE = 09-FEB-96; DISCOUNT_T ---------685

Appendix A 3 OPERATOR /

953

OPERATOR /
OPERATOR /
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

b1, b2:

numeric variables

Description
Use the / operator to divide numeric values.

Examples
PL/SQL
MonthlySalary:= AnnualSalary / 12;

SQL
SELECT SALES_AMOUNT / 10 DISCOUNT_TOTAL FROM DAILY_SALES WHERE SALES_DATE = 09-FEB-96; DISCOUNT_T ---------68.5

954

Appendix A 3 Command Reference

OPERATOR < =
OPERATOR <=
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

See Also Other comparison operators like =, !=, <, >, >=

Syntax
b1 <= b2

Variables in the syntax

b1, b2:

numeric variables

Description
The <= operator denotes less than or equal to. Use the <= operator to compare which variable is either less than or equal to the other.

Examples
PL/SQL
IF DAILY_SALES <= (MONTHLY_SALES/30) THEN TARGET: = -1 END IF;

SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE <= 09-FEB-96; DISCOUNT_T ---------168.5

Appendix A 3 OPERATOR =

955

OPERATOR =
OPERATOR =
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

See Also Other comparison operators like !=, <, <=, < >, >, >=

Syntax
b1 = b2

Variables in the syntax

b1, b2:

numeric variables

Description
The = operator denotes equal to. Use the = operator to compare equality between two variables.

Examples
PL/SQL
IF DAILY_SALES = THEN TARGET: = 1 END IF; (MONTHLY_SALES/30)

SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE = 09-FEB-96; DISCOUNT_T ---------68.5

956

Appendix A 3 Command Reference

OPERATOR AND
OPERATOR AND
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

b1, b2:

any variables or results

Description
The AND is a logical operator that combines the results of two component conditions to produce a single result. The AND condition returns TRUE if both the component conditions are TRUE. It returns FALSE if any one or both of the component conditions is TRUE.

Examples
PL/SQL
IF (DAILY_SALES = MONTHLY_SALES/30) AND (ANNUAL_SALES > 0 )THEN BONUS: = 100 END IF;

SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE <= 09-FEB-96 AND SALES_DATE >= 09-MAR-96; DISCOUNT_T ---------368.5

Appendix A 3 OPERATOR BETWEEN

957

OPERATOR BETWEEN
OPERATOR BETWEEN
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also Other comparison operators like IN, NOT IN, ANY, ALL, BETWEEN

Syntax
WHERE column BETWEEN n AND m

Variables in the syntax

n and m:

any variables of the same datatype

Description
BETWEEN is a comparison operator used with a SQL statement. BETWEEN refers to

values that fall in the range. It will return TRUE if the value compared is greater than or equal to n and less than or equal to m.

Examples
SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE BETWEEN 09-FEB-96 AND 10-FEB-96; DISCOUNT_T ---------68.5 12.5

958

Appendix A 3 Command Reference

OPERATOR IN
OPERATOR IN
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter 9

Section Joining Tables

See Also Other comparison operators like NOT IN, ANY, ALL, BETWEEN

Syntax
WHERE column IN

Description
IN is a comparison operator used with a SQL statement. IN refers to any members

of the group. It will return TRUE if any of the members of the group satisfies the test condition.

Examples
SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE IN (09-FEB-96, 10-FEB-96); DISCOUNT_T ---------68.5 12.5

Appendix A 3 OPERATOR IS NOT NULL

959

OPERATOR IS NOT NULL

OPERATOR IS NOT NULL
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Appendix A 3 Command Reference

OPERATOR IS NULL
OPERATOR IS NULL
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

that the value is either unknown or is irrelevant.

Examples
SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE IS NULL; DISCOUNT_T ---------10.2

Appendix A 3 OPERATOR NOT

961

OPERATOR NOT
OPERATOR NOT
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

b1, b2:

results

Description
The NOT is a logical operator that combines the results of two component conditions to produce a single result. The NOT condition returns TRUE if the condition following it is false. It returns FALSE if the condition following it is true.

Examples
PL/SQL
IF NOT (DAILY_SALES != BONUS: = 100 END IF; MONTHLY_SALES/30) THEN

SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE NOT (SALES_DATE = 09-FEB-96); DISCOUNT_T ---------4468.5

962

Appendix A 3 Command Reference

OPERATOR NOT BETWEEN

OPERATOR NOT BETWEEN
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also Other comparison operators like IN, NOT IN, ANY, ALL, BETWEEN

Syntax
WHERE column NOT BETWEEN n AND m

Variables in the syntax

n and m:

any variables of the same datatype

Description
NOT BETWEEN is a comparison operator used with a SQL statement. NOT BETWEEN

refers to values that do not fall in the range. It will return TRUE if the value compared is not greater than or equal to n nor is less than or equal to m. It is a negation of the BETWEEN operator.

Examples
SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE NOT BETWEEN 09-FEB-96 AND 10-FEB-96; DISCOUNT_T ---------13 144.12

Appendix A 3 OPERATOR NOT IN

963

OPERATOR NOT IN
OPERATOR NOT IN
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also Other comparison operators like IN, ANY, ALL, BETWEEN

Syntax
WHERE column NOT IN

Description
NOT IN is a comparison operator used with a SQL statement. It is the negation of the IN operator. NOT IN refers to where no members of the group belongs. It will return TRUE if

none of the members of the group satisfies the test condition.

Examples
SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE NOT IN (09-FEB-96, 10-FEB-96); DISCOUNT_T ---------100.12 62.5

964

Appendix A 3 Command Reference

OPERATOR OR
OPERATOR OR
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

b1, b2:

any variables or results

Description
The OR is a logical operator that combines the results of two component conditions to produce a single result. The OR condition returns TRUE if any one of the component conditions is TRUE. It returns FALSE only if both of the the component conditions are FALSE.

Examples
PL/SQL
IF (DAILY_SALES = 0 )THEN BONUS: = 100 END IF; MONTHLY_SALES/30) OR (ANNUAL_SALES >

SQL
SELECT SALES_AMOUNT FROM DAILY_SALES WHERE SALES_DATE <= 09-FEB-96 OR SALES_DATE >= 09-MAR-96; DISCOUNT_T ---------3368.5

Appendix A 3 PRIOR

965

PRIOR
PRIOR
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

sql_expn: user: table:

a valid SQL expression

the owner of the Table the Table for the SQL SELECT the WHERE condition of the SQL SELECT

where_condition: expn:

any valid expression

Description
The PRIOR operator can be used within a SQL SELECT statement along with the CONNECT BY clause to draw a hierarchical structure. It is used most when the data can be represented in a tree-like structure. The position of PRIOR helps ORACLE to determine the hierarchy. You can make use of the WHERE clause to eliminate individual items in the hierarchy, but it would not be possible to eliminate both the individual and its descendent items unless you use a not equal sign with the CONNECT BY clause.

966

Appendix A 3 Command Reference

Examples
SQL
SELECT employee_name, department_name FROM employee CONNECT BY emp_no = PRIOR department_no ORDER BY department_no;

Appendix A 3 PROGRAM_ERROR

967

PROGRAM_ERROR
PROGRAM_ERROR
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

statement_1,...,statement_n:

a sequence of statements

Description
An internal error within PL/SQL will implicitly raise a pre-defined exception of PROGRAM_ERROR. The corresponding Oracle error and SQLCODE values are ORA-06501 and -6501 respectively.

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

968

Appendix A 3 Command Reference

PROMPT
PROMPT
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

See Also

Syntax
PROMPT [text]

Variables in the syntax

text:

the line of text you would like SQL*Plus to display

Description
To prompt the User with the given line of text, use the PROMPT command. To direct SQL*Plus to display a blank line, use the PROMPT command with no argument.

Examples
SQL
PROMPT PROMPT The system will be shut down within 10 minutes.

Appendix A 3 PSEUDOCOLUMN

969

PSEUDOCOLUMN
PSEUDOCOLUMN
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

See Also CURRVAL, LEVEL, NEXTVAL, NULL, ROWID, ROWNUM, SYSDATE, UID, USER

Description
A pseudocolumn is a Column that returns a value when selected. The pseudocolumn is not a Column of any Table, and is used in conjunction with the DUAL system Table. The list of pseudocolumns are: 3 [Link] 3 LEVEL 3 [Link] 3 NULL 3 ROWID 3 ROWNUM 3 SYSDATE 3 UID 3 USER

Examples
See sections on specific pseudocolumns for examples.

970

Appendix A 3 Command Reference

RAISE
RAISE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

exception_name:

a predefined or User defined exception

Description
Use the RAISE statement to stop execution of current block or sub program, and transfer control to the specified exception. You can specify a predefined, or User defined exception.

Examples
PL/SQL
IF past_due > 90 THEN RAISE past_due_exception; END IF;

Appendix A 3 RAWTOHEX

971

RAWTOHEX
RAWTOHEX
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a variable of type RAW.

Description
The RAWTOHEX function converts the RAW datatype to its equivalent hexadecimal value.

Examples
SQL
SELECT RAWTOHEX(animation) Animation FROM animation; Animation ---------------------------------------3D

972

Appendix A 3 Command Reference

RECORD
RECORD
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

type_name: field_type:

a type specifier used within subsequent declarations of records any datatype including RECORD and TABLE

Description
A PL/SQL record is a composite datatype modeled as a database record. PL/SQL records can have unlimited fields, and represent structures. A PL/SQL record allows nested records (a record can be nested within another record).

Examples
PL/SQL
DECLARE TYPE LoanRecTyp IS RECORD ( cname CHAR(50), loan_amount NUMBER (5)); loan_rec LoanRecTyp;

Appendix A 3 RECORD

973

BEGIN SELECT cust_name, loan_amt INTO loan_rec FROM LOAN; END; DECLARE TYPE TimeTyp IS RECORD ( minute hour TYPE MeetingTyp IS RECORD ( day time place meeting MeetingTyp; BEGIN [Link] := 26-JAN-51; [Link] := 45; [Link] := 12; END;

SMALLINT, SMALLINT); DATE, TimeTyp, CHAR(20));

974

Appendix A 3 Command Reference

REFTOHEX
REFTOHEX
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 9

Section Querying Objects

Variables in the syntax

expn:

any expression that returns a reference to an object

Description
REFTOHEX converts the argument passed to it to its hexadecimal equivalent.

Examples:
SQL
CREATE TABLE customer_tab (customer_no NUMBER, loan REF loan_tab); SELECT REFTOHEX (loan) FROM customer_tab;

Appendix A 3 REMARK

975

REMARK
REMARK
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

comment:

the comment you would like to include within the command file

Description
To include a comment within a SQL*Plus command file, use the REMARK command.
Tip

You can also use the /* */ or the command for documenting your code.

Examples
SQL
REM REM Loan account information

976

Appendix A 3 Command Reference

RENAME
RENAME
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

old: new:

a Table, view, sequence, or private synonym you would like to rename the new name for the Table, view, sequence, or private synonym

Description
To rename a Table, view, sequence, or private synonym that exists within your own Schema, use the RENAME statement.

Examples
SQL
RENAME accounting TO loanaccts RENAME marketing TO sales

Appendix A 3 REPFOOTER

977

REPFOOTER
REPFOOTER
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

printspec: printspec will include one of the following clauses that SQL*Plus will use to place and format the report footers text:

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 text for the report footer 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) 3 [Link] (current error code) 3 [Link] (current Username)

978

Appendix A 3 Command Reference

Description
To place the specified report footer within the specified format at the top of each report, use the REPFOOTER command. To turn the REPFOOTER definition off, use the OFF option. To turn the REPFOOTER definition on, use the ON option.

Examples
SQL
REPFOOTER PAGE RIGHT END OF REPORT REPFOOTER OFF REPFOOTER ON

Appendix A 3 REPHEADER

979

REPHEADER
REPHEADER
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

printspec: printspec will include one of the following clauses that SQL*Plus will use to place and format the report headers text:

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 text for the report header 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) 3 [Link] (current error code) 3 [Link] (current Username)

980

Appendix A 3 Command Reference

Description
To place the specified report header within the specified format at the top of each report, use the REPHEADER command. To list the current REPHEADER definition, use the REPHEADER command. To turn the REPHEADER definition off, use the OFF option. To turn the REPHEADER definition on, use the ON option.

Examples
SQL
REPHEADER REPHEADER REPHEADER REPHEADER PAGE CENTER TOTAL SALES BY REGION PAGE BOLD TOTAL UNITS SOLD OFF ON

Appendix A 3 REPLACE

981

REPLACE
REPLACE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

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 string that you want to replace. This is optional. z: a character or varchar2 variable that should be specified within single quotes. This refers to the string that you want to replace with. This is optional.

Description
The function REPLACE replaces a portion of one string with another. If y is not specified or is null, the function returns x. If z is not specified or is null, the function removes all the occurrences of y.

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

SQL
SELECT REPLACE (Oracle, Or, Mir) Example FROM DUAL; Example ------Miracle

982

Appendix A 3 Command Reference

REPLACE
REPLACE
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

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

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

Appendix A 3 RETURN

983

RETURN
RETURN
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

expression:

a valid expression. A valid expression will include variables, operators, literals, function calls, or constants.

Description
Use the RETURN statement to complete execution of the current sub program or block, and return control to the calling program.

Examples
PL/SQL
RETURN loan_bal; RETURN 5;

984

Appendix A 3 Command Reference

REVOKE
REVOKE
Difficulty
Advanced

Recommended Tool

Other Tools

Chapter 6, 11

Section Grant and Revoke Object Privileges

Variables in the syntax

system_privilege:

the system privilege you would like to revoke from the User,

or Role
object_privilege: the Object privilege you would like to revoke from the User, or Role. The Object privilege will be one of the following:

3 ALTER 3 DELETE 3 EXECUTE 3 INDEX 3 INSERT 3 REFERENCES 3 SELECT 3 UPDATE

role:

the Role you would like to revoke from the User

Appendix A 3 REVOKE

985

Description
To revoke a system privilege or Role from a User or Role, use the REVOKE statement. To revoke a system privilege or Role from all Users, use the PUBLIC option. To revoke a system privilege or Role, you must have the appropriate system privilege. For example, to revoke a Role, you must have the GRANT ANY ROLE system privilege. In addition, you can use the REVOKE statement to revoke an Object privilege on a given Object within the given Schema from the given User, or Role. To drop all the referential integrity Constraints associated with the Object, use the CASCADE CONSTRAINTS option. To revoke all the Object privileges, use the ALL option.

Examples
SQL
REVOKE REVOKE REVOKE REVOKE REVOKE ALTER TABLESPACE FROM john; GRANT ANY ROLE FROM todd; manager FROM imran; INSERT ON sales FROM javed; ALL ON marketing FROM terry;

986

Appendix A 3 Command Reference

ROLLBACK
ROLLBACK
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

WORK:

optional keyword. No net effect if you use it. Simply makes the sentence sound more complete. optional keyword. No net effect if you use it. Simply makes the sentence sound more complete. an undeclared identifier identifying the current mark within the current transaction, and up to which the changes made within the current transaction will be cancelled by the ROLLBACK statement

SAVEPOINT:

savepoint_work:

Description
Use the ROLLBACK statement to cancel the changes made within the current transaction. By issuing a ROLLBACK statement, you direct the application not to write the changes within the current transaction to the database.

Appendix A 3 ROLLBACK

987

Examples
SQL
ROLLBACK; ROLLBACK WORK; ROLLBACK TO loan_changes; ROLLBACK TO SAVEPOINT loan_changes; ROLLBACK WORK TO SAVEPOINT loan_changes;

988

Appendix A 3 Command Reference

ROUND
ROUND
Difficulty
Beginner

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

x: y:

numeric variable numeric variable (optional)

Description
The ROUND function will round off the value of x to the y places right of the decimal. If y is negative, this function rounds off the value left of the decimal. If y is omitted, the function will round off the value of x to 0 decimals.

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

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

Appendix A 3 ROUND

989

ROUND
ROUND
Difficulty
Intermediate

Recommended Tool

Other Tools

Chapter

Section

Variables in the syntax

a valid date variable

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

Table A-20 Variables for ROUND command

Format
CC, SCC

Result Century

SYYYY, YYYY, YEAR, SYEAR, YYY, YY Y Year (rounded as on July 1) 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)

990

Appendix A 3 Command Reference

Table A-20 (Continued)

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

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

Description
The ROUND function returns the date d in the format as specified by 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:= ROUND (SYSDATE,YEAR);

SQL
SELECT ROUND (SYSDATE,YEAR) CURRENT_FISCAL_YEAR FROM DUAL; CURRENT_F --------01-JAN-98

Advanced PL/SQL Training Course
No ratings yet
Advanced PL/SQL Training Course
101 pages
PLSQL Apex Notes
No ratings yet
PLSQL Apex Notes
45 pages
PL/SQL Procedures and Functions Guide
No ratings yet
PL/SQL Procedures and Functions Guide
22 pages
ملخص شابتر 5
No ratings yet
ملخص شابتر 5
6 pages
9i PL-SQL Volume 2 PDF
No ratings yet
9i PL-SQL Volume 2 PDF
31 pages
Oracle 9i Develop PLSQL Program Units Vol2 PDF
No ratings yet
Oracle 9i Develop PLSQL Program Units Vol2 PDF
62 pages
Oracle 9i-PLSQL - Program Units (Vol 2)
No ratings yet
Oracle 9i-PLSQL - Program Units (Vol 2)
60 pages
PL/SQL FORALL and Bulk Binds Explained
No ratings yet
PL/SQL FORALL and Bulk Binds Explained
9 pages
PL/SQL Composite Data Types Explained
No ratings yet
PL/SQL Composite Data Types Explained
11 pages
Oracle PL/SQL Course Overview
No ratings yet
Oracle PL/SQL Course Overview
5 pages
Oracle Sequence and View Management
No ratings yet
Oracle Sequence and View Management
35 pages
PL/SQL Data Types and Structures Guide
No ratings yet
PL/SQL Data Types and Structures Guide
29 pages
PL/SQL Basics and Common Errors
No ratings yet
PL/SQL Basics and Common Errors
62 pages
PL/SQL Language Features Explained
No ratings yet
PL/SQL Language Features Explained
19 pages
PLSQL Tutorial pdf.463
No ratings yet
PLSQL Tutorial pdf.463
47 pages
Understanding PL/SQL Subprograms
No ratings yet
Understanding PL/SQL Subprograms
18 pages
Oracle PL/SQL Stored Procedures Guide
No ratings yet
Oracle PL/SQL Stored Procedures Guide
10 pages
Stored Procedures and Function
No ratings yet
Stored Procedures and Function
44 pages
PL/SQL Package Architecture Guide
No ratings yet
PL/SQL Package Architecture Guide
13 pages
Oracle Academy Introduction To PL/SQL Instructor Resource Guide
No ratings yet
Oracle Academy Introduction To PL/SQL Instructor Resource Guide
23 pages
Dbms Lecture 4 Stored Proc
No ratings yet
Dbms Lecture 4 Stored Proc
16 pages
Understanding PL/SQL Packages
No ratings yet
Understanding PL/SQL Packages
13 pages
PL/SQL Procedures and Functions Overview
No ratings yet
PL/SQL Procedures and Functions Overview
37 pages
Collections
No ratings yet
Collections
26 pages
Unit-III Dbms Notes
No ratings yet
Unit-III Dbms Notes
14 pages
PL/SQL Stored Procedures Guide
No ratings yet
PL/SQL Stored Procedures Guide
32 pages
PL/SQL Programming Guide and Techniques
No ratings yet
PL/SQL Programming Guide and Techniques
9 pages
PL/SQL Basics: Variables and Syntax
No ratings yet
PL/SQL Basics: Variables and Syntax
9 pages
PL/SQL Programming Guide for Oracle
No ratings yet
PL/SQL Programming Guide for Oracle
34 pages
Views Oracle
No ratings yet
Views Oracle
14 pages
Oracle User Creation and Privileges Guide
No ratings yet
Oracle User Creation and Privileges Guide
33 pages
PL/SQL Programming Fundamentals Guide
No ratings yet
PL/SQL Programming Fundamentals Guide
9 pages
OCP - SQL&PL - SQL (Vol2)
No ratings yet
OCP - SQL&PL - SQL (Vol2)
348 pages
PL/SQL Basics and Control Structures
No ratings yet
PL/SQL Basics and Control Structures
25 pages
Database Management Systems Lab Manual
No ratings yet
Database Management Systems Lab Manual
53 pages
Understanding Stored Procedures in PL/SQL
No ratings yet
Understanding Stored Procedures in PL/SQL
2 pages
SQL Server: Creating Stored Procedures Guide
No ratings yet
SQL Server: Creating Stored Procedures Guide
29 pages
Introduction to PL/SQL Basics
No ratings yet
Introduction to PL/SQL Basics
50 pages
PL/SQL Basics and Architecture Guide
No ratings yet
PL/SQL Basics and Architecture Guide
153 pages
Stored PLSQL Program Units
No ratings yet
Stored PLSQL Program Units
33 pages
PL/SQL Cursors and Collections Overview
No ratings yet
PL/SQL Cursors and Collections Overview
9 pages
of Chapter 3.1 - Package, Procedures and Triggers
No ratings yet
of Chapter 3.1 - Package, Procedures and Triggers
36 pages
PLSQL PLSQL Complete
88% (8)
PLSQL PLSQL Complete
343 pages
Advanced SQL and PL/SQL Techniques
No ratings yet
Advanced SQL and PL/SQL Techniques
52 pages
Understanding SQL Stored Procedures
No ratings yet
Understanding SQL Stored Procedures
2 pages
Quick SQL for Rapid Database Creation
No ratings yet
Quick SQL for Rapid Database Creation
11 pages
Introduction to PL/SQL Programming
No ratings yet
Introduction to PL/SQL Programming
19 pages
PL/SQL Package Creation Guide
No ratings yet
PL/SQL Package Creation Guide
15 pages
PL/SQL Course Overview and Objectives
No ratings yet
PL/SQL Course Overview and Objectives
21 pages
PL/SQL Functions and Procedures Guide
No ratings yet
PL/SQL Functions and Procedures Guide
19 pages
Week 016 Creating Procedures
No ratings yet
Week 016 Creating Procedures
34 pages
Overview of PL/SQL Features and Benefits
No ratings yet
Overview of PL/SQL Features and Benefits
9 pages
PL/SQL Basics and Features Guide
No ratings yet
PL/SQL Basics and Features Guide
51 pages
PL/SQL Programming Basics Guide
No ratings yet
PL/SQL Programming Basics Guide
11 pages
Simple PL/SQL Programming Guide
No ratings yet
Simple PL/SQL Programming Guide
13 pages
PL/SQL Basics: Language Overview and Features
No ratings yet
PL/SQL Basics: Language Overview and Features
35 pages
Introduction to PL/SQL Programming
No ratings yet
Introduction to PL/SQL Programming
126 pages
PL/SQL Package Creation Guide
No ratings yet
PL/SQL Package Creation Guide
6 pages
Understanding PL/SQL Basics
No ratings yet
Understanding PL/SQL Basics
26 pages
Oracle Forms Menu Management Tutorial
No ratings yet
Oracle Forms Menu Management Tutorial
47 pages
Planning via Propositional Satisfiability
No ratings yet
Planning via Propositional Satisfiability
26 pages
Oracle Forms Development Overview
96% (28)
Oracle Forms Development Overview
88 pages
Adversarial Search Techniques Explained
No ratings yet
Adversarial Search Techniques Explained
15 pages
Mobilink and Telenor Internet Settings
No ratings yet
Mobilink and Telenor Internet Settings
2 pages
Knowledge-Based Agents in Logic
No ratings yet
Knowledge-Based Agents in Logic
67 pages
First Order Logic Lecture Overview
No ratings yet
First Order Logic Lecture Overview
20 pages
Local Search Algorithms Explained
No ratings yet
Local Search Algorithms Explained
13 pages
Rational Agents and Their Environments
No ratings yet
Rational Agents and Their Environments
31 pages
Understanding Adversarial Search in AI
No ratings yet
Understanding Adversarial Search in AI
25 pages
Uninformed Search Strategies Explained
No ratings yet
Uninformed Search Strategies Explained
34 pages
Problem-Solving Agents Overview
No ratings yet
Problem-Solving Agents Overview
25 pages
Informed Search Strategies Explained
No ratings yet
Informed Search Strategies Explained
21 pages
Overview of Psychology Methods
100% (2)
Overview of Psychology Methods
24 pages
Oracle Joins: 8i vs 9i Syntax Comparison
100% (1)
Oracle Joins: 8i vs 9i Syntax Comparison
3 pages
Overview of Oracle Single Row Functions
No ratings yet
Overview of Oracle Single Row Functions
7 pages
Overview of Pure and Applied Psychology
No ratings yet
Overview of Pure and Applied Psychology
38 pages
Naturalistic Observation in Psychology
100% (1)
Naturalistic Observation in Psychology
15 pages
Understanding Polymorphism in Java
No ratings yet
Understanding Polymorphism in Java
8 pages
Understanding Objects and Classes in Java
No ratings yet
Understanding Objects and Classes in Java
13 pages
W1209 Temperature Controller Guide
No ratings yet
W1209 Temperature Controller Guide
3 pages
C++ Programming MCQs and Answers
No ratings yet
C++ Programming MCQs and Answers
34 pages
nanDECK Tips and Tricks Guide
No ratings yet
nanDECK Tips and Tricks Guide
72 pages
Surpad 4 User Manual
No ratings yet
Surpad 4 User Manual
140 pages
OptiStar CG13 en
No ratings yet
OptiStar CG13 en
53 pages
B.Sc. AI & Data Science First Year Courses
No ratings yet
B.Sc. AI & Data Science First Year Courses
39 pages
Computational Physics with Julia Guide
No ratings yet
Computational Physics with Julia Guide
99 pages
ASP.NET Tutorial for Beginners
No ratings yet
ASP.NET Tutorial for Beginners
35 pages
UHF Add-On Instructions for RSLogix 5000
No ratings yet
UHF Add-On Instructions for RSLogix 5000
29 pages
Microsoft Excel Features and Functions
No ratings yet
Microsoft Excel Features and Functions
22 pages
Woodward CAN To Mdec
100% (6)
Woodward CAN To Mdec
46 pages
Python Programming Course 314004
No ratings yet
Python Programming Course 314004
9 pages
Python Programs for Data Structures and Functions
No ratings yet
Python Programs for Data Structures and Functions
52 pages
ArcSight FlexConnector Training Guide
No ratings yet
ArcSight FlexConnector Training Guide
75 pages
Understanding Object-Oriented Programming
No ratings yet
Understanding Object-Oriented Programming
548 pages
1978 ITTC Performance Prediction Method
No ratings yet
1978 ITTC Performance Prediction Method
31 pages
Java OOP Concepts Overview
No ratings yet
Java OOP Concepts Overview
99 pages
Execute PL/SQL from OAF Page
100% (1)
Execute PL/SQL from OAF Page
6 pages
Parameter Passing in Subprograms
No ratings yet
Parameter Passing in Subprograms
9 pages
MAG ETEGRA Software API Overview
No ratings yet
MAG ETEGRA Software API Overview
41 pages
Understanding Functions in C Programming
No ratings yet
Understanding Functions in C Programming
47 pages
XML Data Management in SQL Server
No ratings yet
XML Data Management in SQL Server
24 pages
Suppose That Alpha and Beta Are Int Variablesthe Statement Alpha Beta Is Equivalent To The Statements
No ratings yet
Suppose That Alpha and Beta Are Int Variablesthe Statement Alpha Beta Is Equivalent To The Statements
10 pages
Introduction to Object Oriented Programming
No ratings yet
Introduction to Object Oriented Programming
29 pages
B.Tech CSE AI & ML Course Structure
No ratings yet
B.Tech CSE AI & ML Course Structure
34 pages
Chemkin Tutorial for Reaction Mechanisms
No ratings yet
Chemkin Tutorial for Reaction Mechanisms
15 pages
Java Programming Labs Guide
No ratings yet
Java Programming Labs Guide
56 pages
User Manual For PileGroup
No ratings yet
User Manual For PileGroup
330 pages
RC2024-PPG-Reference 260124 103823
100% (1)
RC2024-PPG-Reference 260124 103823
95 pages

SQL CREATE Statements Overview

Uploaded by

SQL CREATE Statements Overview

Uploaded by

CREATE INDEX

See Also ALTER INDEX, DROP INDEX

Variables in the syntax

Appendix A 3 Command Reference

Appendix A 3 CREATE LIBRARY

See Also CREATE FUNCTION, CREATE PROCEDURE, DROP LIBRARY

Variables in the syntax

Appendix A 3 Command Reference

See Also CREATE PACKAGE BODY, ALTER PACKAGE, DROP PACKAGE

Variables in the syntax

Appendix A 3 CREATE PACKAGE

Appendix A 3 Command Reference

CREATE PACKAGE BODY

Variables in the syntax

Appendix A 3 CREATE PACKAGE BODY

Appendix A 3 Command Reference

See Also ALTER PROCEDURE, DROP PROCEDURE

Variables in the syntax

procedure: parameter: datatype: block:

Appendix A 3 CREATE PROCEDURE

Appendix A 3 Command Reference

See Also ALTER ROLE, DROP ROLE, GRANT

Variables in the syntax

Appendix A 3 CREATE ROLLBACK SEGMENT

CREATE ROLLBACK SEGMENT

Section Inserting Rows

See Also ALTER ROLLBACK SEGMENT, DROP ROLLBACK SEGMENT

Variables in the syntax

the rollback segment you would like to create

Appendix A 3 Command Reference

See Also CREATE TABLE, DROP SCHEMA

Variables in the syntax

the Schema you want to create

Appendix A 3 CREATE SEQUENCE

See Also ALTER SEQUENCE, DROP SEQUENCE

Variables in the syntax

any positive integer value

Appendix A 3 Command Reference

Table A-10 Options for the CREATE SEQUENCE statement

Appendix A 3 CREATE SNAPSHOT

See Also ALTER SNAPSHOT, DROP SNAPSHOT

Variables in the syntax

tablespace: start_date: next_date: query:

the SQL SELECT statement that will form the snapshot

Appendix A 3 Command Reference

Appendix A 3 CREATE SNAPSHOT LOG

CREATE SNAPSHOT LOG

Variables in the syntax

Appendix A 3 Command Reference

Appendix A 3 CREATE SYNONYM

See Also CREATE DATABASE LINK, CREATE TABLE, CREATE VIEW

Variables in the syntax

Appendix A 3 Command Reference

See Also ALTER TABLE, DROP TABLE

Variables in the syntax

Appendix A 3 CREATE TABLE

cluster: the cluster where you want to create the Table n:

Appendix A 3 Command Reference

See Also ALTER TABLESPACE

Variables in the syntax

Appendix A 3 CREATE TRIGGER

See Also ALTER TRIGGER, DROP TRIGGER

Variables in the syntax

trigger: column: table: old: new:

the PL/SQL block that should be executed

Appendix A 3 Command Reference

Appendix A 3 CREATE TYPE

See Also ALTER TYPE, CREATE TYPE BODY, DROP TYPE

Variables in the syntax

type of restrictions such as WNDS, WNPS, RNDS, RNPS

Appendix A 3 Command Reference

Appendix A 3 CREATE TYPE

Appendix A 3 Command Reference

CREATE TYPE BODY

See Also CREATE TYPE, DROP TYPE BODY

Variables in the syntax