Each version of the Oracle database comes with its own corresponding version of PL/SQL. As you use more up-to-date versions of PL/SQL, an increasing array of functionality will be available to you. One of our biggest challenges as PL/SQL programmers is simply “keeping up.” We need to constantly educate ourselves about the new features in each version—figuring out how to use them and how to apply them to our applications, and determining which new techniques are so useful that we should modify existing applications to take advantage of them.
Table 1-1 summarizes the major elements in each of the versions (past and present) of PL/SQL in the database. It offers a very high-level glimpse of the new features available in each version. Following the table, you will find more detailed descriptions of “what’s new” in PL/SQL in the latest Oracle versions, Oracle8i and Oracle9i.
Tip
The Oracle Developer product suite also comes with its own version of PL/SQL, and it generally lags behind the version available in the Oracle RDBMS itself. This chapter (and the book as whole) concentrates on server-side PL/SQL programming.
Table 1-1. Oracle database and corresponding PL/SQL versions
|
Oracle version |
PL/SQL version |
Characteristics |
|---|---|---|
|
Oracle6 |
1.0 |
Initial version of PL/SQL, used primarily as a scripting language in SQL*Plus (it was not yet possible to create named, reusable, and callable programs) and also as a programming language in SQL*Forms 3. |
|
Oracle7 |
2.0 |
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. |
|
Oracle 7.1 |
2.1 |
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. |
|
Oracle 7.3 |
2.3 |
Enhances functionality of PL/SQL tables, offers improved remote dependency management, adds file I/O capabilities to PL/SQL with the UTL_FILE package, and completes the implementation of cursor variables. |
|
Oracle8 |
8.0 |
The new version number reflects Oracle’s effort to synchronize version numbers across related products. PL/SQL8 is the version of PL/SQL that 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 Queuing facility). |
|
Oracle8i |
8.1 |
The first of Oracle’s i series (the “Internet database”), the corresponding release of PL/SQL offers a truly impressive set of added functionality, including a new version of dynamic SQL (native dynamic SQL), support for Java in the database, the invoker rights model, execution authority option, autonomous transactions, and high-performance “bulk” DML and queries. |
|
Oracle9i Release 1 |
9.0 |
Oracle9i came fairly quickly on the heels of Oracle8i. The first release of this version showcases the following features for PL/SQL developers: support for inheritance in object types, table functions and cursor expressions (allowing for parallelization of PL/SQL function execution), multi-level collections (collections within collections), and the CASE statement and expression. |
|
Oracle9i Release 2 |
9.2 |
The most recent version of the Oracle product set, Oracle9i Release 2 puts a major emphasis on XML (Extensible Markup Language), but also has some treats for PL/SQL developers, including associative arrays (index-by tables that can be indexed by VARCHAR2 strings in addition to integers), record-based DML (allowing you to perform an insert using a record, for example), and a thorough rewrite of UTL_FILE (now allowing you to copy, remove, and rename files). |
Oracle8i is for many developers still a relatively new release of Oracle, so we thought it would be useful to describe some of the most important Oracle8i PL/SQL features covered in this book. They are summarized in the following sections, and covered more thoroughly in the indicated chapters.
One long-standing request from PL/SQL developers has been the ability to execute and then save or cancel certain Data Manipulation Language (DML) statements (INSERT, UPDATE, DELETE) without affecting the overall session’s transaction. You can now do this with autonomous transactions.
Where would you find autonomous transactions useful in yourapplications? Here are some ideas:
- Logging mechanism
This is the classic example of the need for an autonomous transaction. You need to log error information in a database table, but don’t want that log entry to be a part of the logical transaction.
- Reusable application components
You are building an Internet application. You want to combine components from many different vendors and layers, and they need to interact in certain well-defined ways. If when one component commits, it affects all other aspects of your application, it will not function well in this environment. Autonomous transactions solve this problem.
When you define a PL/SQLblock (anonymous block, procedure, function, packaged procedure, packaged function, or database trigger) as an autonomous transaction, you isolate the DML in that block from the rest of your session. That block becomes an independent transaction that is started by another transaction, referred to as the main transaction. Within the autonomous transaction block, the main transaction is suspended. You perform your SQL operations, commit or roll back those operations, and then resume the main transaction.
There isn’t much involved in defining a PL/SQL block as an autonomous transaction. You simply include the following statement in your declaration section:
PRAGMA AUTONOMOUS_TRANSACTION;
Here is a very simple logging mechanism that relies on the autonomous transaction feature to save changes to the log without affecting the rest of the session’s transaction:
PROCEDURE write_log (
code IN INTEGER, text IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log VALUES (
code, text,
USER, SYSDATE
);
COMMIT:
END;Of course, there are all sorts of rules and some restrictions to be aware of. See Chapter 13 for all the details.
Back in the old days of Oracle7 and Oracle 8.0, whenever you executed a stored program, it executed under the authority of the owner of that program. This “rights model” was known as definer rights . This was not a big deal if your entire application—code, data, and users—worked out of the same Oracle account; however, that scenario probably fit about 0.5% of all Oracle shops. Definer rights proved to be a real pain in the neck for the other 99.5%, because usually code was stored in one schema and then shared through GRANT EXECUTE statements with other users (directly or through roles).
That centralized, stored code would not automatically apply the privileges of a user (also known as an invoker) to the code’s objects. The user might not have had DELETE privileges on a table, but the stored code did, so delete away! In some circumstances, that is just how you wanted it to work, but in others, particularly when you were executing programs relying on dynamic SQL (with either DBMS_SQL or native dynamic SQL), awesome complications ensued.
In Oracle 8.1, PL/SQL has been enhanced so that at the time of compilation, you can decide whether a program (or all programs in a package) should run under the authority of the definer (the only choice in Oracle 8.0 and below) or of the invoker of that program.
The syntax to support this invoker rights feature is simple enough. Here is a generic “run DDL” engine that relies on the new native dynamic SQL statement EXECUTE IMMEDIATE:
CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2) AUTHID CURRENT_USER IS BEGIN EXECUTE IMMEDIATE ddl_in; END; /
The AUTHID CURRENT_USER clause before the IS keyword indicates that when the runddl procedure executes, it should run under the authority of the invoker (or “current user”), not under the authority of the definer.
Chapter 20 explores both the definer and invoker rights execution models.
Ever since Oracle 7.1, we PL/SQL developers have been able to use the built-in DBMS_SQL package to execute dynamic SQL and PL/SQL. This means that at runtime, you can construct the query, a DELETE TABLE, a CREATE TABLE, or even a PL/SQL block, as a string—and then execute it. Dynamic SQL is extremely handy when you are building ad hoc query systems, when you need to execute Data Definition Language (DDL) inside PL/SQL, and just generally when you don’t know in advance exactly what you or the user will want to do. Dynamic SQL is a frequent requirement in web-based applications.
But there are some problems with DBMS_SQL; for example, it is a very complicated package; it only works with Oracle7 datatypes (no object types or INTERVALs need apply); and it is relatively slow.
So our dear friends at PL/SQL Central in Redwood Shores took pity on us all and re-implemented dynamic SQL directly in the PL/SQL language itself. This is called native dynamic SQL (NDS).
NDS introduces the EXECUTE IMMEDIATE statement to the PL/SQL language, and expands the OPEN FOR statement (for cursor variables) to support dynamic query strings to handle multi-row queries. The following function uses NDS to return the number of rows in whatever table you specify:
CREATE OR REPLACE FUNCTION tabCount (
tab IN VARCHAR2, whr IN VARCHAR2 := NULL)
RETURN INTEGER
IS
retval INTEGER;
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*)
FROM ' || tab ||
' WHERE ' || NVL (whr, '1=1')
INTO retval;
RETURN retval;
END;Those of you familiar with DBMS_SQL will find this code a breath of fresh air. See Chapter 15 for extensive coverage of this wonderful new capability.
One of the major priorities of Oracle’s PL/SQL development team is to speed up the performance of their language. This effort cannot come a moment too soon. We developers have been complaining about runtime performance for years, and finally the development team has responded (although, to be brutally honest, it seems to me that the intensive tuning steps taken in Oracle 8.0 were motivated at least partly by the need to make PL/SQL fast enough to support object types).
One area of improvement concerns the execution of bulk DML inside PL/SQL. Consider, for example, the following code that deletes each employee identified by the employee number found in the nested table list:
/* File on web: bulktiming.sql */
CREATE TYPE empnos_list_t IS VARRAY(100) OF NUMBER;
CREATE OR REPLACE PROCEDURE del_emps (list_in IN empnos_list_t)
IS
BEGIN
FOR listnum IN list_in.FIRST.. list_in.LAST
LOOP
DELETE FROM emp WHERE empno = list_in (listnum);
END LOOP;
END;Easy to write, easy to read—but what about performance? Whenever this program issues its DELETE, a context switch takes place from PL/SQL to SQL to execute that command. If there are 100 elements in the list, there are at least 100 switches, with corresponding performance degradation.
Acknowledging this common requirement and its overhead, Oracle now offers a bulk bind variation on the FOR loop—the FORALL statement. With this statement, you can recode the del_emps procedure as follows:
CREATE OR REPLACE PROCEDURE del_emps (list_in IN empnos_list_t)
IS
BEGIN
FORALL listnum IN list_in.FIRST.. list_in.LAST
DELETE FROM emp WHERE empno = list_in (listnum);
END;Now there will be many fewer context switches; all of the DELETE operations will be bundled into a single bulk operation and passed to the SQL layer together.
In addition to the FORALL bulk DML operator, Oracle 8.1 also offers the BULK COLLECT variation on the INTO clause of an implicit query. This operation allows you to retrieve multiple rows in a single context switch.
Chapter 13 gives you the inside scoop on FORALL, and BULK COLLECT is covered in Chapter 14.
Oracle8i expands significantly the use of triggers to administer a database and “publish” information about events taking place within the database. By employing database triggers on the newly defined system events and by usingOracle Advanced Queuing (AQ) within those triggers, you can take advantage of the publish/subscribe capabilities of Oracle8i.
The database event publication feature allows applications to subscribe to database events just as they subscribe to messages from other applications. The trigger syntax is extended to support system and other data events on a database or a schema. Trigger syntax also supports a CALL to a procedure as the trigger body.
You can now define a programmatic trigger on the following actions:
DML statements (DELETE, INSERT, and UPDATE)
DDL events (e.g., CREATE, DROP, and ALTER)
Database events (SERVERERROR, LOGON, LOGOFF, STARTUP, and SHUTDOWN)
These are the new trigger features available in Oracle8i:
- Triggers on nested table columns
The CAST . . . MULTISET operation allows you to trigger activity when only an attribute in a nested table column is modified.
- Database-level event triggers
You can now define triggers to respond to suchsystem events as LOGON, DATABASE SHUTDOWN, and even SERVERERROR.
- Schema-level event triggers
You can now define triggers to respond to such user- or schema-level events as CREATE, DROP, and ALTER.
Chapter 18 covers these new trigger features, as well as the more traditional DML triggers with which you can define actions on INSERT, UPDATE, and DELETE statements.
Java is a very powerful language, more robust in many ways than PL/SQL. Java also offers hundreds of classes that provide clean, easy-to-use application programming interfaces (APIs) to a wide range of functionality. In Oracle8i and above, you can now invoke Java Stored Procedures (which are, in reality, Java methods stored in the database) from within your PL/SQL applications. You do so by constructing a “wrapper” or PL/SQL layer that encapsulates a call to a Java method, thereby making it available to any environment that can invoke PL/SQL programs.
Here is an example of such a wrapper, allowing me to delete files from PL/SQL:
CREATE OR REPLACE PACKAGE xfile
IS
FUNCTION delete (file IN VARCHAR2) RETURN INTEGER
AS LANGUAGE JAVA
NAME 'JFile1.delete (java.lang.String) return int';
END;Java in the Oracle database is a big topic; Java programming all by itself is an even bigger topic. Complete treatment of either is outside the scope of this book, but Chapter 22 will give you all the information you need to leverage Java from within your PL/SQL programs.
Oracle9i Release 1 and Release 2 have added exciting new functionality to the PL/SQL language. These are summarized in the following sections and are covered more thoroughly in the indicated chapters.
New to Oracle9i Release 2, you can now use records in INSERT and UPDATE statements. Here is an example:
CREATE OR REPLACE PROCEDURE set_book_info (
book_in IN books%ROWTYPE)
IS
BEGIN
INSERT INTO books VALUES book_in;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
UPDATE books SET ROW = book_in
WHERE isbn = book_in.isbn;
END;This enhancement offers some very compelling advantages over working with individual variables or fields within a record:
- Very concise code
You can “stay above the fray” and work completely at the record level. There is no need to declare individual variables or to decompose a record into its fields when passing that data to the DML statement.
- More robust code
By working with %ROWTYPE records and not explicitly manipulating fields in those records, your code is less likely to break as changes are made to the tables and views upon which the records are based.
You can find additional information about record-based DML in Chapter 13.
A table function is a function that returns a result set (in the form of a PL/SQL collection) and can be called in the FROM clause of a query. Table functions were available in a very limited fashion in Oracle8i, but Oracle9i has greatly expanded the scope and usefulness of these functions. In Oracle9i it is now possible for you to write functions that do the following:
- Return rows from a result set in “pipelined fashion”
This means that data is returned while the function is still executing.
- Participate fully in parallel query execution
In other words, the function can be run simultaneously within multiple “slave processes” against different, partitioned elements of data.
Here is an example of the header of a pipelined table function that can run in parallel, defined so that all the rows for a given department go to the same slave process, and all rows are delivered consecutively:
CREATE OR REPLACE FUNCTION my_transform_fn (
p_input_rows in employee_info.recur_t )
RETURN employee_info.transformed_t
PIPELINED
CLUSTER P_INPUT_ROWS BY (department)
PARALLEL_ENABLE
( PARTITION P_INPUT_ROWS BY HASH (department))You can find additional information about table functions, pipelining, and parallel execution in Chapter 16.
Oracle now offers dramatically improved support for timestamps, time zone management, and interval calculations (the amount of time between two dates or timestamps). The following block showcases a number of aspects of these new datatypes and associated functions:
DECLARE
boss_free TIMESTAMP(0) WITH TIME ZONE;
steven_leaves TIMESTAMP(0) WITH TIME ZONE;
window_for_conversation INTERVAL DAY(3) TO SECOND(3);
BEGIN
boss_free := TO_TIMESTAMP_TZ (
'29-JAN-2002 12:00:00.0 US/Pacific PST',
'DD-MON-YYYY HH24:MI:SSXFF TZR TZD' );
steven_leaves := TO_TIMESTAMP_TZ (
'29-JAN-2002 16:45:00.0 US/Central CST',
'DD-MON-YYYY HH24:MI:SSXFF TZR TZD' );
window_for_conversation := steven_leaves - boss_free;
DBMS_OUTPUT.PUT_LINE (
TO_CHAR (window_for_conversation, 'HH:MI:SSXFF' ));
-- Implicit conversion from TimeZone to Date...
DBMS_OUTPUT.PUT_LINE ( ADD_MONTHS (boss_free, -5 ) );
END;Chapter 10 explores these new datatypes in detail.
On a completely different front, Oracle has now incorporated XML functionality directly into the database with the addition of the XMLType datatype (implemented as an object type). Oracle has committed itself to supporting XML, and with its own XML native datatype hopes to blur the lines between SQL and XML. In Oracle9i, you can issue SQL queries against XML documents stored in table columns. You can also use XPath syntax to search the contents of those documents.
Here is an example of inserting an XML document into a table (notice the use of the CREATEXML method of the XMLType object type to convert a string to an XML document):
INSERT INTO env_analysis
VALUES ('ACME SILVERPLATING',
TO_DATE ('15-02-2001', 'DD-MM-YYYY'),
XMLTYPE.CREATEXML (
'<?xml version="1.0"?>
<report>
<site>1105 5th Street</site>
<substance>PCP</substance>
<level>1054</level>
</report>'));The following CREATE statement utilizes XPath syntax to create a function-based index on the first 26 characters of the customer’s name on a purchase order:
CREATE UNIQUE INDEX i_purchase_order_reference
ON purchaseorder p (
SUBSTR(XMLTYPE.GETSTRINGVAL(
XMLTYPE.EXTRACT(
p.PODOCUMENT,
'/PurchaseOrder/Customer/text( )')),1,26)
);Chapter 12 provides a lot more detail on the XMLType datatype.
Oracle first introduced the object type (the closest that Oracle comes to a “class” in the, ahem, classical sense of object orientation) in Oracle8, but it was plagued by a severe lack of standard object functionality, most notably inheritance.
In Oracle9i, you can now define a hierarchy of object types in which the attributes and methods of a supertype are inherited or overwritten by the subtype. The following three object type specification definitions offer a simple example of such a hierarchy (in which food is the root, desserts are types of food, and cakes are types of desserts).
CREATE TYPE food_t AS OBJECT ( name VARCHAR2(100), food_group VARCHAR2 (100), grown_in VARCHAR2 (100), MEMBER FUNCTION price RETURN NUMBER ) NOT FINAL; / CREATE TYPE dessert_t UNDER food_t ( contains_chocolate CHAR(1), year_created NUMBER(4), OVERRIDING MEMBER FUNCTION price RETURN NUMBER ) NOT FINAL; / CREATE TYPE cake_t UNDER dessert_t ( diameter NUMBER, inscription VARCHAR2(200) -- The cake_t type has no distinct pricing calculator. ); /
Notice that the dessert_t type overrides the root’s price function, but the cake_t type inherits the price function from dessert_t.
With inheritance, there is now a much more compelling reason to give object types a serious evaluation for use in your applications. You can find out more about object types and inheritance in Chapter 21.
PL/SQL collections are array-like structures that allow you to maintain lists of information. These lists can be of simple data, such as strings, or more complex structures, such as records. In Oracle9i, Oracle extends the features of collections with two major enhancements:
Support for multiple-level collections (i.e., collections within collections).
The ability to index the contents of one type of collection (associative arrays, previously known as index-by tables) by strings as well as integers.
Here is an example of using strings for the index values or row numbers in a collection:
DECLARE
TYPE population_t IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_t;
continent_population population_t;
howmany NUMBER;
row_id VARCHAR2(64);
BEGIN
country_population('Colymphia') := 100000;
country_population('Ribalizia') := 750000;
howmany := country_population('Colymphia');
row_id := continent_population.FIRST;
DBMS_OUTPUT.PUT_LINE (continent_population(row_id));
END;
/Multi-level collections allow us to emulate N-dimensional arrays and, more generally, model arbitrarily complex data. For more information about these new collection features, check out Chapter 11.
Prior to Oracle9i, compilation of PL/SQL source code always results in a representation (usually referred to as bytecode) that is stored in the database and is interpreted at runtime by a virtual machine implemented within Oracle that, in turn, runs natively on the given platform. Oracle9i introduces a new approach. PL/SQL source code may optionally be compiled into native object code that is linked into Oracle. Native compilation can result in significant improvement in overall application performance. (Its impact is felt in compute-intensive programs, but will not affect SQL performance.)
For instructions on how to take advantage of native compilation, see Chapter 19.
You need to be aware of the version ofOracle you are running in order to know which features are available for your use. You may sometimes even have to write PL/SQL programs that can run on different versions of Oracle. In this case, you have two options:
Avoid relying on any “advanced” features of PL/SQL—that is, features that appear in a later version of the language, but not in a version that you must write for. This can be termed the “lowest common denominator” approach.
Maintain multiple versions of your programs for each of the different versions of Oracle (assuming that there are features you want, or need to take advantage of, in later versions). It is hard to maintain code using this approach. If you are in this situation, you might want to take a look at the oneversion.sql script available on the O’Reilly site. This script shows how you can determine the Oracle version from the data dictionary and then use SQL*Plus substitution variables to turn portions of your program on or off, as appropriate for a particular version.
Get Oracle PL/SQL Programming, Third Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
