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