O'Reilly logo

Oracle PL/SQL Programming, 5th Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

About PL/SQL Versions

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. (Note that in early versions of the database, PL/SQL version numbers differed from database release numbers, but since Oracle8 Database, they have been identical.) The table 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 version, Oracle Database 11g.


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 database itself. This chapter (and the book as a whole) concentrates on server-side PL/SQL programming.

Table 1-1. Oracle database and corresponding PL/SQL versions

Oracle Database release

PL/SQL version highlights


The initial version of PL/SQL (1.0) was 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.


This major upgrade (2.0) to PL/SQL 1.0 added support for stored procedures, functions, packages, programmer-defined records, PL/SQL tables (now known as collections), and many package extensions.


This PL/SQL version (2.1) supported programmer-defined subtypes, enabled the use of stored functions inside SQL statements, and offered dynamic SQL with the DBMS_SQL package. With PL/SQL 2.1, you could execute SQL DDL statements from within PL/SQL programs.


This PL/SQL version (2.3) provided enhanced functionality of collections, offered improved remote dependency management, added file I/O capabilities to PL/SQL with the UTL_FILE package, and completed the implementation of cursor variables.


The new version number (8.0) for PL/SQL reflected Oracle’s effort to synchronize version numbers across related products. PL/SQL 8.0 is the version of PL/SQL that supported enhancements of Oracle8 Database, including large objects (LOBs), object-oriented design and development, collections (VARRAYs and nested tables), and the Oracle/Advanced Queuing facility (Oracle/AQ).


The first of Oracle’s i series, the corresponding release of PL/SQL offered a truly impressive set of added functionality, including a new version of dynamic SQL, support for Java in the database, the invoker rights model, the execution authority option, autonomous transactions, and high-performance “bulk” DML and queries.


Oracle 9i Database Release 1 came fairly quickly on the heels of its predecessor. The first release of this version included support for inheritance in object types, table functions and cursor expressions (allowing for parallelization of PL/SQL function execution), multilevel collections, and the CASE statement and CASE expression.


Oracle 9i Database Release 2 put a major emphasis on XML (Extensible Markup Language) but also had some treats for PL/SQL developers, including associative arrays 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 many improvements to UTL_FILE (which allows you to read/write files from within a PL/SQL program).


Oracle Database 10g Release 1 was unveiled in 2004 and focused on support for grid computing, with an emphasis on improved/automated database management. From the standpoint of PL/SQL, the most important new features, an optimized compiler and compile-time warnings, were transparently available to developers:


Oracle Database 10g Release 2, released in 2005, offered a small number of new features for PL/SQL developers, most notably support for preprocessor syntax that allows you to conditionally compile portions of your program, depending on Boolean expressions you define.


Oracle Database 11g Release 1 arrived in 2007. The most important feature for PL/SQL developers was the function result cache, but there are also some other goodies like compound triggers, the CONTINUE statement, and native compilation that produces machine code.


Oracle Database 11g Release 2 became available in the fall of 2009. The most important new feature overall is the edition-based redefinition capability, which allow administrators to “hot patch” applications while they are being executed by users.

Oracle Database 11g New Features

Oracle Database 11g offers a number of new features that improve the performance and usability of PL/SQL. It also rounds out some “rough edges” of the language. Here is a summary of the most important changes for PL/SQL developers (all features are available in both Release 1 and Release 2 unless otherwise noted).

Edition-based redefinition capability (Release 2 only)

Historically, applications built on Oracle Database had to be taken offline while the application’s database objects were patched or upgraded. Oracle Database 11g Release 2 introduces revolutionary new capabilities that allow online application upgrades with uninterrupted availability of the application. Existing sessions can continue to use the pre-upgrade application until their users decide to finish; and, at the same time, new sessions can use the post-upgrade application. When there are no sessions using the pre-upgrade application any longer, it can be retired. The application as a whole therefore enjoys hot rollover from the pre-upgrade version to the post-upgrade version.

