By Bill Pribyl, Steven Feuerstein, Chip Dawes

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., Oracle8i), 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 books:

Oracle PL/SQL Programming, Second Edition , by Steven Feuerstein with Bill Pribyl (O'Reilly & Associates, 1997).

Oracle Built-in Packages , by Steven Feuerstein, Charles Dye, and John Beresniewicz (O'Reilly & Associates, 1998).

Oracle PL/SQL Built-ins Pocket Reference , by Steven Feuerstein, John Beresniewicz, and Chip Dawes (O'Reilly & Associates, 1998).

We would like to thank our reviewers: Eric J. Givler, Department of Environmental Protection, Harrisburg, Pennsylvania; and Stephen Nelson, HK Systems, New Berlin, Wisconsin.

UPPERCASE indicates PL/SQL keywords.

lowercase indicates user-defined items such as parameters.

Italic indicates file names and parameters within text.

Constant width is used for code examples.

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

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

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., Oracle8i), 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 books:

Oracle PL/SQL Programming, Second Edition , by Steven Feuerstein with Bill Pribyl (O'Reilly & Associates, 1997).

Oracle Built-in Packages , by Steven Feuerstein, Charles Dye, and John Beresniewicz (O'Reilly & Associates, 1998).

Oracle PL/SQL Built-ins Pocket Reference , by Steven Feuerstein, John Beresniewicz, and Chip Dawes (O'Reilly & Associates, 1998).

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

We would like to thank our reviewers: Eric J. Givler, Department of Environmental Protection, Harrisburg, Pennsylvania; and Stephen Nelson, HK Systems, New Berlin, Wisconsin.

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 file names and parameters within text.

Constant width is used for code examples.

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

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, carriage return

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

Section 1.4.1.1: Identifiers

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

• 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

If you enclose an identifier within double quotes, then all but the first of these rules are ignored. For example, the following declaration is valid:

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 describes several 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 collection.
Reference	Pointers to values.
LOB	Variables containing Large OBject (LOB) locators.

Scalar datatypes divide into four families: number, character, date-time, 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) structures as well as sequential control (GOTO, NULL) constructs.

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;

The GOTO statement performs unconditional branching to a named label. It should be used rarely. At least one executable statement must follow the label (the NULL statement can be this necessary executable statement). The format of a GOTO statement is:

GOTO label_name;

The format of the label is:

<<label_name>>

There are a number of scope restrictions on where a GOTO can branch control. A GOTO:

• Can branch out of an IF statement, LOOP, or sub-block
• Cannot branch into an IF statement, LOOP, or sub-block
• Cannot branch from one section of an IF statement to another (from the IF/THEN section to the ELSE section is illegal)

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 repeatedly execute a sequence of statements. There are three kind of loops: simple, WHILE, and FOR.

Use the EXIT statement to break out of LOOP and pass control to the statement following the END LOOP.

The syntax for a simple loop is:

LOOP
   executable_statement(s)
END LOOP;

The simple loop should contain an EXIT or EXIT WHEN statement so as not 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;

The syntax for a numeric FOR loop is:

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:

BEGIN
   DBMS_OUTPUT.PUT_LINE('Beginning Forward');
   FOR counter IN 1 .. 4
   LOOP
      DBMS_OUTPUT.PUT_LINE('counter='||counter);
   END LOOP;

   DBMS_OUTPUT.PUT_LINE('Beginning REVERSE');
   FOR counter IN REVERSE 1 .. 4
   LOOP
      DBMS_OUTPUT.PUT_LINE('counter='||counter);
   END LOOP;
END;

The syntax for a cursor FOR loop is:

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 and Cursors

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, and SELECT) directly in PL/SQL programs. You can also execute Data Definition Language (DDL) statements through the use of dynamic SQL (DBMS_SQL in Oracle7 and Oracle8, native dynamic SQL in Oracle8i). 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, ROLLBACK FORCE). Transactions begin with the first change to data and end with either a COMMIT or 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 are: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION, and LOCK TABLE. Each is detailed here:

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 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 covered in greater detail in this section. Dynamic cursors in PL/SQL are implemented via the built-in package DBMS_SQL. See the book Oracle Built-in Packages and the corresponding Oracle PL/SQL Built-ins Pocket Reference , both from O'Reilly & Associates, for full coverage on DBMS_SQL and the other built-in packages.

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, such as:

      CURSOR company_cur 
         IS
         SELECT company_id FROM company;

• A cursor that accepts arguments through a parameter list:

      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:

      CURSOR company_cur (id_in IN NUMBER) 
      RETURN company%ROWTYPE IS
      SELECT * FROM company;

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. 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-01001	INVALID_CURSOR
ORA-01012	NOT_LOGGED_ON
ORA-01017	LOGIN_DENIED
ORA-01403	NO_DATA_FOUND
ORA-01410	SYS_INVALID_ROWID
ORA-01422	TOO_MANY_ROWS
ORA-01476

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 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 in the PL/SQL block or a package specification with the TYPE statement. Variables of this type can then be declared:

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 

For example:

employee.first_name

Individual fields within a record can be read from or written to. They can appear on either the left or right side of the assignment operator:

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

The PL/SQL programming language allows you to create a variety of named program units (containers for code). They 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

Triggers

Programs that execute in response to database changes

Object type

Oracle8's version of a SQL3 named row type; 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 ] -- Oracle8i
   [DETERMINISTIC]                   -- Oracle8i
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) --15% discount

Functions are program units that execute one 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 ] -- Oracle8i
   [DETERMINISTIC]                   -- Oracle8i
   [PARALLEL_ENABLE]                 -- Oracle8i
