Reprinted with Permission by RevealNet, Inc.  July  2001
PL/SQL Enhancements in Oracle9i
By Sandeepan Banerjee, Director of Product Mgmt, Oracle Server Technologies,
Chris Racicot, Director, SQL/DSS, Oracle Server Technologies.

Introduction

PL/SQL is Oracle's procedural language extension to SQL. PL/SQL enables you to mix SQL statements with procedural constructs. With PL/SQL, you can define and execute PL/SQL program units such as procedures, functions, and packages. The PL/SQL engine, which processes PL/SQL program units, is a special component of many Oracle products, including the Oracle server. For example, for a traditional Employee table, it is possible to define a procedure HIRE_EMP to place a new Employee record in the table:

Procedure HIRE_EMP (name VARCHAR2, job VARCHAR2,
mgr NUMBER, hiredate DATE, sal NUMBER,
comm NUMBER, deptno NUMBER)

BEGIN
.
.
INSERT INTO emp VALUES (emp_sequence.NEXTVAL, name, job, mgr, hiredate, sal, comm, deptno);
.
.
END;

The procedure can be called from Oracle SQL*Plus or Oracle Enterprise Manager as:

EXECUTE hire_emp ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20);

PL/SQL can improve database performance in several ways:

In addition, by designing applications around a common set of PL/SQL procedures or functions, you can avoid redundant coding and increase your productivity.  Stored procedures improve the integrity and consistency of your applications. By developing all of your applications around a common group of procedures, you can reduce the likelihood of committing coding errors.

For example, you can test a procedure or function to guarantee that it returns an accurate result and, once it is verified, reuse it in any number of applications without testing it again. If the data structures referenced by the procedure are altered in any way, only the procedure needs to be recompiled. Applications that call the procedure do not necessarily require any modifications.

The PL/SQL engine, which processes PL/SQL program units, is a component of many Oracle products, including the Oracle server. Figure 1 below shows the SQL and PL/SQL engines contained in the Oracle server.

When an application calls a procedure stored in the database, Oracle loads the compiled program unit (i.e. the parsed representation of the procedure) into the shared pool in the system global area (SGA). The PL/SQL and SQL statement executors work together to process the statements within the procedure. Queries, which have no procedural aspect, are handled directly by the SQL engine.

PL/SQL can execute dynamic SQL statements whose complete text is not known until runtime. Dynamic SQL statements are stored in character strings that are entered into, or built by, the program at runtime. This enables you to create general- purpose procedures. For example, using dynamic SQL allows you to create a procedure that operates on a table whose name is not known until runtime.

Over the years, Oracle has enhanced PL/SQL with myriad features to the point where today it is a finely honed engine that addresses the most data intensive operations for an enormous variety of applications around the world.

 What’s New in Oracle9i for PL/SQL?

A number of trends have come together in recent years to cause a widely felt evolution in application development environments. New channels – the browser-based intranet application, the portal, the exchange, or the wireless gateway -- have been opened-up to the end-user.  More and more applications have to contend with the demands of a global, multi-lingual, 24x7 base of users. Content is richer, data more complex than ever before. Compressed development cycles put pressure on those environments that try to integrate a variety of niche solutions.

As developers struggle to upgrade their skills to cope with these challenges and also exploit the new opportunities, they look to build upon what they already know. Oracle’s SQL and PL/SQL technologies are widely available and understood. As discussed above, a huge proportion of enterprise applications are built using these technologies. The most popular business-to-business (B2B) and business-to-consumer (B2C) applications use Oracle back-end servers.  Oracle’s software serves up content for the most demanding content-management (CM) applications.  However, whatever be the nature of final application, success depends on a functional, integrated, easy-to-use, robust development platform. In Oracle9i, we have added a number of capabilities to ensure that Oracle remains the best development platform for both Internet and intranet applications.

In Oracle9i, a number of enhancements have been made to PL/SQL:

Let us look at each of the above in greater detail.

PL/SQL Native Compilation and performance

A number of significant performance enhancements have been made to PL/SQL. Primary among these are native compilation, bulk binding, and pipelined table functions for PL/SQL.

Native Compilation of PL/SQL