This new capability relies on a number of database features, but the biggest one is edition-based redefinition. While application architects will be responsible for most of the edition-based redefinition tasks, this capability will also be of great interest to developers.

The edition-based redefinition capability is introduced in Chapter 20.

FORCE option with CREATE TYPE (Release 2 only)

You can now specify that you want to force the “CREATE OR REPLACE” of a new type, even if it has other types that depend on it. In earlier versions, such an attempt would raise the ORA-02303 exception.

See Chapter 26 for more information on the FORCE option.

Function result cache

Prior to the release of Oracle Database 11g, package-based caching offered the best, most flexible option for caching data for use in a PL/SQL program. Sadly, the circumstances under which it can be used are quite limited, since the data source must be static, and memory consumption grows with each session connected to the Oracle database.

Recognizing the performance benefit of this kind of caching, Oracle implemented the function result cache in Oracle Database 11g Release 1 and enhanced it in Release 2. This feature offers a caching solution that overcomes the weaknesses of package-based caching and offers performance that is almost as fast. When you turn on the function result cache for a function, you get the following benefits:

  • The Oracle database stores both inputs and the return value in a separate cache for each function. The cache is shared among all sessions connected to this instance of the database; it is not duplicated for each session.

  • Whenever the function is called, the database checks to see if it has already cached the same input values. If so, then the function is not executed. The values in the cache are simply returned.

  • Whenever changes are made to tables that are identified as dependencies for the cache, the database automatically invalidates the cache. Subsequent calls to the function will then repopulate the cache with consistent data.

You will definitely want to seek out opportunities to apply this outstanding feature, which is described in much more detail in Chapter 21.

CONTINUE statement

Oracle Database 11g offers a new feature for loops: the CONTINUE statement. Use this statement to exit the current iteration of a loop and immediately continue on to the next iteration of that loop. This statement comes in two forms, just like EXIT: the unconditional CONTINUE and the conditional CONTINUE WHEN.

Here is a simple example of using CONTINUE WHEN to skip over loop body execution for even numbers:

   FOR l_index IN 1 .. 10
      CONTINUE WHEN MOD (l_index, 2) = 0;
      DBMS_OUTPUT.PUT_LINE ('Loop index = ' || TO_CHAR (l_index));

The CONTINUE statement is described in detail in Chapter 5.

Sequences in PL/SQL expressions

You can now reference the sequence_name.CURRVAL and sequence_name.NEXTVAL elements natively in PL/SQL. A SELECT FROM SYS.dual is no longer necessary.

See Chapter 14 for more details.

Dynamic SQL enhancements

The PL/SQL development team has greatly increased the interoperability between the two types of dynamic SQL (DBMS_SQL and native dynamic SQL), as well as improved the completeness of feature coverage. You can, for example, now convert between a DBMS_SQL cursor number and a cursor variable. You can also EXECUTE IMMEDIATE a CLOB.

Oracle Database 11g also has enhanced the security of DBMS_SQL. The package now protects against the situation in which a program that uses DBMS_SQL and raises an exception allows an attacker to use the unclosed cursor to compromise the security of the database. Security enhancements include the generation of unpredictable (probably randomized) cursor numbers, restriction of the use of DBMS_SQL whenever an invalid cursor number is passed to a DBMS_SQL program, and rejection of a DBMS_SQL operation when the current user attempting to use the cursor has changed from the user that opened the cursor.

See Chapter 16 for information about these features.

New native compilation and SIMPLE datatypes

The PL/SQL native compiler now generates native machine code directly, instead of translating PL/SQL code to C code and having the C compiler generate that machine code. Working with native compilation is now also simpler: an individual developer can compile PL/SQL units for native execution without any intervention by a DBA. With natively compiled code, you can expect to see substantial improvement in execution speed, perhaps by as much as an order of magnitude. With native compilation turned on, you can also benefit from improved performance with several new, specialized numeric datatypes: SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE.

