#

| Chapter 1 Introduction to PL/SQL |

## 1.4 PL/SQL Versions

One thing that may complicate using PL/SQL is that it is not a single product. There are several distinct, supported versions out there. Table 1.2 summarizes the various versions; the following sections describe the main features available in each of the versions in use today.

Table 1.2: PL/SQL Versions
Version/Release | Characteristics
--- | ---
Version 1.0 | First available in SQL*Plus as a batch-processing script. Oracle Version 6.0 was released at approximately the same time. PL/SQL was then implemented within SQL*Forms Version 3, the predecessor of Oracle Forms.
Release 1.1 | Available only in the Oracle Developer/2000 Release 1 tools. This upgrade supports client-side packages and allows client-side programs to execute stored code transparently.
Version 2.0 | Available with Release 7.0 (Oracle Server). Major upgrade to Version 1. Adds support for stored procedures, functions, packages, programmer-defined records, PL/SQL tables, and many package extensions, including DBMS_OUTPUT and DBMS_PIPE.
Release 2.1 | Available with Release 7.1 of the Oracle Server Version. Supports programmer-defined subtypes, enables the use of stored functions inside SQL statements, and offers dynamic SQL with the DBMS_SQL package. With Version 2.1, you can now execute SQL DDL statements from within PL/SQL programs.
Release 2.2 | Available with Release 7.2 of the Oracle Server Version. Implements a binary "wrapper" for PL/SQL programs to protect source code, supports cursor variables for embedded PL/SQL environments such as Pro*C, and makes available database-driven job scheduling with the DBMS_JOB package.
Release 2.3 | Available with Release 7.3 of the Oracle Server Version. Enhances functionality of PL/SQL tables, offers improved remote dependency management, adds file I/O capabilities to PL/SQL, and completes the implementation of cursor variables.
Version 8.0 | Available with Oracle8 Release 8.0. The drastic change in version number reflects Oracle's effort to synchronize version numbers across related products. PL/SQL8 is the version of PL/SQL which supports the many enhancements of Oracle8, including large objects (LOBs), object-oriented design and development, collections (VARRAYs and nested tables), and Oracle/AQ (the Oracle/Advanced Queueing facility).

### 1.4.1 Working with Multiple Versions of PL/SQL

All of the releases of PL/SQL Version 2 are linked directly to the release of the Oracle Server on which they run. PL/SQL Release 1.1 is available only with the Oracle Developer/2000 Release 1 tools. The presence of these different versions can make your life complicated. Consider the following scenarios:

- You want to take full advantage of all the latest features of the Oracle software family, from the frontend tools to the backend RDBMS and stored procedures. You will therefore use PL/SQL Release 1.1 (until Oracle makes available the second version of its Oracle Developer/2000 toolset) to build client-side programs and will use PL/SQL Release 2.X through PL/SQL8 for your stored programs.

- You develop applications in a distributed environment and support different versions of the Oracle Server on different platforms. If you need to build stored programs to run in each of these databases, you will need to work with more than one release of PL/SQL.

Given this complexity, you need to be aware of the differences between the releases, as well as restrictions on PL/SQL development in Release 1.1 of PL/SQL.

### 1.4.2 How This Book Handles Different Versions of PL/SQL

This book uses Version 2.0 of the PL/SQL language as the "base" version for purposes of presenting the technology. If you are using any of the more recent releases of the PL/SQL language (2.1, 2.2, 2.3, or 8.0), you will be able to take advantage of all the standard features of PL/SQL and, in addition, leverage the enhancements of that release. If you are using Release 1.1 of PL/SQL, you will not be able to use all the features of Versions 2.0 through Version 8.0.

The new Oracle8-related functionality (delivered in PL/SQL8) is covered primarily in Part 5, New PL/SQL8 Features. Additional coverage of PL/SQL8-specific features is provided in chapters which cover that area of technology. For example, Chapter 4, Variables and Program Data, introduces the new datatypes of PL/SQL8, including large objects (LOBs).

I will note explicitly throughout the book when a particular feature is available only in a specific PL/SQL version or release. If this book does not cover a particular feature, that is mentioned as well.

The following sections summarize the new features of each PL/SQL release, as well as the restrictions placed on the use of PL/SQL Release 1.1. Review these sections now so you can more easily identify and use them later in your programming efforts.

> NOTE: If you are using the Oracle Developer/2000 suite of development tools, then you will be using PL/SQL Release 1.1 for all local PL/SQL programs. You may also run PL/SQL Version 2-based programs stored in the database from the Oracle Developer/2000 application. See the section below for those features of PL/SQL Version 2.0 that may be used in programs based on PL/SQL Release 1.1.1.

### 1.4.3 PL/SQL Version 2.0