The PL/SQL native compiler feature in Oracle9i compiles PL/SQL code for faster access. The greatest benefit is to applications that use stored procedures, triggers etc. and are data intensive; whether the PL/SQL runs in the application server or database server, performance can be very significantly improved. By taking fewer cycles to run a piece of logic, this also enables the Oracle server scalability to improve.

In Oracle9i, a PL/SQL library unit can be compiled as native C code, rather than interpreted as byte code. It is then stored as a shared library in the file system. The process of compiling a PL/SQL function or procedure is very simple:

ALTER FUNCTION my_func COMPILE;

Compilation results in faster execution of PL/SQL programs. The improvement in execution speed is due to the following factors:

The compiled code corresponding to a PL/SQL program is mapped to a PGA as opposed to SGA to allow better concurrent access. With native compilation, PL/SQL that does not contain SQL references can be 2-10 times faster, though performance remains dependent on a large number of application-specific factors.

To turn native compilation on, you need to issue the following statement:

ALTER SESSION SET plsql_compiler_switches=NATIVE;

This is all you need to do after a simple 1-time setup by your DBA to configure Oracle to run native code securely.  This setting is persistent and if the procedure is automatically recompiled it will recompile in native mode.

Bulk Binds

A number of other functional enhancements to PL/SQL provide additional performance benefits to server-resident mission-critical business logic. Prominent among these are Bulk Bind enhancements and Bulk Dynamic SQL.

Bulk Binding allows the manipulation of an entire collection of rows in a single DML statement. In general, such binding reduces the number of database calls, and lets applications with long transactions to execute with fewer iterations Internally, bulk binds facilitate the usage of PL/SQL records and tables, improving performance.

Bulk Bind features have been enhanced in Oracle9i to support more efficient and convenient bulk bind operations. Restrictions on usage of collections in SELECT and FETCH clauses have been removed. Error handling for failure in bulk binds has been provided.

Native Dynamic SQL, introduced in Oracle8i, now supports BULK operations. The main benefit of Bulk Dynamic SQL is the improvement in performance --the number of context switches between SQL statement executor and PL/SQL engine is reduced.

Common SQL Parser

Traditionally, SQL and PL/SQL have had different parsers for SQL.  In Oracle9i, there is a common SQL parser. This eliminates duplication of SQL parsing and analysis, and enables PL/SQL to pick up all SQL changes as they are introduced.

Pipelined, Parallelized Table Functions

Table functions introduced in Oracle8i enable various internal or external, simple or complex data stores to be represented to Oracle as tables and operated on thereafter using the relational metaphor. Oracle 9i supports pipelining and parallelization of table functions. Table Functions can be defined in PL/SQL using a native PL/SQL interface or in Java or C using the Oracle Data Cartridge Interfaces.  Table functions reduce response time by piping the results as soon as they are produced, without waiting for the entire data source to be materialized in memory. Pipelining eliminates the need for buffering the produced rows. Table functions can return multiple rows during each invocation. The number of invocations is reduced, thereby improving performance.

Other secular performance improvements in PL/SQL deal with significantly faster record construction and copying, as well as improvements in dealing with cross-package references.  Oracle9i achieves a 60% reduction in the overhead of calling PL/SQL procedures from SQL statements.

With Oracle9i, PL/SQL extends the envelope for fast, reliable, data intensive server-resident business logic.

Transparent Performance Improvements

A number of behind-the-scenes optimizations also contribute to PL/SQL performance in Oracle9i. Certain record operations are much faster.  More, importantly, calling PL/SQL functions from SQL on a per row basis is now 60% (or more) faster than in 8i. This can greatly simplify the lives of SQL programmers who can now abstract away common expressions into stored procedures and use them from their SQL statements without taking large performance hits.

PL/SQL and XML

XML is not only a popular suite of standards, but it is also fundamental emerging technology that will enable the building of the next generation of loosely coupled, semantically rich applications which make efficient use of network resources.  Integrating XML with the SQL and PL/SQL engines was an important design goal of Oracle9i.  Several important new capabilities related to XML make their debut in Oracle9i.

XMLType