Native compilation is described in Chapter 24. The new numeric types are described in Chapter 9.


The terminology for the LOB implementation has changed in Oracle Database 11g. Oracle has re-engineered the implementation of LOBs using a technology called SecureFiles. SecureFiles improves many aspects of managing LOBs, including disk format, caching, locking, redo, and space management algorithms. This updated technology significantly improves performance and allows LOBs to be deduplicated, compressed, and encrypted using simple parameter settings.

For more information on using SecureFiles, see Chapters 13 and 23.

Trigger enhancements

You can now create a compound trigger that allows you to combine what were previously distinct triggers (BEFORE and AFTER events) into a single body of code with separate event sections. This trigger will make it easier to maintain complex trigger logic, especially for mutating table trigger errors. You can also now explicitly specify the order of executions when you have more than one trigger defined on the same event (and the same database object).

Compound triggers are described in detail in Chapter 19.

Automatic subprogram inlining

A new level of compiler optimization (3) now implements automated subprogram inlining, which means that the compiler replaces a local subprogram call (to a subprogram declared in the same PL/SQL unit) with a copy of the code implementing that subprogram. This optimization reduces runtime execution since a “lookup” to find and execute that subprogram is no longer needed.

Chapter 21 describes the optimization levels and other aspects of PL/SQL performance.


PL/Scope is a compiler-driven tool that collects and organizes data about user-defined identifiers from PL/SQL source code, and makes that information available through the ALL_IDENTIFIERS data dictionary view. PL/Scope makes it much easier to build automatic, sophisticated quality assurance and search processes for your applications. You will most likely take advantage of PL/Scope through the PL/SQL editor you are using, but you can also write (somewhat complex) queries against ALL_IDENTIFIERS to “mine” your code base.

See Chapter 20 for a more detailed description of PL/Scope.

PL/SQL hierarchical profiler

In Oracle Database 11g, Oracle complements the existing PL/SQL Profiler (DBMS_PROFILER) with a new hierarchical profiler. By using the supplied DBMS_HPROF package, you can obtain information about the dynamic execution profile of your PL/SQL code, organized by subprogram calls. This profiler accounts for SQL and PL/SQL execution times separately. Each subprogram-level summary in the dynamic execution profile includes key data, including the number of calls to a subprogram, how much time is spent in the subprogram, how much time is spent in the subprogram’s subtree (any subprograms it calls), and detailed parent-children information.

Chapter 21 discusses both the traditional profiler and the hierarchical profiler.

Fine-grained dependency tracking

Prior to Oracle Database 11g, dependency information was recorded only with the granularity of the object as a whole. If any change at all was made to that object, all dependent program units were marked INVALID, even if the change did not affect that program unit. In Oracle Database 11g, Oracle has fine-tuned its dependency tracking down to the element within an object. In the case of tables, for example, the Oracle database now records that a program unit depends on specific columns within a table. With fine-grained dependency tracking, the database can avoid the recompilation that was required in earlier versions of the database, making it easier for you to evolve your application code base.

See Chapter 20 for more discussion of fine-grained dependency tracking.

Supertype invocation from subtype

One restriction in Oracle’s object-oriented functionality that has been lifted in Oracle Database 11g is the ability to invoke a method of a supertype that is overridden in the current (or higher-level) subtype. Prior to Oracle Database 11g, if you overrode a supertype’s method in a subtype, there was no way that you could call the supertype’s method in an instance of the subtype. This is now possible. In Oracle’s implementation of supertype invocation, you don’t simply refer to the supertype with a generic SUPERTYPE keyword, as is done in some other object-oriented languages. Instead, you must specify the specific supertype from the hierarchy. This approach is more flexible (you can invoke whichever supertype method you like, but it also means that you must hardcode the name of the supertype in your subtype’s code.

See the further discussion of this feature in Chapter 26.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required