What's New in PL/SQL?

This section briefly describes the new PL/SQL features for 11g Release 1 (11.1) and provides links to additional information.

New PL/SQL Features for 11g Release 1 (11.1)

The new PL/SQL features for 11g Release 1 (11.1) are:

• Enhancements to Regular Expression Built-in SQL Functions

• SIMPLE_INTEGER Datatype

• CONTINUE Statement

• Sequences in PL/SQL Expressions

• Dynamic SQL Enhancements

• Named and Mixed Notation in PL/SQL Subprogram Invocations

• Cross-Session PL/SQL Function Result Cache

• Compound Triggers

• More Control Over Triggers

• Database Resident Connection Pool

• Automatic Subprogram Inlining

• PL/Scope

• PL/SQL Hierarchical Profiler

• PL/SQL Native Compiler Generates Native Code Directly

Enhancements to Regular Expression Built-in SQL Functions

The regular expression built-in functions REGEXP_INSTR and REGEXP_SUBSTR have increased functionality. A new regular expression built-in function, REGEXP_COUNT, returns the number of times a pattern appears in a string. These functions act the same in SQL and PL/SQL.

SIMPLE_INTEGER Datatype

The SIMPLE_INTEGER datatype is a predefined subtype of the PLS_INTEGER datatype that has the same range as PLS_INTEGER (-2,147,483,648 through 2,147,483,647) and has a NOT NULL constraint. It differs significantly from PLS_INTEGER in its overflow semantics.

You can use SIMPLE_INTEGER when the value will never be NULL and overflow checking is unnecessary. Without the overhead of checking for nullness and overflow, SIMPLE_INTEGER provides significantly better performance than PLS_INTEGER when PLSQL_CODE_TYPE='NATIVE', because arithmetic operations on SIMPLE_INTEGER values are done directly in the hardware. When PLSQL_CODE_TYPE='INTERPRETED', the performance improvement is smaller.

For more information, see:

• "SIMPLE_INTEGER Subtype of PLS_INTEGER"

• "Use PLS_INTEGER or SIMPLE_INTEGER for Integer Arithmetic"

CONTINUE Statement

The CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration (in contrast with the EXIT statement, which exits a loop and transfers control to the end of the loop). The CONTINUE statement has two forms: the unconditional CONTINUE and the conditional CONTINUE WHEN.

For more information, see:

• "Controlling Loop Iterations (LOOP, EXIT, and CONTINUE Statements)"

• "CONTINUE Statement"

Sequences in PL/SQL Expressions

The pseudocolumns CURRVAL and NEXTVAL make writing PL/SQL source code easier for you and improve run-time performance and scalability. You can use sequence_name.CURRVAL and sequence_name.NEXTVAL wherever you can use a NUMBER expression.

For more information, see "CURRVAL and NEXTVAL".

Dynamic SQL Enhancements

Both native dynamic SQL and the DBMS_SQL package have been enhanced.

Native dynamic SQL now supports a dynamic SQL statement larger than 32 KB by allowing it to be a CLOB—see "EXECUTE IMMEDIATE Statement" and "OPEN-FOR Statement".

In the DBMS_SQL package:

• All datatypes that native dynamic SQL supports are supported.

• The DBMS_SQL.PARSE function accepts a CLOB argument, allowing dynamic SQL statements larger than 32 KB.

• The new "DBMS_SQL.TO_REFCURSOR Function" enables you to switch from the DBMS_SQL package to native dynamic SQL.

• The new "DBMS_SQL.TO_CURSOR_NUMBER Function" enables you to switch from native dynamic SQL to the DBMS_SQL package.

Named and Mixed Notation in PL/SQL Subprogram Invocations

Before Release 11.1, a SQL statement that invoked a PL/SQL subprogram had to specify the actual parameters in positional notation. As of Release 11.1, named and mixed notation are also allowed. This improves usability when a SQL statement invokes a PL/SQL subprogram that has many defaulted parameters, and few of the actual parameters must differ from their default values.

For an example, see the SELECT statements following Example 8-5.

Cross-Session PL/SQL Function Result Cache

A function result cache can save significant space and time. Each time a result-cached function is invoked with different parameter values, those parameters and their result are stored in the cache. Subsequently, when the same function is invoked with the same parameter values, the result is retrieved from the cache, instead of being recomputed.

