Cover | Table of Contents

Table of Contents

Chapter 1: Oracle PL/SQL Language Pocket Reference

Content preview·Buy reprint rights for this chapter

The Oracle PL/SQL Language Pocket Reference is a quick reference guide to the PL/SQL programming language, which provides procedural extensions to the SQL relational database language and a range of Oracle development tools. Where a package, program, or function is supported only for a particular version of Oracle (e.g., Oracle9i), we indicate this in the text.

The purpose of this pocket reference is to help PL/SQL users find the syntax of specific language elements. It is not a self-contained user guide; basic knowledge of the PL/SQL programming language is required. For more information, see the following O'Reilly books:

Oracle PL/SQL Programming, Third Edition, by Steven Feuerstein with Bill Pribyl

Learning Oracle PL/SQL, by Bill Pribyl with Steven Feuerstein

Oracle Built-in Packages, by Steven Feuerstein, Charles Dye, and John Beresniewicz

Oracle PL/SQL Built-ins Pocket Reference, by Steven Feuerstein, John Beresniewicz, and Chip Dawes

Many thanks to all those who helped in the preparation of this book. In particular, thanks to first edition reviewers Eric J. Givler and Stephen Nelson and second edition reviewer Jonathan Gennick. In addition, we appreciate all the good work by the O'Reilly crew in editing and producing this book.

UPPERCASE indicates PL/SQL keywords.

lowercase indicates user-defined items such as parameters.

Italic indicates filenames and parameters within text.

Constant width is used for code examples and output.

[] enclose optional items in syntax descriptions.

{ } enclose a list of items in syntax descriptions; you must choose one item from the list.

| separates bracketed list items in syntax descriptions.

This section summarizes the fundamental components of the PL/SQL language: characters, identifiers, literals, delimiters, use of comments and pragmas, and construction of statements and blocks.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Introduction

Content preview·Buy reprint rights for this chapter

The Oracle PL/SQL Language Pocket Reference is a quick reference guide to the PL/SQL programming language, which provides procedural extensions to the SQL relational database language and a range of Oracle development tools. Where a package, program, or function is supported only for a particular version of Oracle (e.g., Oracle9i), we indicate this in the text.

The purpose of this pocket reference is to help PL/SQL users find the syntax of specific language elements. It is not a self-contained user guide; basic knowledge of the PL/SQL programming language is required. For more information, see the following O'Reilly books:

Oracle PL/SQL Programming, Third Edition, by Steven Feuerstein with Bill Pribyl

Learning Oracle PL/SQL, by Bill Pribyl with Steven Feuerstein

Oracle Built-in Packages, by Steven Feuerstein, Charles Dye, and John Beresniewicz

Oracle PL/SQL Built-ins Pocket Reference, by Steven Feuerstein, John Beresniewicz, and Chip Dawes

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Acknowledgments

Content preview·Buy reprint rights for this chapter

Many thanks to all those who helped in the preparation of this book. In particular, thanks to first edition reviewers Eric J. Givler and Stephen Nelson and second edition reviewer Jonathan Gennick. In addition, we appreciate all the good work by the O'Reilly crew in editing and producing this book.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Conventions

Content preview·Buy reprint rights for this chapter

UPPERCASE indicates PL/SQL keywords.

lowercase indicates user-defined items such as parameters.

Italic indicates filenames and parameters within text.

Constant width is used for code examples and output.

[] enclose optional items in syntax descriptions.

{ } enclose a list of items in syntax descriptions; you must choose one item from the list.

| separates bracketed list items in syntax descriptions.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

PL/SQL Language Fundamentals

Content preview·Buy reprint rights for this chapter

This section summarizes the fundamental components of the PL/SQL language: characters, identifiers, literals, delimiters, use of comments and pragmas, and construction of statements and blocks.

The PL/SQL language is constructed from letters, digits, symbols, and whitespace, as defined in the following table:

Type	Characters
Letters	A-Z, a-z
Digits	0-9
Symbols	~!@#$%^&*( )_-+=|[ ]{ }:;"'< >,.?/ ^
Whitespace	space, tab, newline, carriage return

Characters are grouped together into four lexical units: identifiers, literals, delimiters, and comments.

Identifiers are names for PL/SQL objects such as constants, variables, exceptions, procedures, cursors, and reserved words. Identifiers have the following characteristics:

• Can be up to 30 characters in length
• Cannot include whitespace (space, tab, carriage return)
• Must start with a letter
• Can include a dollar sign ($), an underscore ( _ ), and a pound sign (#)
• Are not case-sensitive

In addition, you must not use PL/SQL's reserved words as identifiers. For a list of those words, see the table in the final section in this book, Section 1.20.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Variables and Program Data

Content preview·Buy reprint rights for this chapter

PL/SQL programs are normally used to manipulate database information. You commonly do this by declaring variables and data structures in your programs, and then working with that PL/SQL-specific data.

A variable is a named instantiation of a data structure declared in a PL/SQL block (either locally or in a package). Unless you declare a variable as a CONSTANT, its value can be changed at any time in your program.

The following table summarizes the different types of program data:

Type	Description
Scalar	Variables made up of a single value, such as a number, date, or Boolean
Composite	Variables made up of multiple values, such as a record or a collection
Reference	Pointers to values
LOB	Variables containing large object (LOB) locators

Scalar datatypes divide into four families: number, character, datetime, and Boolean.

Section 1.5.1.1: Numeric datatypes

Numeric datatypes are further divided into decimal, binary integer, and PLS_INTEGER storage types.

Decimal numeric datatypes store fixed and floating-point numbers of just about any size. They include NUMBER, DEC, DECIMAL, NUMERIC, FLOAT, REAL, and DOUBLE PRECISION. The maximum precision of a variable with type NUMBER is 38 digits, which yields a range of values from 1.0E-129 through 9.999E125. (This range of numbers would include the mass of an electron over the mass of the universe or the size of the universe in angstroms.)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Conditional and Sequential Control

Content preview·Buy reprint rights for this chapter

PL/SQL includes conditional (IF, CASE) structures as well as sequential control (GOTO, NULL) constructs.

There are several varieties of IF-THEN-ELSE and CASE structures.

Section 1.6.1.1: IF-THEN combination

IF condition THEN
   executable statement(s)
END IF;

For example:

IF caller_type = 'VIP' THEN
   generate_response('GOLD');
END IF;

Section 1.6.1.2: IF-THEN-ELSE combination

IF condition THEN
   TRUE sequence_of_executable_statement(s)
ELSE
   FALSE/NULL sequence_of_executable_statement(s) 
END IF;

For example:

IF caller_type = 'VIP' THEN
   generate_response('GOLD');
ELSE
   generate_response('BRONZE');
END IF;

Section 1.6.1.3: IF-THEN-ELSIF combination

IF condition-1 THEN
   statements-1
ELSIF condition-N THEN
 statements-N
[ELSE
   ELSE statements]
END IF;

For example:

IF caller_type = 'VIP' THEN
   generate_response('GOLD');
ELSIF priority_client THEN
   generate_response('SILVER');
ELSE
   generate_response('BRONZE');
END IF;

Section 1.6.1.4: CASE statement (Oracle9i)

There are two types of CASE statements: simple and searched.

A simple CASE statement is similar to an IF-THEN-ELSIF structure. The statement has a switch expression immediately after the keyword CASE. The expression is evaluated and compared to the value in each WHEN clause. The first WHEN clause with a matching value is executed and then control passes to the next statement following the END CASE. For example:

CASE region_id
   WHEN 'NE' THEN  
      mgr_name := 'MINER'; 
   WHEN 'SE' THEN
      mgr_name := 'KOOI';
   ELSE mgr_name := 'LANE';
END CASE;

If a switch expression evaluates to NULL, the ELSE case is the only one that can possibly match; WHEN NULL will never match because Oracle performs an equality comparison on the expressions.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Loops

Content preview·Buy reprint rights for this chapter

The LOOP construct allows you to execute a sequence of statements repeatedly. There are three kind of loops: simple (infinite), FOR, and WHILE.

You can use the EXIT statement to break out of LOOP and pass control to the statement following the END LOOP.

LOOP
   executable_statement(s)
END LOOP;

The simple loop should contain an EXIT or EXIT WHEN unless you want it to execute infinitely. Use the simple loop when you want the body of the loop to execute at least once. For example:

LOOP
   FETCH company_cur INTO company_rec;
   EXIT WHEN company_cur%ROWCOUNT > 5 OR
      company_cur%NOTFOUND;
   process_company(company_cur);
END LOOP;

FOR loop_index IN [REVERSE] lowest_number..highest_number
LOOP
   executable_statement(s)
END LOOP;

The PL/SQL runtime engine automatically declares the loop index a PLS_INTEGER variable; never declare a variable with that name yourself. The lowest_number and highest_number ranges can be variables, but are evaluated only once—on initial entry into the loop. The REVERSE keyword causes PL/SQL to start with the highest_number and decrement down to the lowest_number. For example, this code:

BEGIN
   FOR counter IN 1 .. 4
   LOOP
      DBMS_OUTPUT.PUT(counter);
   END LOOP;
   DBMS_OUTPUT.NEW_LINE;

   FOR counter IN REVERSE 1 .. 4
   LOOP
      DBMS_OUTPUT.PUT(counter);
   END LOOP;
   DBMS_OUTPUT.NEW_LINE;END;

yields the following output:

1234
4321

FOR record_index IN [cursor_name | (SELECT statement)]
LOOP
   executable_statement(s)
END LOOP;

The PL/SQL runtime engine automatically declares the loop index a record of cursor_name%ROWTYPE; never declare a variable with that name yourself.

The cursor FOR loop automatically opens the cursor, fetches all rows identified by the cursor, and then closes the cursor. You can embed the SELECT statement directly in the cursor FOR loop. For example:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Database Interaction

Content preview·Buy reprint rights for this chapter

PL/SQL is tightly integrated with the underlying SQL layer of the Oracle database. You can execute SQL statements (UPDATE, INSERT, DELETE, MERGE, and SELECT) directly in PL/SQL programs. You can also execute Data Definition Language (DDL) statements through the use of dynamic SQL. In addition, you can manage transactions with COMMIT, ROLLBACK, and other Data Control Language (DCL) statements.

The Oracle RDBMS provides a transaction model based on a unit of work. The PL/SQL language supports most, but not all, of the database model for transactions (you cannot, for example, specify ROLLBACK FORCE). A transaction begins with the first change to data and ends with either a COMMIT or a ROLLBACK. Transactions are independent of PL/SQL blocks. Transactions can span multiple PL/SQL blocks, or there can be multiple transactions in a single PL/SQL block. The PL/SQL-supported transaction statements include COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION, and LOCK TABLE, described in the following sections.

Section 1.8.1.1: COMMIT

COMMIT [WORK] [COMMENT text];

COMMIT makes the database changes permanent and visible to other database sessions. The WORK keyword is optional and only aids readability—it is rarely used. The COMMENT text is optional and can be up to 50 characters in length. It is only germane to in-doubt distributed (two-phase commit) transactions. The database statement COMMIT FORCE, also for distributed transactions, is not supported in PL/SQL.

Section 1.8.1.2: ROLLBACK

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];