IS | AS
   [declaration_section]
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [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!

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.

The syntax for creating a trigger is:

CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE | AFTER | INSTEAD OF trigger_event 
   ON 
   [ NESTED TABLE nested_table_column OF view ]    
      | table_or_view_reference | DATABASE
   [referencing_clause]
[FOR EACH ROW [WHEN trigger_condition]]
trigger_body;

INSTEAD OF triggers are valid on only Oracle8 views. Oracle8i must create a trigger on a nested table column.

Trigger events are defined in the following table.

Trigger Event	Description
INSERT	Fires whenever a row is added to the table_reference.
UPDATE	Fires whenever an UPDATE changes the table_reference. UPDATE triggers can additionally specify an OF clause to restrict firing to updates OF certain columns. See the following examples.
DELETE	Fires whenever a row is deleted from the table_reference. Does not fire on TRUNCATE of the table.
CREATE (Oracle8i)	Fires whenever a CREATE statement adds a new object to the database. In this context, objects are things like tables or packages (found in ALL_OBJECTS). Can apply to a single schema or the entire database.

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 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 (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 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 ]   -- Oracle8i
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]

[BEGIN
   executable_statements

[EXCEPTION
   exception_handlers ] ]

END [package_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!

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 guaranteed to follow the 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 will 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 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 referencing 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 mandatory 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 :

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

Oracle8 Objects

Content preview·Buy reprint rights for this chapter

In Oracle8, an object type combines attributes (data structures) and methods (functions and procedures) into a single programming construct. The object type construct allows programmers to defined their own reusable datatypes for use in PL/SQL programs and table and column definitions.

An instance of an object type is an object in the same way that variables are instances of scalar types. 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 the database as a row object, 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] -- Oracle8i
AS OBJECT (
   attribute_name datatype,...,
   [MEMBER | STATIC PROCEDURE | FUNCTION   
      program_spec],
   [ORDER | MAP MEMBER FUNCTION 
      comparison_function_spec],
   [PRAGMA RESTRICT_REFERENCES(program_name, 
      purities)]
);

All attribute specifications must appear before all method specifications. Object attributes, like variables, are declared with a name and a datatype. The name can be any legal identifier. Attribute datatypes can be any SQL datatype except LONG, LONG RAW, NCHAR, NVARCHAR2, NCLOB, ROWID, and UROWID. Attributes cannot have datatypes unique to PL/SQL such as BOOLEAN.

Member function and procedure headers are listed in the object type specification in a comma-delimited list. Unlike in a package specification, commas (not semicolons) terminate the object type program specifications. To support object comparisons and sorting, the type can optionally include one comparison method—either ORDER or MAP.

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: index-by tables (formerly known as PL/SQL tables), nested tables, and VARRAYs.

Index-by table

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

Nested table

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 can become sparse through deletions.

VARRAYs

Variable-size arrays are 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	Index-by Table	Nested Table	VARRAY
Dimensionality	Single	Single	Single
Usable in SQL?	No	Yes

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 a Net8 listener, it can be used as the extproc listener as well. See the Oracle8 Administrators' Guide or the Net8 Administrators' Guide for the details on configuring your listener.

Section 1.18.1.2: Identify or create the shared library or DLL

This step has nothing to do with PL/SQL or the database. You must write your own C routines and link them into a shared library/DLL or use an existing library's functions or procedures. In the simple example below, we will use the existing random number generating calls available from the operating system.

Section 1.18.1.3: Create the library in the database

Create a library in the database for the shared library or DLL using the CREATE LIBRARY statement:

CREATE [OR REPLACE] LIBRARY library_name IS | AS 
	'absolute_path_and_file';

To remove libraries from the database, you use the DROP LIBRARY statement:

DROP LIBRARY library_name;

To call out to the C runtime library's rand function, you don't have to code any C routines at all, since the call is already linked into a shared library, and because its arguments are directly type-mappable to PL/SQL. If the rand function is in the standard /lib/libc.so shared library, as on Solaris, you would issue the following CREATE LIBRARY statement:

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

In Oracle8i, 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 mechanisms 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 Oracle8i.

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!

Return to Oracle PL/SQL Language Pocket Reference