PL/SQL Version 2.0 was first released with the Oracle Server and expanded significantly the ability of PL/SQL to support large-scale, complex, distributed application development efforts. With Version 2.0, you can modularize your programs into procedures, functions, and -- most importantly -- packages. You can store your modules in the database and call them from any Oracle session, on both the client and server sides of distributed applications.

The features of PL/SQL Version 2.0 are described in the following sections.

#### 1.4.3.1 Integration with SQL

PL/SQL was originally designed to provide procedural extensions to the SQL language. From within PL/SQL you can execute any DML statement (SELECT, UPDATE, INSERT, and DELETE). You cannot, however, execute a DDL statement, such as CREATE TABLE. This capability is available only in later releases through the use of specially provided packages, such as DBMS_SQL.

This integration with SQL also means that from within the native PL/SQL language you can make full use of all SQL operators, predicates, and built-in functions. Outside a SQL statement you can call the TO_CHAR function to convert a date to a string and check to see if one string is LIKE another string (an operator usually found in the WHERE clause of a SQL statement).

In addition to DML statements, you can use SQL's transaction-oriented statements to commit and roll back transactions. You can also mark SAVEPOINTs and roll back to those intermediate phases in a transaction.

Because the SQL is called from within PL/SQL, you can make use of PL/SQL constructs in the SQL statement. The following example references the PL/SQL variable last_hire_date in the WHERE clause of a SELECT statement:

>     SELECT employee.last_name     -- Database column
>       INTO new_hire_name          -- Local PL/SQL variable
>       FROM employee               -- Name of table
>      WHERE hire_date =            -- References column and PL/SQL variable
>                last_hire_date;

With Version 2.0, PL/SQL also supports the syntax required to perform distributed SQL. The following update changes the data in a remote table by selecting information from a second remote table:

>     UPDATE employee@NEW_YORK
>        SET salary = (SELECT MAX(salary)
>                        FROM employee@TORONTO);

NEW_YORK and TORONTO are database links to employee tables in different database instances (presumably in those cities).

PL/SQL Version 2.0 also lets you include hints to the optimizer (structured as comments within the SQL statement, enclosed by the `/*` and `*/` delimiters) to modify the execution plan used by the optimizer.

#### 1.4.3.2 Expanded set of datatypes for variables and constants

PL/SQL lets you declare local variables and constants and then use those identifiers in your PL/SQL program. You can declare the variables and constants to be a datatype known to the RDBMS, such as NUMBER or VARCHAR2. However, you can also make use of PL/SQL-specific data structures such as:

BOOLEAN

  A true Boolean datatype whose value is TRUE, FALSE, or NULL.

BINARY_INTEGER

  Similar to NUMBER, BINARY_INTEGER datatype represents values as signed binary numbers of virtually any size. Because signed binary is the internal format for numeric values, you can perform calculations with this datatype that do not require any conversions.

PL/SQL record

  A record contains one or more fields and is similar to a row in a database table. You can assign values to variables and SELECT information from the database into these variables.

In addition to these datatypes, PL/SQL Version 2.0 has added ROWID, RAW, LONG RAW, and MLSLABEL (for Trusted Oracle). PL/SQL Version 2.0 also predefines a set of "subtypes," which applies constraints to an existing "base" subtype. These subtypes include NATURAL (all integers greater than zero, a subtype of BINARY_INTEGER), and REAL (a subtype of NUMBER).

#### 1.4.3.3 Programmer-defined records

A record is a composite data structure, consisting of one or more fields or columns. A record in PL/SQL roughly corresponds to a row in a database table. With earlier versions of PL/SQL, you can create records using the %ROWTYPE attribute; however, these records can only reflect the structure of a table or cursor. With PL/SQL Version 2.0, you can create records with whatever structure you decide upon, completely independent of any particular table or cursor. A programmer-defined record may even have another record as a field in its record, thereby allowing nested records and the ability to represent real-world structures in your programs.

Here is an example of the definition of a record type, followed by a declaration of the actual record:

>     DECLARE
>        /* Create a record to hold four quarters of sales data. */
>        TYPE sales_quarters_rectype IS RECORD
>           (q1_sales NUMBER,
>            q2_sales NUMBER,
>            q3_sales NUMBER,
>            q4_sales NUMBER);
>        /*
>        || Create a record to hold sales information for a customer.
>        || Notice the nested record.
>        */
>        TYPE customer_sales_rectype IS RECORD
>           (customer_id NUMBER (5),
>            customer_name customer.name%TYPE,
>            total_sales NUMBER (15,2),
>            sales_by_quarter sales_quarters_rectype);

>        /* Create a record for use in the program of this record type. */
>        customer_sales_rec customer_sales_rectype;
>     BEGIN