ROLLBACK undoes the changes made in the current transaction either to the beginning of the transaction or to a savepoint. A savepoint is a named processing point in a transaction, created with the SAVEPOINT statement. Rolling back to a savepoint is a partial rollback of a transaction, wiping out all changes (and savepoints) that occurred later than the named savepoint.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Cursors in PL/SQL

Content preview·Buy reprint rights for this chapter

Every SQL statement executed by the RDBMS has a private SQL area that contains information about the SQL statement and the set of data returned. In PL/SQL, a cursor is a name assigned to a specific private SQL area for a specific SQL statement. There can be either static cursors, whose SQL statement is determined at compile time, or dynamic cursors, whose SQL statement is determined at runtime. Static cursors are used only for DML statements (SELECT, INSERT, UPDATE, DELETE, MERGE, or SELECT FOR UPDATE). These static cursors can be explicitly declared and named or may appear in-line as an implicit cursor. Dynamic cursors are used for any type of valid SQL statement including DDL (CREATE, TRUNCATE, ALTER) and DCL (GRANT, REVOKE). Dynamic cursors are implemented with the EXECUTE IMMEDIATE statement.

Explicit cursors are SELECT statements that are DECLAREd explicitly in the declaration section of the current block or in a package specification. Use OPEN, FETCH, and CLOSE in the execution or exception sections of your programs.

Section 1.9.1.1: Declaring explicit cursors