Before Release 11.1, if you wanted your PL/SQL application to cache the results of a function, you had to design and code the cache and cache-management subprograms. If multiple sessions ran your application, each session had to have its own copy of the cache and cache-management subprograms. Sometimes each session had to perform the same expensive computations.

As of Release 11.1, PL/SQL provides a cross-session function result cache. To use it, use the RESULT_CACHE clause in each PL/SQL function whose results you want cached. Because the function result cache is stored in a shared global area (SGA), it is available to any session that runs your application.

If you convert your application from single-session caching to cross-session caching, your application will use more SGA, but significantly less total system memory.

For more information, see:

• "Using the Cross-Session PL/SQL Function Result Cache"

• "Function Declaration and Definition"

Compound Triggers

A compound trigger is a Database Manipulation Language (DML) trigger that can fire at more than one timing point.

The body of a compound trigger supports a common PL/SQL state that the code for all of its sections can access. The common state is established when the triggering statement starts and destroyed when the triggering statement completes, even when the triggering statement causes an error.

Before Release 11.1, application developers modeled the common state with an ancillary package. This approach was both cumbersome to program and subject to memory leak when the triggering statement caused an error and the after-statement trigger did not fire. Compound triggers make it easier to program an approach where you want the actions you implement for the various timing points to share common data.

For more information, see "Compound Triggers".

More Control Over Triggers

The SQL statement CREATE TRIGGER now supports ENABLE, DISABLE, and FOLLOWS clauses that give you more control over triggers. The DISABLE clause lets you create a trigger in the disabled state, so that you can ensure that your code compiles successfully before you enable the trigger. The ENABLE clause explicitly specifies the default state. The FOLLOWS clause allows you to control the firing order of triggers that are defined on the same table and have the same timing point.

For more information, see:

• "Ordering of Triggers"

• "Enabling Triggers"

• "Disabling Triggers"

Database Resident Connection Pool

DBMS_CONNECTION_POOL package is meant for managing the Database Resident Connection Pool, which is shared by multiple middle-tier processes. The database administrator uses procedures in DBMS_CONNECTION_POOL to start and stop the Database Resident Connection Pool and to configure pool parameters such as size and time limit.

For more information, see "DBMS_CONNECTION_POOL Package".

Automatic Subprogram Inlining

Subprogram inlining replaces a subprogram call (to a subprogram in the same program unit) with a copy of the called subprogram, which almost always improves program performance.

You can use PRAGMA INLINE to specify that individual subprogram calls are, or are not, to be inlined. You can also turn on automatic inlining—that is, ask the compiler to search for inlining opportunities—by setting the Oracle parameter PLSQL_OPTIMIZE_LEVEL to 3 (the default is 2).

In the rare cases when automatic inlining does not improve program performance, you can use the PL/SQL hierarchical profiler to identify subprograms for which you want to turn off inlining.

For more information, see:

• "How PL/SQL Optimizes Your Programs"

• "INLINE Pragma"

PL/Scope

PL/Scope is a compiler-driven tool that collects and organizes data about user-defined identifiers from PL/SQL source code. Because PL/Scope is a compiler-driven tool, you use it through interactive development environments (such as SQL Developer and JDeveloper), rather than directly.

PL/Scope enables the development of powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code.

For more information, see "Collecting Data About User-Defined Identifiers".

PL/SQL Hierarchical Profiler

The PL/SQL hierarchical profiler reports the dynamic execution profile of your PL/SQL program, organized by subprogram calls. It accounts for SQL and PL/SQL execution times separately. Each subprogram-level summary in the dynamic execution profile includes information such as number of calls to the subprogram, time spent in the subprogram itself, time spent in the subprogram's subtree (that is, in its descendent subprograms), and detailed parent-children information.

You can browse the generated HTML reports in any browser. The browser's navigational capabilities, combined with well chosen links, provide a powerful way to analyze performance of large applications, improve application performance, and lower development costs.

For more information, see "Profiling and Tracing PL/SQL Programs".

PL/SQL Native Compiler Generates Native Code Directly

The PL/SQL native compiler now generates native code directly, instead of translating PL/SQL code to C code and having the C compiler generate the native code. An individual developer can now compile program units for native execution without any set-up on the part of the DBA. Execution speed of natively compiled PL/SQL programs improves, in some cases by an order of magnitude.

For more information, see "Compiling PL/SQL Program Units for Native Execution".