You can pass records as parameters and perform aggregate assignments with records -- that is, with a single assignment operation you can assign all the values of one record to another, compatible record.

#### 1.4.3.4 PL/SQL tables

One of the first questions I ever heard posed about PL/SQL Version 1.1 was, "Where are the arrays?" Programmers who coded in the nonprocedural interface of SQL*Forms, after spending a decade with languages like FORTRAN and C, got all excited when they heard about PL/SQL: they thought they'd get all the good stuff they had in their 3GL environments -- particularly arrays. Imagine the shock and disappointment when PL/SQL not only lacked the ability to read and write disk files, but also did not support arrays!

The designers of the PL/SQL language recognized the need to manipulate data in array-like structures in memory, but they also wanted to make sure to maintain PL/SQL's nature as an extension to SQL. The result is the PL/SQL table, available with Version 2.0.

The PL/SQL table is a memory-resident object that gives you array-like access to rows of information. It is similar to, but not the same as, a database table. Currently, a PL/SQL table may contain only one column (with the datatype of your choice) and one primary key (with a mandatory datatype of BINARY_INTEGER). The following declaration section contains the definition of a table type, followed by the declaration of a table, that can be used in a program:

>     DECLARE
>        /* Table of strings to hold company names. */
>        TYPE company_names_tabtype IS TABLE OF
>           VARCHAR2(100) INDEX BY BINARY_INTEGER;

>        /* Declaration of actual table to be used in code. */
>        company_names company_names_tabtype;
>     BEGIN

PL/SQL tables can be passed as parameters in modules. Unlike arrays found in 3GL programs, PL/SQL tables are unconstrained and sparse. Unconstrained means that, as with database tables, you do not have to decide the size of a PL/SQL table in advance. Sparse means that the only rows defined in memory are those you create with an assignment to that row.

#### 1.4.3.5 Built-in functions

PL/SQL Version 2.0 supports a wide range of built-in functions to manipulate data in your programs. These built-ins may be categorized as follows:

Character functions

  Functions that analyze and modify the contents of CHAR and VARCHAR2 string variables

Date functions

  Utilities that allow programmers to perform high-level actions on date variables, including date arithmetic

Numeric functions

  A full range of functions that manipulate numbers, including trigonometric, logarithmic, and exponential functions

Conversion functions

  Functions that convert from one datatype to another, often formatting the output data at the same time

#### 1.4.3.6 Built-in packages

In addition to the built-in functions, each release of PL/SQL offers built-in packages, which bundle together related programs. These packages will prove to be invaluable in your development efforts; they are summarized in Appendix C, Built-In Packages. Here are some examples of built-in packages available with PL/SQL Release 2.0:

DBMS_OUTPUT

  Displays information to the screen; useful for debugging PL/SQL scripts.

DBMS_PIPE

  Communicates between Oracle sessions via "pipes." Oracle Corporation uses this package to parallelize their database, and you can use it to parallelize your own programs.

DBMS_LOCK

  Requests and manages programmer-defined locks.

You would, for example, use the PUT_LINE procedure in the DBMS_OUTPUT package to display information to your screen (or, in the case of the World Wide Web, to your home page):

>     DBMS_OUTPUT.PUT_LINE ('Option selected is ' || option_desc);

#### 1.4.3.7 Control structures

PL/SQL provides procedural extensions to SQL to implement the following types of control structures:

- Conditional control via IF statements

- Iterative control via loops, including FOR, WHILE, and simple loops

- Sequential control via GOTO and NULL statements

The following variations of conditional control constructs are available: IF-END IF, IF-ELSE-END IF, and IF-ELSIF-ELSE-END IF. However, PL/SQL does not support a CASE structure. All variations of the conditional structures end with an END IF statement. The result of this is a highly structured block orientation. Here is an example of an IF-ELSIF-ELSE statement:

>     IF average_salary < 10000
>     THEN
>        bonus := 2000;

>     ELSIF average_salary BETWEEN 10000 AND 20000
>     THEN
>        bonus := 1000;

>     ELSE
>        bonus := 500;

>     END IF;

PL/SQL supports a number of different types of loops:

- WHILE loops

- FOR loops (numeric and cursor)

- Infinite or "simple" loops

These constructs allow you to execute the same code repeatedly. You can nest loops within loops. All loops end with an END LOOP statement, which results in a highly structured block orientation for your loops. Here is an example of a WHILE loop which, in turn, contains a FOR loop:

>     WHILE still_searching
>     LOOP
>        FOR month_index IN 1 .. 12
>        LOOP
>           calculate_profits (month_index);
>        END LOOP;
>     END LOOP;