Oracle9i introduces a native XMLType data type to enable storage of XML documents. Data stored in XMLType columns (or object tables) is queryable from SQL, with XPath support. A number of useful functions are supplied with XMLType to enable common XML node processing operations. XMLType can also be invoked from PL/SQL procedures and functions. In addition, Oracle9i also introduces native XML generation and aggregation in the form of SQL operators and PL/SQL packages. These server-based XML greatly increase the throughput of operations on XML.

Generating and Aggregating XML

In Oracle8i Release 3, Oracle introduced the XML-SQL Utility (XSU) and the related XSQL Servlet to generate XML from SQL queries. The XSU ran as a Java client-program to the database. In Oracle9i, a similar generation capability is introduced natively in SQL and PL/SQL, increasing the XML generation throughput by many orders of magnitude. The XSU will, of course, continue to be available as part of the Oracle9i XDK to help developers access pre-9i databases.

DBMS_XMLGEN

The DBMS_XMLGEN package can creates a XML document from any SQL query, returning the result as a CLOB. It also provides an interesting  "fetch" interface that can be used to control the maximum rows returned, rows to skip --useful for pagination in web applications. DBMS_XMLGEN is very flexible and provides options for changing tag names for returned results to suit the application consuming generated XML. Here is an example of using DBMS_XMLGEN:

CREATE TABLE temp_clob_tab(result CLOB);

  DECLARE

  qryCtx DBMS_XMLGEN.ctxHandle;

    result CLOB;

  BEGIN

    qryCtx := dbms_xmlgen.newContext

    ('SELECT * from employees;');

 DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE');

  DBMS_XMLGEN.setMaxRows(qryCtx, 5);

  LOOP

   result := DBMS_XMLGEN.getXML(qryCtx);

  EXIT WHEN

  DBMS_XMLGEN.getNumRowsProcessed((qryCtx)=0);

 INSERT INTO temp_clob_tab VALUES(result);

  END LOOP;

END;

/

The returned XML results look like:

<?xml version=''1.0''?>

<ROWSET>

 <EMPLOYEE>

 <EMPLOYEE_ID>30</EMPLOYEE_ID>

 <LAST_NAME>SCOTT</LAST_NAME>

 <SALARY>20000<SALARY>

 </EMPLOYEE>

  <EMPLOYEE>

<EMPLOYEE_ID>31</EMPLOYEE_ID>

  <LAST_NAME>MARY</LAST_NAME>

  <AGE>25</AGE>

 </EMPLOYEE>

</ROWSET>

DBMS_XMLGEN can also be used to generate more complex XML, using SQL object types to represent nested structures. More information about such use can be found in the Oracle9i Application Developers Guide for XML.  In this context, also see the new SQL operators, SYS_XMLGEN and SYS_XMLAGG, which also help generate XML natively from SQL.

URI_References

XML documents in the server often refer to other XML documents (or fragments) inside or outside the server. As such, a mechanism is needed to intelligently specify such references, and efficiently operate on them.

