PL/SQL Features Introduced in Oracle11g

Following are the list PL/SQL Features introduced in Oracle11g

• Automatic subprogram inlining
• A continue statement
• A cross-session PL/SQL function result cache
• Dynamic SQL enhancements
• Mixed, named, and positional notation SQL calls
• A multiprocess connection pool
• A PL/SQL Hierarchical Profiler
• That the PL/SQL Native Compiler now generates native code
• PL/Scope
• Regular expression enhancements
• A SIMPLE_INTEGER datatype
• Direct sequence calls in SQL statements

Automatic Subprogram Inlining

Inlining a subprogram replaces the call to the external subprogram with a copy of the subprogram. This almost always improves program performance. You could instruct the compiler to inline subprograms by using the PRAGMA INLINE compiler directive in PL/SQL starting with the Oracle 11g Database. You must set the PRAGMA when you have the PLSQL_OPTIMIZE_LEVEL parameter set to 2.
Let’s say you have an ADD_NUMBERS stored function in a schema; you can then instruct a PL/SQL program unit to inline the call to the ADD_NUMBERS function. This would be very useful when you call the ADD_NUMBERS function in a loop, as in this example:

CREATE OR REPLACE PROCEDURE inline_demo
( a NUMBER
, b NUMBER ) IS
  PRAGMA INLINE(add_numbers,’YES’);
BEGIN
  FOR i IN 1..10000 LOOP
    dbms_output.put_line(add_function(8,3));
  END LOOP;
END;

The database automates inlining choices when you set the PLSQL_OPTIMIZE_LEVEL parameter to 3. This generally frees you from identifying when it is appropriate to inline function calls. However, these are only recommendations to the compiler. It is recommended that you let the engine optimize your code during compilation.

Continue Statement

The CONTINUE statement has finally been added to the PL/SQL language. Some may have mixed emotions. There are opinions that the continue statement leads to less-than-optimal programming, but generally it simplifies loop structures.
The CONTINUE statement signals an immediate end to a loop iteration and returns to the first statement in the loop. The following anonymous block illustrates using a continue statement when the loop index is an even number:

BEGIN
  FOR i IN 1..5 LOOP
    dbms_output.put_line(’First statement, index is ['||i||'].’);
    IF MOD(i,2) = 0 THEN
      CONTINUE;
    END IF;
    dbms_output.put_line(’Second statement, index is ['||i||'].’);
  END LOOP;
END;

The MOD function returns a zero when dividing any even number, so the second line is never printed, because the CONTINUE statement aborts the rest of the loop

Cross-Session PL/SQL Function Result Cache

The cross-session PL/SQL function result cache is a mechanism to share frequently accessed functions in the SGA between sessions. Prior to the Oracle 11g Database, each call to a function with the same actual parameters, or run-time values, was cached once per session. The only work-around to that functionality required you to code the access methods.
You designate either of the following to cache results:

RESULT_CACHE clause
or
RESULT_CACHE RELIES_ON(table_name)

The RELIES_ON clause places a limitation on the cached result. Any change to the referenced table invalidates the function, as well as any functions, procedures, or views that depend on the function. The overhead when calling the function for the first time is no different than that from calling a non-cached result. Likewise, the cache will age out of the SGA when it is no longer actively called by sessions

Dynamic SQL Enhancements

Dynamic SQL still has two varieties in the Oracle 11g Database. You have Native Dynamic SQL, also known as NDS, and the DBMS_SQL built-in package. Both have been improved in this release.

Native Dynamic SQL
In Oracle 11g, native dynamic SQL now supports dynamic statements larger than 32KB by accepting CLOB. You access it them in lieu of a SQL statement by using the following syntax:

OPEN cursor_name FOR dynamic_string;

The dynamic string can be a CHAR, VARCHAR2, or CLOB. It cannot be a Unicode NCHAR or NVARCHAR2. This removes the prior restriction that limited the size of dynamically built strings.

The DBMS_SQL Built-in Package

Several changes have improved the utility of the DBMS_SQL package. Starting with Oracle 11g, you can now use all NDS-supported datatypes. Also, you can now use the PARSE procedure to work with statements larger than 32KB. This is done by using a CLOB datatype. The CLOB replaces the prior work-around that used a table of VARCHAR2 datatypes (typically VARCHAR2A or VARCHAR2S). Fortunately, the DBMS_SQL package continues to support the work-around, but you should consider moving forward to the better solution.

DBMS_SQL has added two new functions: the TO_REFCURSOR and TO_CURSOR_NUMBER functions. They let you transfer reference cursors to cursors and vice versa. There naturally are some words of wisdom on using these. You must open either the cursor or system reference cursor before using them, and after running them you cannot access their old structures. Basically, the code reassigns the interactive reference from the cursor to system reference cursor or from the system reference cursor to the cursor.