To use an explicit cursor, you must first declare it in the declaration section of a block or package. There are three types of explicit cursor declarations:

• A cursor without parameters; for example:

  CURSOR company_cur 
     IS
     SELECT company_id FROM company;

• A cursor that accepts arguments through a parameter list; for example:

  CURSOR company_cur (id_in IN NUMBER) IS
  SELECT name FROM company
  WHERE  company_id = id_in;

• A cursor header that contains a RETURN clause in place of the SELECT statement; for example:

  CURSOR company_cur (id_in IN NUMBER) 
  RETURN company%ROWTYPE;

This last example shows that the cursor can be declared separately from its implementation; for example, the header in a package specification and the implementation in the package body. See Section 1.14 for more information.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Exception Handling

Content preview·Buy reprint rights for this chapter

PL/SQL allows developers to raise and handle errors (exceptions) in a very flexible and powerful way. Each PL/SQL block can have its own exception section in which exceptions can be trapped and handled (resolved or passed on to the enclosing block).

When an exception occurs (is raised) in a PL/SQL block, its execution section immediately terminates. Control is passed to the exception section.

Every exception in PL/SQL has an error number and error message; some exceptions also have names.

Some exceptions (see the following table) have been pre-defined by Oracle in the STANDARD package or other built-in packages, such as UTL_FILE. You can also declare your own exceptions as follows:

DECLARE
   exception_name EXCEPTION;

Error	Named exception
ORA-00001	DUP_VAL_ON_INDEX
ORA-00051	TIMEOUT_ON_RESOURCE
ORA-00061	TRANSACTION_BACKED_OUT
ORA-01001	INVALID_CURSOR
ORA-01012	NOT_LOGGED_ON
ORA-01017	LOGIN_DENIED
ORA-01403	NO_DATA_FOUND
ORA-01410	SYS_INVALID_ROWID

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Records in PL/SQL

Content preview·Buy reprint rights for this chapter

A PL/SQL record is a data structure composed of multiple pieces of information called fields. To use a record, you must first define it and declare a variable of this type.

There are three types of records: table-based, cursor-based, and programmer-defined.

You define and declare records either in the declaration section of a PL/SQL block or globally, via a package specification.

You do not have to explicitly define table-based or cursor-based records, as they are implicitly defined with the same structure as a table or a cursor. Variables of these types are declared via the %ROWTYPE attribute. The record's fields correspond to the table's columns or the columns in the SELECT list. For example:

DECLARE
   -- Declare table-based record for company table.
   comp_rec  company%ROWTYPE

   CURSOR comp_summary_cur IS
      SELECT C.company_id,SUM(S.gross_sales) gross
        FROM company C ,sales S
       WHERE C.company_id = S.company_id;

   -- Declare a cursor-based record.
   comp_summary_rec  comp_summary_cur%ROWTYPE;

Programmer-defined records must be explicitly defined with the TYPE statement in the PL/SQL declaration section or in a package specification. Variables of this type can then be declared as shown here:

DECLARE
   TYPE name_rectype IS RECORD(
      prefix       VARCHAR2(15)
      ,first_name  VARCHAR2(30)
      ,middle_name VARCHAR2(30)
      ,sur_name    VARCHAR2(30)
      ,suffix      VARCHAR2(10) );

   TYPE employee_rectype IS RECORD (
      emp_id       NUMBER(10) NOT NULL
      ,mgr_id      NUMBER(10)
      ,dept_no     dept.deptno%TYPE
      ,title       VARCHAR2(20)
      ,name        empname_rectype
      ,hire_date   DATE := SYSDATE
      ,fresh_out   BOOLEAN );

   -- Declare a variable of this type. 
   new_emp_rec employee_rectype;
BEGIN

Individual fields are referenced via dot notation:

               record_name.field_name 
            

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Named Program Units

Content preview·Buy reprint rights for this chapter

PL/SQL allows you to create a variety of named program units, or containers for code. These include:

Procedure

A program that executes one or more statements

Function

A program that returns a value

Package

A container for procedures, functions, and data structures

Trigger

A program that executes in response to database changes

Object type

Oracle's version of an object-oriented class; object types can contain member procedures and functions

Procedures are program units that execute one or more statements and can receive or return zero or more values through their parameter lists. The syntax of a procedure is:

