Oracle PL/SQL for DBAs

About PL/SQL Versions

There are many versions of PL/SQL, and you may even find that you need to work with multiple versions in your database administration activities.

We assume for this book that Oracle Database 10g is the baseline PL/SQL version. However, where appropriate, we reference specific features introduced (or only available) in other, earlier versions. In cases where a feature is in any way version-dependent—for example, if you can use it only in Oracle Database 10g Release 2—we note that in the text.

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 users of PL/SQL is simply keeping up. We need to educate ourselves constantly 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 P-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.

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 a whole) concentrates on server-side PL/SQL.

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

Oracle database release	PL/SQL version	Characteristics
6.0	1.0	This was the 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.
7.0	2.0	This was a major upgrade to PL/SQL 1.0. It added support for stored procedures, functions, packages, programmer-defined records, PL/SQL tables, and many package extensions.
7.1	2.1	This version 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 finally execute SQL DDL statements from within PL/SQL programs.
7.3	2.3	This version enhanced the functionality of PL/SQL tables, 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.
8.0	8.0	The new version number reflected Oracle’s effort to synchronize version numbers across related products. PL/SQL 8.0 was 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 Oracle/AQ (the Oracle/Advanced Queuing facility).
8.1	8.1	The first of Oracle’s i series, the corresponding release of PL/SQL in Oracle8i Database 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.
9.1	9.1	Oracle9i 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), multi-level collections, and the CASE statement and CASE expression.
9.2	9.2	Oracle9i Database Release 2 put a major emphasis on Extensible Markup Language (XML), but also offered many other features, 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).
10.1	10.1	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 were transparently available to developers: an optimized compiler and compile-time warnings.
10.2	10.2	Oracle Database 10g Release 2, first available in the fall of 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.