Last but certainly not least, you can now perform bulk binding operations against user-defined collection types. Collection types can be scalar arrays. You were previously restricted to the types defined by the DBMS_SQL package specification.

Mixed Name and Position Notation Calls

The Oracle 11g Database brings changes in how name and positional notation work in both SQL and PL/SQL. They actually now work the same way in both SQL and PL/SQL. This fixes a longstanding quirk in the Oracle database.

PL/SQL Calls

Previously, you had two choices. You could list all the parameters in their positional order or address some to all parameters by named reference. You can now use positional reference, named reference, or a mix of both.
The following function will let you experiment with the different approaches. The function accepts three optional parameters and returns the sum of three numbers.

CREATE OR REPLACE FUNCTION add_three_numbers
( a NUMBER := 0, b NUMBER := 0, c NUMBER := 0 ) RETURN NUMBER IS
BEGIN
  RETURN a + b + c;
END;

The first three subsections show how you call using positional, named, and mixed notation. In these you provide actual parameters for each of the formal parameters defined by the function signature. You can also exclude one or more values because all formal parameters are defined as optional, which means they have default values

Positional Notation
You call the function using positional notation by

BEGIN
  dbms_output.put_line(add_three_numbers(3,4,5));
END;

Named Notation
You call the function using named notation by

BEGIN
  dbms_output.put_line(add_three_numbers(c => 4,b => 5,c => 3));
END;

Mixed Notation
You call the function using a mix of both positional and named notation by

BEGIN
  dbms_output.put_line(add_three_numbers(3,c => 4,b => 5));
END;

There is a restriction on mixed notation. All positional notation actual parameters must occur first and in the same order as they are defined by the function signature. You cannot provide a position value after a named value.

Exclusionary Notation
As mentioned, you can also exclude one or more of the actual parameters when the formal parameters are defined as optional. All parameters in the ADD_THREE_NUMBERS function are optional. The following example passes a value to the first parameter by positional reference, and the third parameter by named reference:

BEGIN
  dbms_output.put_line(add_three_numbers(3,c => 4));
END;

When you opt to not provide an actual parameter, it acts as if you’re passing a null value. This is known as exclusionary notation. This has been the recommendation for years to list the optional variables last in function and procedure signatures. Now, you can exclude one or a couple but not all optional parameters. This is a great improvement, but be careful how you exploit it.

SQL Call Notation

Previously, you had only one choice. You had to list all the parameters in their positional order because you couldn’t use named reference in SQL. This is fixed in Oracle 11g; now you can call them just as you do from a PL/SQL block. The following demonstrates mixed notation in a SQL call:

SELECT add_three_numbers(3,c => 4,b => 5) FROM dual;

As in earlier releases you can only call functions that have IN mode–only variables from SQL statements. You cannot call a function from SQL when any of its formal parameters are defined as IN OUT or OUT mode–only variables. This is because you must pass a variable reference when a parameter has an OUT mode. Functions return a reference to OUT mode variables passed as actual parameters.

Multiprocess Connection Pool

Enterprise JavaBeans (EJBs) just got better with the release of multiprocess connection pooling in the Oracle 11g Database. It is officially Database Resident Connection Pooling (DRCP). This feature lets you manage a more scalable server-side connection pool. Prior to this release you would leverage shared server processes or a multithreaded Java Servlet.

The multiprocess connection pool significantly reduces the memory footprint on the database tier, and it boosts the scalability of both the middle and database tiers. A standard database connection requires 4.4MB of real memory; 4MB is allotted for the physical connection and 400KB for the user session. Therefore, 500 dedicated concurrent connections would require approximately 2.2GB of real memory. A shared-server model is more scalable and requires only 600MB of real memory for the same number of concurrent users. Eighty percent of that memory would be managed in Oracle’s Shared Global Area (SGA). Database Resident Connection Pooling scales better and would require only 400MB of real memory. Clearly for web-based applications DRCP is the preferred solution, especially when using OCI8 persistent connections.

The behaviors of these models dictate their respective scalability. Figure graphically depicts memory use for the three models from 500 to 2,000 concurrent users

The new feature is delivered by the new DBMS_CONNECTION_POOL built-in package. This package lets you start, stop, and configure connection pooling parameters such as size and time limit. You start the connection pool as the SYS user by using the following command:

SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();

You must enable your tnsnames.ora file to support the connection to the shared pool. The following enables a shared pool connect descriptor, provided you substitute a correct hostname, domain, and Oracle listener port number:

ORCLCP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)
               (HOST = hostname.domain)
               (PORT = port_number)
    )
    (CONNECT_DATA = (SERVER = POOLED)
                    (SERVICE_NAME = orcl)
    )
  )

The SERVER key in the connect descriptor directs connections to the connection pooling service. You can only use the connection pool when you have a supported Oracle 11g Database or Oracle 11g Client, though this feature could be subsequently backported. The following error is raised when attempting to connect with an older release client or server library:

ERROR:
ORA-56606: DRCP: Client version does not support the feature

The message is signaled from the server when it fails to create an appropriate socket; it indicates that it is dropping the remote connection pool request.

PL/SQL Hierarchical Profiler

The hierarchical profiler delivered in the Oracle 11g Database lets you see how all components in an application perform. This differs from a non-hierarchical (flat) profiler that simply records the time spent in each module. Hierarchical profilers follow the execution cycle from the containing program down to the lowest subprogram.
The PL/SQL Hierarchical Profiler does the following:

• It reports the dynamic execution profile of your PL/SQL program, which is organized by subprogram calls.
• It divides SQL and PL/SQL execution times and reports them separately.
• It requires no special source or compile-time preparation, like the PRAGMA required for recommending inlining.
• It stores results in a set of database tables, which you can use to develop reporting tools or alternatively use the plshprof command-line tool to generate simple HTML reports.

The subprogram-level summary includes information about the number of subprogram calls, time spent in subprograms or their subtrees, and detailed information between modules.

PL/SQL Native Compiler Generates Native Code

PL/SQL Native compilation changes in the Oracle 11g Database. Unlike prior versions where the PL/SQL was translated first into C code and then compiled, you can now compile directly. Execution speed of the final code in some cases is twice as fast or an order of magnitude greater.

Oracle recommends that you run all PL/SQL in either NATIVE or INTERPRETED mode. INTERPRETED mode is the database default, and PL/SQL modules are stored as clear text or wrapped text. You can view stored programs by querying the ALL_SOURCE, DBA_SOURCE, or USER_SOURCE data dictionary tables. NATIVE-mode code is compiled into an intermediate form before being reduced to machine-specific code. A copy of the code is also stored in the data dictionary, while the library is positioned in an external directory. You map the physical directory to the virtual directory defined by the PLSQL_NATIVE_LIBRARY_DIR database parameter.

Natively compiled code is advantageous when the PL/SQL code run time is slow. This can happen with compute-intensive code, but generally performance delays are caused by SQL statement processing. You should use the new PL/SQL Hierarchical Profiler to determine if there is a significant advantage to support your conversion effort.

PL/Scope

The PL/Scope is a compiler-driven tool that lets you examine identifiers and their respective behaviors in the Oracle 11g Database. It is disabled by default. You can enable it for the database or session. You should consider enabling it for the session, not the database. You enable it by using the following command:

ALTER SESSION SET PLSCOPE_SETTINGS = ‘IDENTIFIERS:ALL’;

The PL/Scope utility does not collect statistics until you enable it. The statistics let you examine how your programs use identifiers. It should be
added to your arsenal of tuning tools.

Regular Expression Enhancement

Oracle 11g Release 1 enhances the functionality of the REGEXP_LIKE and REGEXP_INSTR functions and introduces the REGEXP_COUNT function.

SIMPLE_INTEGER Datatype

The Oracle 11g Database introduces the SIMPLE_INTEGER. It is a derived subtype of BINARY_INTEGER, and it has the same range of values. Unlike BINARY_INTEGER, SIMPLE_INTEGER excludes the null value and overflow is truncated. Also, the truncation from overflow does not raise an error. You should use the SIMPLE_INTEGER type if you opt to natively compile your code because it provides significant performance improvements in compiled code.

This section has reviewed the Oracle 11g Database new features, or it has referred you to other areas of the book for more information. Clearly, the new features make upgrading very appealing.

Direct Sequence Calls in SQL Statements

Oracle 11g finally lets you call a sequence with the .nextval or .currval inside SQL commands, which means you can dispense with this:

SELECT some_sequence.nextval
INTO   some_local_variable
FROM   dual;

Related Posts

1. Oracle PL/SQL: Loops and Conditional Statements
2. DB2 with DotNet and SQL Server Reporting Services
3. Oracle PL/SQL: Cursors
4. SQL Server 2008 Top New Features
5. Pass by Value and Pass by Reference in Java

Tags: continue statement, cross session PL/SQL, oracle 11g, PL/Scope, pl/sql features, PL/SQL Hierarchical Profiler, PL/SQL Native Compiler, plsql, Subprogram Inlining