CREATE [OR REPLACE] PROCEDURE name 
   [ (parameter [,parameter]) ]
   [AUTHID { CURRENT_USER | DEFINER } ]
   [DETERMINISTIC] 
{ IS | AS }
   declaration_section
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [name];

A procedure is called as a standalone executable PL/SQL statement:

apply_discount(new_company_id, 0.15);

Functions are program units that execute zero or more statements and return a value through the RETURN clause. Functions can also receive or return zero or more values through their parameter lists. The syntax of a function is:

CREATE [OR REPLACE] FUNCTION name 
   [ (parameter [,parameter]) ] 
   RETURN return_datatype
   [AUTHID { CURRENT_USER | DEFINER } ] 
   [DETERMINISTIC] 
   [PARALLEL_ENABLE] 
   [PIPELINED]
   [AGGREGATE USING]
{ IS | AS }
   [declaration_section]
BEGIN
   

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Triggers

Content preview·Buy reprint rights for this chapter

Triggers are programs that execute in response to changes in table data or certain database events. There is a predefined set of events that can be "hooked" with a trigger, enabling you to integrate your own processing with that of the database. A triggering event fires or executes the trigger.

There are three types of triggering events:

• DML events fire when an INSERT, UPDATE, or DELETE statement executes.
• DDL events fire when a CREATE, ALTER, or DROP statement executes.
• Database events fire when one of the predefined database-level events occurs.

Complete lists of these events are included in later sections.

The syntax for creating a trigger on a DML event is:

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } trigger_event 
   ON {table_or_view_reference |
     NESTED TABLE nested_table_column OF view}
     [REFERENCING [OLD AS old] [NEW AS new]
       [PARENT AS parent]]
[FOR EACH ROW ][WHEN trigger_condition]
trigger_body;

The syntax for creating a trigger on a DDL or database event is:

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER } trigger_event 
   ON [ DATABASE | schema ]
 [WHEN trigger_condition]
trigger_body;

Trigger events are listed in the following table:

Trigger event	Description
INSERT	Fires whenever a row is added to the table_or_view_reference.
UPDATE	Fires whenever an UPDATE changes the table_or_view_reference

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Packages

Content preview·Buy reprint rights for this chapter

A package is a collection of PL/SQL objects that are grouped together. There are a number of benefits to using packages, including information hiding, object-oriented design, top-down design, object persistence across transactions, and improved performance.

Elements that can be placed in a package include procedures, functions, constants, variables, cursors, exception names, and TYPE statements (for associative arrays [formerly known as index-by tables], records, REF CURSORs, etc.).

A package can have two parts: the specification and the body. The package specification is required and lists all the objects that are publicly available (i.e., may be referenced from outside the package) for use in applications. It also provides all the information a developer needs in order to use objects in the package; essentially, it is the package's API.

The package body contains all the code needed to implement procedures, functions, and cursors listed in the specification, as well as any private objects (accessible only to other elements defined in that package), and an optional initialization section.

If a package specification does not contain any procedures or functions and no private code is needed, then that package does not need to have a package body.

The syntax for the package specification is:

CREATE [OR REPLACE] PACKAGE package_name 
[ AUTHID { CURRENT_USER | DEFINER } ] 
{ IS | AS }

   [definitions of public TYPEs
   ,declarations of public variables, types, and 
      objects
   ,declarations of exceptions
   ,pragmas
   ,declarations of cursors, procedures, and   
      functions
   ,headers of procedures and functions]

END [package_name];

The syntax for the package body is:

CREATE [OR REPLACE] PACKAGE BODY package_name 
   { IS | AS }

   [definitions of private TYPEs
   ,declarations of private variables, types, and 
      objects
   ,full definitions of cursors
   ,full definitions of procedures and functions

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Calling PL/SQL Functions in SQL

Content preview·Buy reprint rights for this chapter

Stored functions can be called from SQL statements in a manner similar to built-in functions like DECODE, NVL, or RTRIM. This is a powerful technique for incorporating business rules into SQL in a simple and elegant way. Unfortunately, there are a number of caveats and restrictions.

The most notable caveat is that stored functions executed from SQL are not by default guaranteed to follow the statement-level read consistency model of the database. Unless the SQL statement and any stored functions in that statement are in the same read-consistent transaction (even if they are read-only), each execution of the stored function may look at a different time-consistent set of data. To avoid this potential problem, you need to ensure read consistency programmatically by issuing the SET TRANSACTION READ ONLY or SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before executing your SQL statement containing the stored function. A COMMIT or ROLLBACK then needs to follow the SQL statement to end this read-consistent transaction.

The syntax for calling a stored function from SQL is the same as that used to reference it from PL/SQL:

[schema_name.][pkg_name.]func_name[@db_link]
   [parm_list]

schema_name is optional and refers to the user/owner of the function or package. pkg_name is optional and refers to the package containing the called function. func_name is required and is the function name. db_link is optional and refers to the database link name to the remote database containing the function. parm_list is optional, as are the parameters passed to the function.

The following are example calls to the GetTimestamp function in the time_pkg example seen earlier in Section 1.14.1:

-- Capture system events.
INSERT INTO v_sys_event (timestamp ,event ,qty_waits)
   SELECT time_pkg.GetTimestamp ,event ,total_waits
   FROM v$system_event

-- Capture system statistics.
INSERT INTO v_sys_stat (timestamp,stat#,value)
   SELECT time_pkg.GetTimestamp ,statistic# ,value
   FROM v$sysstat;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Oracle's Object-Oriented Features

Content preview·Buy reprint rights for this chapter

In Oracle, an object type combines attributes (data structures) and methods (functions and procedures) into a single programming construct. The object type construct allows programmers to define their own reusable datatypes for use in PL/SQL programs and table and column definitions. An object type must be created in a database before it can be used in a PL/SQL program.

An instance of an object type is an object in the same way that a variable is an instance of a scalar type. Objects are either persistent (stored in the database) or transient (stored only in PL/SQL variables). Objects can be stored in a database as a row in a table (a row object) or as a column in a table. A table of row objects can be created with syntax such as this:

CREATE TABLE table_name OF object_type;

When stored in such a table, the object (row) has an OID (Object IDentifier) that is unique throughout the database.

An object type has two parts: the specification and the body. The specification is required and contains the attributes and method specifications. The syntax for creating the object type specification is:

CREATE [OR REPLACE] TYPE obj_type_name 
[AUTHID { CURRENT_USER | DEFINER } ] 
{ { IS | AS } OBJECT | UNDER parent_type_name }
(
   attribute_name datatype,...,
   [ [ [NOT] OVERRIDING ] [ {NOT] FINAL ] [ {NOT} 
   INSTANTIABLE ] method_spec,...,]
   [PRAGMA RESTRICT_REFERENCES(program_name, purities)]
)
[ [NOT] FINAL ]
[ [NOT] INSTANTIABLE ];

Where method_spec is one of the following:

MEMBER { PROCEDURE | FUNCTION } program_spec
            

or:

STATIC { PROCEDURE | FUNCTION } program_spec
            

or:

{ ORDER | MAP } MEMBER FUNCTION comparison_function_spec
            

or:

CONSTRUCTOR FUNCTION constructor_function_spec
            

Attribute specifications must appear before method specifications. Object attributes, like table columns, are defined with a name and a datatype. The name can be any legal identifier, and the datatype can be almost any datatype known to SQL other than LONG, LONG RAW, ROWID, and UROWID. Attributes can be declared on other programmer-defined object types or collection types, but not on the Oracle9

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Collections

Content preview·Buy reprint rights for this chapter

There are three types of collections: associative arrays (formerly known as index-by tables or PL/SQL tables), nested tables, and VARRAYs.

Associative arrays

Single-dimension, unbounded collections of homogeneous elements available only in PL/SQL, not in the database. Associative arrays are initially sparse; they have nonconsecutive subscripts.

Nested tables

Single-dimension, unbounded collections of homogeneous elements available in both PL/SQL and the database as columns or tables. Nested tables are initially dense (they have consecutive subscripts), but they can become sparse through deletions.

VARRAYs

Variable-size arrays. Single-dimension, bounded collections of homogeneous elements available in both PL/SQL and the database. VARRAYs are never sparse. Unlike nested tables, their element order is preserved when you store and retrieve them from the database.

The following table compares these similar collection types:

		Collection type
Characteristic	Associative array	Nested table	VARRAY
Dimensionality	Single	Single	Single
Usable in SQL?	No

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

External Procedures

Content preview·Buy reprint rights for this chapter

External procedures provide a mechanism for calling out to a non-database program, such as a DLL under NT or a shared library under Unix. Every session calling an external procedure will have its own extproc process started by the listener. This extproc process is started with the first call to the external procedure and terminates when the session exits. The shared library needs to have a corresponding library created for it in the database.

The following are the steps you need to follow in order to create an external procedure.

Section 1.18.1.1: Set up the listener

External procedures require a listener. If you are running an Oracle Net database listener, it can be used as the extproc listener as well, although you may increase security by separating it from the external procedure listener and launching it from a privilege-limited account. Here is one way to structure the listener.ora file:

LISTENER =
   (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521))

EXTPROC_LISTENER =
   (ADDRESS = (PROTOCOL = IPC)(KEY = extprocKey))

SID_LIST_LISTENER =
   (SID_DESC =
      (GLOBAL_DBNAME = global_name)
      (ORACLE_HOME = oracle_home_directory)
      (SID_NAME = SID)
   )

SID_LIST_EXTPROC_LISTENER =
   (SID_DESC =
      (SID_NAME = extprocSID)
      (ORACLE_HOME = oracle_home_directory)
      (ENVS = "EXTPROC_DLLS=
        qualifier:shared_object_file_list")
      (PROGRAM = extproc)
   )

extprocKey

Short identifier used by Oracle Net to distinguish this listener from other potential IPC listeners. Its actual name is arbitrary, because your programs will never see it. Oracle uses EXTPROC0 as the default name for the first Oracle Net installation on a given machine. This identifier must be the same in the address list of the listener.ora and tnsnames.ora files.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Java Language Integration

Content preview·Buy reprint rights for this chapter

Java™ programmers can write server-side classes that invoke SQL and PL/SQL using standard JDBC™ or SQLJ calls. PL/SQL programmers can call server-side Java methods by writing a PL/SQL cover or call spec for Java using Oracle DDL.

Server-side Java in Oracle may be faster than PL/SQL for compute-intensive programs, but not as nimble for database access. PL/SQL is much more efficient for database-intensive routines because, unlike Java, it doesn't have to pay the overhead for converting SQL datatypes for use inside the stored program. Oracle programmers will want to continue to use PL/SQL for programs that perform a lot of database I/O, and use Java for the best raw computation performance.

The first step in creating a Java stored procedure (JSP) is writing or otherwise obtaining functional Java code. Having source code is not necessary, though, so you can use class libraries from third parties. The classes must, however, meet the following requirements:

• Methods published to SQL and PL/SQL must be declared static. PL/SQL has no mechanism for instantiating non-static Java classes.
• The classes must not issue any GUI calls (for example, to AWT) at runtime.

If you write your own JSP, and it needs to connect to the database for access to tables or stored procedures, use standard JDBC and/or SQLJ calls in your code. Many JDBC and SQLJ reference materials are available to provide assistance in calling SQL or PL/SQL from Java, but be sure to review the Oracle-specific documentation that ships with your release.

Once you have the Java class in hand, either in source or .class file format, the next step is loading it into the database. Oracle's loadjava command-line utility is a convenient way to accomplish the load. Refer to Oracle's documentation for further assistance with loadjava.

The third step is to create a call spec for the Java method, specifying the AS LANGUAGE JAVA clause of the CREATE command. You may create a function or procedure cover as appropriate.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Reserved Words

Content preview·Buy reprint rights for this chapter

As we mentioned earlier in this book, the PL/SQL language recognizes certain identifiers (language keywords and identifiers from the STANDARD package) as having special meaning. You must not redefine these reserved words as identifiers in your programs.

We compiled the following table of reserved words by taking the list Oracle publishes in the V$RESERVED_WORDS data dictionary view and trying to declare them (as variables and/or procedures). If the declarations failed, we added the words to the list. Avoid using these words in your programs.

ACCESS	ADD	ALL	ALTER
AND	ANY	AS	ASC
AT	AUDIT	BEGIN	BETWEEN
BY	CASE	CHAR	CHECK
CLOSE	CLUSTER	COLUMN	COLUMNS
COMMENT	COMMIT	COMPRESS	CONNECT
CREATE	CURRENT	CURSOR

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Return to Oracle PL/SQL Language Pocket Reference