PL/SQL Version 2.0 also supports the GOTO statement and the NULL statement. GOTO transfers control from one executable statement to any other statement in the current program body. You can specify the NULL statement if you want to "do nothing" -- and, believe it or not, there are a number of times when that is all you want to do! This example illustrates both statements:

>     IF rooms_available = 0
>     THEN
>        GOTO no_rooms;
>     ELSE
>        reserve_a_room;
>     END IF;

>     <<no_rooms>>
>     NULL;

#### 1.4.3.8 Cursor-based access to the database

One of the most important features of PL/SQL is the ability to handle data one row at a time. SQL is a set-at-a-time database language. You cannot selectively examine or modify a single row from a SELECT statement's result set. With PL/SQL Version 2.0's cursors, however, you can attain much finer control over manipulation of information from the database. Cursors in PL/SQL can be opened, fetched from, and closed. You can use the cursor FOR loop to access all the records in a cursor quickly and easily. PL/SQL Version 1.1 also provides a useful set of cursor attributes to let you determine the current status of a cursor.

The following example declares the cursor based on a SELECT statement (along with a record to hold the information fetched from the cursor), then opens, fetches from, and closes the cursor. Before opening the cursor, the code checks the cursor attribute %ISOPEN to see if it is already open:

>     DECLARE
>        CURSOR extinction_cur IS
>           SELECT species_name, last_sighting
>             FROM rainforest
>            WHERE year = 1994
>              AND number_species_left = 0;
>        extinction_rec extinction_cur%ROWTYPE;

>        expedition_leader VARCHAR2(100);
>     BEGIN
>        /* Only open the cursor if it is not already open. */
>        IF NOT extinction_cur%ISOPEN
>        THEN
>           OPEN extinction_cur;
>        END IF;

>        /* Fetch the next record. */
>        FETCH extinction_cur INTO extinction_rec;

>        /* Execute statements based on record contents. */
>        IF extinction_rec.last_sighting = 'BRAZIL'
>        THEN
>           expedition_leader := 'RAMOS';

>        ELSIF extinction_rec.last_sighting = 'BACKYARD'
>        THEN
>           expedition_leader := 'FEUERSTEIN';
>        END IF;

>        /* Close the cursor. */
>        CLOSE extinction_cur;
>     END;

#### 1.4.3.9 Error handling

PL/SQL Version 2.0 traps and responds to errors, called exceptions, using an event-driven model. When an error occurs, an exception is raised. The normal processing in your program halts, and control is transferred to the separate exception handling section of your program (if it exists).

The exception handler mechanism allows you to cleanly separate your error-processing code from your executable statements. It provides an event-driven model, as opposed to a linear code model, for processing errors. In other words, no matter how a particular exception is raised, it is handled by the same exception handler in the exception section.

The following PL/SQL block attempts to select information from the employee and includes an exception handler for the case in which no data is found:

>     DECLARE
>        soc_sec_number NUMBER;
>     BEGIN
>        SELECT social_security#
>          INTO soc_sec_number
>          FROM employee
>         WHERE last_name = 'FEUERSTEIN';
>     EXCEPTION
>        WHEN NO_DATA_FOUND
>        THEN
>           INSERT INTO employee
>              (last_name, first_name,
>               social_security#, hire_date, department_id)
>           VALUES
>              ('FEUERSTEIN', 'STEVEN', '123456789', SYSDATE, 10);
>     END;

In other words, if I am not already an employee in the company, the SELECT statement fails and control is transferred to the exception section (which starts with the keyword EXCEPTION). PL/SQL matches up the exception raised with the exception in the WHEN clause (NO_DATA_FOUND is a named, internal exception that represents ORA-01403-no data found). It then executes the statements in that exception handler, so I am promptly inserted into the employee table.

#### 1.4.3.10 Modular construction

The ability to create modules ("black boxes") which can call each other is central to any successful programming language. Modularization of code allows you to break down complex operations into smaller, more comprehensible steps. You can then combine ("plug and play") your different modules together as building blocks.

PL/SQL is itself a block-oriented language: all code is organized into one or more blocks demarked by BEGIN and END statements. These blocks provide a high degree of structure to PL/SQL-based programs, making it easier to both develop and maintain the code. PL/SQL Version 2.0 offers both unnamed blocks (also called anonymous blocks) and named blocks. There are two types of named blocks: procedures and functions. A procedure is a sequence of executable statements that performs a particular action. A function is a block that returns a value. Here are two examples of modules:

>     PROCEDURE display_emp_status (status_code_in IN VARCHAR2)
>     /* Display a message depending on the status code supplied as a parameter. */
>     IS
>     BEGIN
>        IF status_code_in = 'O'
>        THEN
>           DBMS_OUTPUT.PUT_LINE ('Status is Open.');
>        ELSE
>           DBMS_OUTPUT.PUT_LINE ('Status is Closed.');
>        END IF;
>     END;
>     FUNCTION total_compensation
>        (salary_in IN NUMBER, commission_in IN NUMBER) RETURN NUMBER
>     /*
>     || Calculate and return total compensation. If commission is NULL
>     || then add zero to salary.
>     */
>     IS
>     BEGIN
>        RETURN salary_in + NVL (commission_in, 0);
>     END;

Procedures and functions are commonly found in programming languages. But PL/SQL goes beyond this level of modularization to also offer a construct called the package. A package is a collection of objects, including modules and other constructs, such as cursors, variables, exceptions, and records.

The package is probably the single most important and powerful addition to the PL/SQL language. With packages, PL/SQL Version 2.0 supports object-oriented design and concepts such as information hiding, encapsulation, and reusability. With packages, you can decide which code is publicly available to programmers and which code should be hidden. In addition, you can implement global variables, data structures, and values, which persist for the entire duration of a user session.

#### 1.4.3.11 Stored procedures, functions, and packages

In combination with the expanded data dictionary of Oracle Server Version 7, you can store your modules (procedures and functions) and packages inside the database itself. These stored modules -- usually referred to simply as stored procedures -- can then be executed by any Oracle session that has access to the modules. With stored procedures, PL/SQL now also supports remote procedure calls; a program on one server or client workstation can run programs stored on different servers, connected by SQL*Net.

With the advent of stored procedures, the Oracle RDBMS becomes a repository not only for data, but for program code itself. A shared area of memory, the Shared Global Area (SGA), caches compiled PL/SQL programs and supplies those objects to the PL/SQL runtime engine when needed by an Oracle session. The database manages dependencies between code and database structures and will automatically recompile invalid modules.

Stored packages also can offer improved performance because all programs in a package are loaded into memory at the same time.

### 1.4.4 PL/SQL Release 2.1

PL/SQL Release 2.1 is the release of Version 2 that comes with Version 7.1 of the Oracle Server. It supports all the features listed for PL/SQL Release 2.0 and also adds the new capabilities described in the following sections.

#### 1.4.4.1 Stored functions in SQL

The single most important enhancement in Release 2.1 is the ability to call stored functions (written in PL/SQL) from within a SQL statement. You can call stored functions anywhere in a SQL statement where an expression is allowed -- in the SELECT, WHERE, START WITH, GROUP BY, HAVING, ORDER BY, SET, and VALUES clauses. (Stored procedures, on the other hand, are in and of themselves PL/SQL executable statements; they cannot be embedded in a SQL statement.) You can use one of your own functions just as you would a built-in SQL function, such as TO_DATE, SUBSTR, or LENGTH.

The following SELECT statement calls the total_compensation function defined earlier in this chapter. This saves you from having to code the calculation explicitly:

>     SELECT last_name, total_compensation (salary, commission)
>       FROM employee
>      ORDER BY total_compensation (salary, commission) DESC;

The ability to place PL/SQL functions inside SQL is a very powerful enhancement to the Oracle development environment. With these functions you can:

- Consolidate business rule logic into a smaller number of well-tuned and easily maintained functions. You do not have to repeat this logic across individual SQL statements and PL/SQL programs.

- Improve the performance of your SQL statements. SQL is a nonprocedural language, yet application requirements often demand procedural logic in your SQL. The SQL language is robust enough to let you get at the answer, but in many situations it is a very inefficient way to get that answer. Embedded PL/SQL can do the job much more quickly.

- Simplify your SQL statements. All the reasons you have to modularize your PL/SQL code apply to SQL as well -- particularly the need to hide complicated expressions and logic behind a function specification. From the DECODE statement to nested, correlated sub-SELECTs, the readability of many SQL statements will benefit from programmer-defined functions.

#### 1.4.4.2 Support for DDL and dynamic SQL

One of the packages provided with Oracle Server Release 7.1 is the DBMS_SQL package. The modules in this package allow you to execute dynamic SQL DDL and DML statements. A SQL statement is dynamic when it is not parsed and bound at compile time. Instead, the statement itself is constructed at runtime and then is passed to the SQL engine for processing.

Dynamic SQL, particularly with DDL statements, offers many possibilities. The following procedure provides a programmatic interface to drop any kind of object from the data dictionary:

>     PROCEDURE drop_object
>        (object_type_in IN VARCHAR2, object_name_in IN VARCHAR2)
>     IS
>        cursor_id INTEGER;
>     BEGIN
>        /*
>        || Open a cursor which will handle the dynamic SQL statement.
>        || The function returns the pointer to that cursor.
>        */
>        cursor_id := DBMS_SQL.OPEN_CURSOR;
>        /*
>        || Parse and execute the drop command which is formed through
>        || concatenation of the arguments.
>        */
>        DBMS_SQL.PARSE
>          (cursor_id,
>           'DROP ' || object_type_in || ' ' || object_name_in,
>           DBMS_SQL.NATIVE);

>        /* Close the cursor. */
>        DBMS_SQL.CLOSE_CURSOR (cursor_id);
>     EXCEPTION
>        /* If any problem arises, also make sure the cursor is closed. */
>        WHEN OTHERS
>        THEN
>           DBMS_SQL.CLOSE_CURSOR (cursor_id);
>     END;

I can now drop the employee table or the total_compensation function by executing the following calls to drop_object:

>     drop_object ('table', 'employee');

>     drop_object ('function', 'total_compensation');

#### 1.4.4.3 Entry-level ANSI SQL92 support

PL/SQL Release 2.1 lets you reference column aliases in the ORDER BY clause of a DML statement. You can also use the AS keyword to define aliases in the SELECT clause of a query. In other words, you can now write a cursor in the following way:

>     DECLARE
>        CURSOR profit_cur IS
>           SELECT company_id, SUM (revenue) - SUM (cost) net_profit
>             FROM fin_performance
>            ORDER BY net_profit DESC;
>     BEGIN

In the past, you would have had to repeat the difference of SUMs in the ORDER BY clause or simply write ORDER BY 2.

#### 1.4.4.4 Programmer-defined subtypes

In addition to the subtypes provided by PL/SQL itself, PL/SQL Release 2.1 lets you create your own subtypes of native datatypes. Programmer-defined subtypes improve the readability and maintainability of your code. Here is an example of a definition of a subtype:

>     SUBTYPE primary_key_type IS NATURAL;

In this case, I create a datatype called primary_key_type of type NATURAL. Now, when I declare a variable with this type, it must be a nonzero, positive integer.

In Release 2.1, these subtypes must be unconstrained -- you cannot define a subtype as VARCHAR2(30), only as VARCHAR2.

### 1.4.5 PL/SQL Release 2.2

PL/SQL Release 2.2 is the release of Version 2 that comes with Version 7.2 of the Oracle Server. It supports all of the features previously listed in this chapter and also adds the new capabilities described in the following sections.

#### 1.4.5.1 The PL/SQL wrapper

The PL/SQL wrapper is a standalone utility that transforms PL/SQL source code into portable binary, object code. "Wrapped" or encrypted PL/SQL source code hides the internals of your application. With Release 2.2, you can distribute software without having to worry about exposing your proprietary algorithms and methods to competitors. The PL/SQL compiler automatically recognizes and loads wrapped PL/SQL program units.

#### 1.4.5.2 Cursor variables

Prior to PL/SQL Release 2.2, you could only declare and manipulate "static" cursors -- cursors that are bound at design time to a specific query and a specific cursor name. With Release 2.2, you can now declare a cursor variable and open it for any compatible query. Most importantly, you can pass and receive cursor variables as arguments to modules. Cursor variables offer tremendous new flexibility in centralizing and controlling the SQL statements in your applications.

In Release 2.2, cursor variables may only appear where PL/SQL code is embedded in a host language environment, such as the precompilers and the OCI layer. While you can OPEN a cursor with a cursor variable in your PL/SQL block, you cannot yet FETCH from that cursor. Cursor variables are made fully available within PL/SQL programs in Release 2.3, described later in this chapter.

#### 1.4.5.3 Job scheduling with DBMS_ JOB

With PL/SQL Release 2.2, Oracle Corporation offers DBMS_ JOB, a new package that allows you to schedule jobs within the database itself. Oracle uses DBMS_JOB to manage its snapshot facility. You can use it to run jobs on a regular basis. A job can be any valid PL/SQL block of code, from a single SQL statement to a complex series of calls to stored procedures.

### 1.4.6 PL/SQL Release 2.3

PL/SQL Release 2.3 is the release of Version 2 that comes with Version 7.3 of the Oracle Server. It supports all of the features previously listed in this chapter and also adds the new capabilities described in the following sections.

#### 1.4.6.1 File I/O with the UTL_FILE package

Far and away the most exciting feature of Release 2.3 is the UTL_FILE package. This collection of built-in functions and procedures allows you to read from and write to operating system files from within PL/SQL. This is a capability for which programmers have been clamoring since PL/SQL first became available.

#### 1.4.6.2 Cursor variables for all PL/SQL environments

With Release 2.3, cursor variables (described under Release 2.2) can now be used in any PL/SQL environments, and do not rely on a host language environment. You can OPEN, FETCH from, and CLOSE cursors using standard PL/SQL syntax. In addition, all cursor attributes are now available for use with cursor variables. Release 2.3 also supports a "weak" cursor type which allows you to declare a cursor variable without having to specify its record structure.

#### 1.4.6.3 Expanded PL/SQL table capabilities

Release 2.3 allows you to create PL/SQL tables of records, as opposed to simple scalar values, such as NUMBERs. In addition, it offers a set of operators or built-ins, which provide you with additional, heretofore unavailable, information about the PL/SQL table, including the following:

COUNT

  Returns the number of rows defined in the PL/SQL table

LAST

  Returns the number of the highest row defined in the PL/SQL table

DELETE

  Deletes rows from the table

PL/SQL tables of records are especially useful for representing database tables in memory.

#### 1.4.6.4 Improved remote dependency model

Prior to Release 2.3 (and the underlying Oracle Server Release 7.3), PL/SQL modules that depended on remote objects (stored procedures or tables, for example) would be flagged as invalid whenever the remote object was modified. This module emphasized safety and correctness, but was also unnecessarily restrictive. For example, as long as the call interface of a procedure has not changed (its name and parameters), any program that calls that procedure should not have to be recompiled.

Release 2.3 offers a choice between the original, "timestamp" dependency model and the new, "signature" dependency model. The signature model will only flag a client-side module as invalid (requiring a recompile) if the remote stored procedure has been modified and if the signature or call interface of the program has changed.

### 1.4.7 PL/SQL Version 8.0

PL/SQL Version 8.0 (PL/SQL8) is the version of PL/SQL that comes with Oracle 8.0, the "object-relational" version of the Oracle database. PL/SQL8 incorporates numerous significant new features and many incremental improvements. PL/SQL8 features are summarized in the following sections and are covered in this book primarily in Part 5. The following overview is not intended to be a comprehensive description of new Oracle8 features; it covers only those aspects of Oracle8 that have an impact on PL/SQL developers.

#### 1.4.7.1 Support for an object-oriented model

When a mainstream vendor like Oracle Corporation ventures into new technology waters, it is virtually certain that the change will be evolutionary rather than revolutionary. True to form, Oracle8's relational capabilities are still the mainstay of Oracle Corporation's flagship database server, and they satisfy the need for compatibility with older Oracle versions. But with the objects option, Oracle8 allows programmers to use a new set of datatypes and models drawn from object programming languages, allowing persistent objects to be created in the database and accessed, via an API, from C++, Smalltalk, Object COBOL, Java, and other languages.

Contrast this "object-relational" database approach with the true "object-oriented databases" (OODBs) that first appeared commercially in the mid-1980s. Most successful in problem domains characterized by complex, often versioned, data (such as engineering, CASE, or CAD), pure OODBs typically extend the type system of object-oriented languages to allow for persistent objects. Oracle8, on the other hand, extends the programming system of the database to allow for operations, and extends conventional datatypes to include complex structures. While these object extensions to SQL and PL/SQL sometimes look as if they were designed simply to confuse the programmer, object types in Oracle8, properly implemented, can be the cornerstone of an overall object strategy.

See Chapter 18, Object Types, for details.

#### 1.4.7.2 Oracle/AQ, the Advanced Queueing Facility

Oracle8 offers an "advanced queuing" facility which implements deferred execution of work. Oracle is positioning Oracle/AQ (Oracle/Advanced Queuing) as an alternative to the queuing mechanisms of teleprocessing monitors and messaging interfaces. Oracle/AQ will also serve as a foundation technology for workflow management applications.

Oracle/AQ is available from within PL/SQL programs through the DBMS_AQ built-in package. This package is described briefly in Appendix C and is covered in detail in Oracle Built-in Packages.

#### 1.4.7.3 Variable arrays and nested tables

Oracle8 introduces two new "collection" structures that will support a wide range of application requirements. These structures are nested tables and variable-size arrays (VARRAYs). As with Oracle8's table datatype, the new structures can be used in PL/SQL programs. But what is dramatically new is the ability to use the new collections as the datatypes of fields in conventional tables and attributes of objects. While not an exhaustive implementation of user-defined datatypes, collections offer rich new physical (and, by extension, logical) design opportunities for Oracle practitioners.

Using a collection, you can actually store a "table within a table." Relational diehards may chafe at the thought, but you can use collections as a way of putting non-first-normal-form data into a table -- entire sets of "detail data" can be squished into a column! No longer do columns need to be "atomic" in order to be retrievable or updateable. Why would you want to do this? Even setting aside theoretical arguments about "natural" data representations, Oracle8 collections provide a dramatic advantage from an application programmer's perspective: you can pass an entire collection between the database and PL/SQL using a single fetch. Under the right circumstances, you can even "pretend" that conventional data is a collection, and realize the same single-call advantages.

See Chapter 19, Nested Tables and VARRAYs, for more information.

#### 1.4.7.4 Object views

As an "object-relational" database, Oracle8 allows both objects and relational tables to coexist. If you wish, you can read and write both objects and relations in the same PL/SQL program. Using "object views," you can even make rows or columns in relational tables look and behave like objects. Object views allow the application programmer to enjoy many of the benefits of objects -- such as efficient access, convenient navigation alternatives, and consistency with new object-based applications -- when working with an underlying database of conventional tables.

Oracle Corporation emphasizes the ability of object views to ease an organization's transition to object-based design and programming. In addition to this benefit, object views also provide a means of evolving object schema designs. Since you can redefine or rebuild object views at any time, a view-based object schema is much more pliable than a table-based object schema. (Oracle 8.0.3 provides virtually no support for modifying table-based object schema.)

As of Oracle 8.0, object views (and conventional views for that matter) can have their own triggers. These "INSTEAD OF" triggers allow you to write PL/SQL code to support insert, update, or delete through almost any view you can dream up.

See Chapter 20, Object Views, for examples and more discussion.

#### 1.4.7.5 External procedures

This long-awaited Oracle feature allows you to call anything that you can compile into the native "shared library" format of the operating system. The external procedures feature is reliable and multi-user. Communication is bidirectional and, importantly, you can use external procedures as user-defined functions in SQL.

Under UNIX, a shared library is a shared object or .so file; under Windows NT, it's a DLL (dynamic linked library). You can write the external routine in any language you wish, as long as your compiler and linker will generate the appropriate shared library format that is callable from C. In Oracle 8.0, however, C will be the most common language for external procedures, since all of Oracle's support libraries are written in C.

See Chapter 21, External Procedures, for further details and examples.

#### 1.4.7.6 Large object support

Oracle8 and PL/SQL8 support several variations of LOB or Large OBject datatypes. LOBs can store large amounts (up to four gigabytes) of raw data, binary data (such as images), or character text data.

Within PL/SQL you can declare LOB variables of the following datatypes:

BFILE

  Declares variables which hold a file locator pointing to a large binary object in an operating system file outside the database

BLOB

  Declares variables which hold a LOB locator pointing to a large binary object

CLOB

  Declares variables which hold a LOB locator pointing to a large block of single-byte, fixed-width character data

NCLOB

  Declares variables which hold a LOB locator pointing to a large block of single-byte or fixed-width multibyte character data

There are two types of LOBs in Oracle8: internal and external. Internal LOBs (BLOBs, CLOBs, and NCLOBs) are stored in the database and can participate in transactions in the database server. External LOBs (BFILEs) are large binary data stored in operating system files outside the database tablespaces. External LOBs cannot participate in transactions. You cannot, in other words, commit or roll back changes to a BFILE. Instead, you rely on the underlying filesystem for data integrity.

Chapter 4, and Chapter 13, Numeric, LOB, and Miscellaneous Functions, provide additional details.

### 1.4.8 PL/SQL Release 1.1

PL/SQL Release 1.1 is only used by the tools in the Oracle Developer/2000 suite: Oracle Forms, Oracle Reports, and Oracle Graphics. Table 1.3 reviews PL/SQL Version 2.0 functionality and indicates any restrictions or special information you will need in order to determine if and how you can make use of those features under Release 1.1.

Table 1.3: PL/SQL Version 1.1 Restrictions
PL/SQL Version 2.0 Feature | Restrictions for Version 1.1
--- | ---
Integration with SQL | You cannot perform distributed DML statements.
Expanded set of datatypes for variables and constants | The BINARY_INTEGER datatype is only available in Version 2.0.
Programmer-defined records | This feature is undocumented in PL/SQL Version 1.1 manuals, but is available.
Built-in functions | The trigonometric, logarithmic, and other "scientific" functions are not implemented.
Built-in packages | You can make use of built-in packages both in the database and in the particular tool. Oracle Forms, for example, offers the OLE package for manipulating OLE2 objects.
Control structures | You have access to all Version 2.0 control structures.
Cursor-based access to the database | You have access to all Version 2.0 cursor features.
Error handling | Exception handling is fully implemented in Release 1.1.
Modular construction | You can build procedures, functions, and packages, but the packages do not offer the same sets of capabilities as those stored in the database.
Stored procedures, functions, and packages | Not available in PL/SQL Release 1.1. Release 1.1 is for client-side application development. The PL/SQL code for these components definitely is not stored in the database.
PL/SQL tables | You cannot declare and use PL/SQL tables in Release 1.1. You can, however, construct stored packages, which serve as interfaces to these data structures, and then call those stored modules from your client application.

1.3 The Origins of PL/SQL |  | 1.5 Advice for Oracle Programmers

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.