An URI_Reference is a generalization of the URL concept. It consists of two parts – a URL part (such as http://www.oracle.com/xml/doc1#//), and a fragment part (such as warehouse_spec/custno).

URI_References come in two flavors. A DBURIType is a intra-database reference – it can ‘point to’ objects inside a database, e.g.:

/SCOTT/EMPLOYEES/ROW[ADDRESS/STATE=’CA’]/CITY

DBURITypes can address different granularities – such as a schema, a table, a row, a column, an object type attribute within a column and so on.

An HTTPURIType, on the other hand, can be used to access documents or fragments outside the database. 

We also provide a base type called URIType, which is an abstract type that can store instances of either of the above, useful when your references can be either local or remote. Here is an example of using URITypes.

CREATE TABLE tax_deductible_tab (

                 uri_col        UriType,

                 max_deduction  NUMBER(7,2),

                 description    VARCHAR2);

INSERT INTO tax_deductible_tab VALUES (

 UriFactory.getURL('/SCOTT/Warehouses/ROW[Area=2500'),

 2500.00, 'Scott's Warehouse Deductible');

INSERT INTO tax_deductible_tab VALUES (

 UriFactory.getURL ('http://proxy.oracle.com/webaccts/pos/scott/po1'),

 1000.00, 'Scott's Web Deductible');

As you can see, URITypes provide a uniform mechanism to model references whether inside or outside the database. This is of great benefit in building loose-ly coupled XML applications (common in many B2B or B2C applications, as well as in many corporate portals) where the targets of references are often weakly typed and where the target content can move in and out of different kinds of repositories.  URITypes can be accessed seamlessly from SQL and PL/SQL:

SELECT e.ur_col.getClob() FROM tax_deductible_tab e;

DECLARE

  V_uri UriType;

BEGIN

    SELECT uri_col into v_uri FROM tax_deductible_tab

    WHERE Description LIKE 'Scott%';

    printDataOut(v_uri.getClob());

END;

URITypes lead to improved mapping of XML documents to columns, and also help efficient indexing, navigation, querying of XML content.

PL/SQL Functional Enhancements

HTTP ‘Cookie’ Support in PL/SQL

A number of Internet packages, such as UTL_TCP, UTL_HTTP, UTL_SMTP etc. were available in Oracle8i. These have been enhanced in Oracle9i., with a more native implementation that improves performance.

Another new piece of functionality in PL/SQL relates to the standard UTL_HTTP package. UTL_HTTP has been enhanced to enable the use of HTTP cookies in running web applications. A PL/SQL program can instruct the UTL_HTTP package to maintain HTTP cookies set by the web application. Cookies are returned to the parent application when the PL/SQL program accesses other web pages. There is also a transparent connection improvement for processing cookies. Each request does not have to be a session; instead, all requests can be part of the same transaction.

Inheritance Support in PL/SQL

Oracle8 introduced support for objects in the server along with relational tables to enable the same data-model across all tiers. In Oracle9i, Oracle’s Object-Relational vision achieves functional and operational completeness with the introduction of features such as inheritance, multi-level collections, type evolution and so on. Inheritance and multi-level collections bring the server’s modeling capabilities closer to that provided by Java, C++ or XML, making it indeed easy to model business objects in the database and achieve uniformity of data models across tiers. 

PL/SQL supports the notion of substitutable variables: a variable intended to hold a supertype (or a REF to one) can be assigned a subtype (or a REF to one).

It is also possible to dispatch overloaded methods polymorphically. A method invoked on an object is dispatched (‘virtually’) to the specific implementation based on the runtime type.

DECLARE

person_var  person_type;

BEGIN

person_var := person_type(…);  

person_var.some_method();  -- invokes some_method() of person

person_var := employee_type(…); –- employee_type inherits from person type and overrides some_method()

person_var.some_method(); -- invokes some_method() of employee

END;

PL/SQL also supports multi-level collections and the other object-relational enhancements in Oracle9i.

PL/SQL CASE Expressions

Oracle9i adds ANSI standard CASE expression support to SQL. Mirroring this functionality is the PL/SQL CASE statement, which supports the grammar rules of the SQL:1999 CASE expressions. Also new are NULLIF and COALESCE expressions, which are shorthand for CASE expressions. NULLIF can be thought of as the inverse of the familiar NVL function, whereas COALESCE is similar to NVL, except that it can take a list of values.

Datatype Enhancements

In Oracle9i, several new datatypes have been introduced in SQL. Prominent among these are the globalized date-time datatype, the TIMESTAMP, and the INTERVAL datatype.  These datatypes enable global e-business applications to track time-related information with accuracy up to a billionth or a second. These datatypes can be used in PL/SQL variable declarations, as return values of functions, as well as parameters in functions and procedures.

Oracle9i also introduces Unicode datatypes such as UNICHAR, UNIVARCHAR2 and UNICLOB. These datatypes are supported in PL/SQL as well. All PL/SQL functions and procedures that take strings as arguments are now able to accept and return UTF-16 arguments.

Conclusion

At the center of Oracle’s time-tested platform stand SQL and PL/SQL. In Oracle9i, architectural enhancements make PL/SQL significantly faster. PL/SQL has been integrated with XML as never before. PL/SQL has also been enhanced to provide a number of functional improvements. With these enhancements, the Oracle development stack makes available today what other frameworks are promising for tomorrow.