L/SQL is Oracle’s procedural extension to industry-standard SQL. Prior to Oracle 8i, PL/SQL was in a sort of static condition. It has not been changed much since Oracle 7. I believe one of the main factors that pushed the Oracle PL/SQL team to make significant enhancements was the introduction of Java in Oracle. With the release of Oracle 8i, the database server now supports two major programming languages, PL/SQL and Java. Oracle believes that PL/SQL is still the best solution for SQL developers and SQL-centered applications, while Java is a general-purpose language for more general applications.

Oracle 8i introduces multiple new features that extend the power, performance, and flexibility of PL/SQL. Knowing what is new and what has changed is extremely important for both developers and DBAs.

Native Dynamic SQL
Native Dynamic SQL is one of the most significant new PL/SQL features in Oracle 8i. Prior to Oracle 8i, DDL (Data Definition Language) and dynamic DML (Data Manipulation Language) statements could be only executed utilizing the DBMS_SQL package. Oracle 8i introduces the EXECUTE IMMEDIATE command, which provides a much simpler way of creating and executing DDL statements, dynamic SQL, and dynamic PL/SQL. Only one statement is needed, rather than multiple statements, which the DBMS_SQL package required.

Native Dynamic SQL allows developers to write much more compact code, which is easier to read and maintain. This code executes faster because it’s embedded into the PL/SQL engine; it’s not external to PL/SQL DBMS_SQL package calls. According to Oracle, Native Dynamic SQL provides 30 to 60 percent performance improvements over DBMS_SQL.

The EXECUTE IMMEDIATE command accepts any SQL statement except SELECT ones that retrieve multiple rows. It is important to note that it could accept bind variables with a USING clause, thereby improving code performance significantly by allowing you to reuse parsed SQL statements from an SGA (System Global Area) pool.

In my example, Customer data is divided by region for performance reasons and stored in four tables with an identical structure: Customer_West, Customer_East, Customer_North, and Customer_South. The Create_Customer stored procedure is used to insert records with customer data in one of the tables determined by the first parameter passed to the procedure.

Here is a simplified version of the Create_Customer stored procedure written before Oracle 8i with the DBMS_SQL package:

CREATE OR REPLACE PROCEDURE Create_Customer(Table_Name VARCHAR2,Customer_ID INTEGER,Customer_Lastname VARCHAR2,Customer_Firstname VARCHAR2,Customer_Address VARCHAR2, Customer_City VARCHAR2,Customer_State VARCHAR2,Customer_Zip VARCHAR2,Customer_Phone VARCHAR2) IScsr_handle INTEGER;cSQL_Statement VARCHAR2(200);cnt_rows BINARY_INTEGER;BEGIN   cSQL_Statement := ‘INSERT INTO ‘ || LTRIM(RTRIM(Table_Name)) ||      ‘ VALUES(:Id, :Last, :First, :Address, :City,       :State, :Zip, :Phone)’;– Step 1: open cursor.csr_handle := DBMS_SQL.OPEN_CURSOR;– Step 2: parse cursor.DBMS_SQL.PARSE(csr_handle, cSQL_Statement, DBMS_SQL.NATIVE);– Step 3: bind values to the variables.DBMS_SQL.BIND_VARIABLE(csr_handle, ‘:Id’,      Customer_ID);DBMS_SQL.BIND_VARIABLE(csr_handle, ‘:Last’,    Customer_Lastname);DBMS_SQL.BIND_VARIABLE(csr_handle, ‘:First’,   Customer_Firstname);DBMS_SQL.BIND_VARIABLE(csr_handle, ‘:Address’, Customer_Address);DBMS_SQL.BIND_VARIABLE(csr_handle, ‘:City’,    Customer_City);DBMS_SQL.BIND_VARIABLE(csr_handle, ‘:State’,   Customer_State);DBMS_SQL.BIND_VARIABLE(csr_handle, ‘:Zip’,     Customer_Zip);DBMS_SQL.BIND_VARIABLE(csr_handle, ‘:Phone’,   Customer_Phone);– Step 4: execute cursor.cnt_rows := DBMS_SQL.EXECUTE(csr_handle);– Step 5: close cursor.DBMS_SQL.CLOSE_CURSOR(csr_handle);  EXCEPTION      WHEN OTHERS THEN      RAISE_APPLICATION_ERROR(-20101,          ‘Error in procedure Create_Customer.’); END Create_Customer;

Here is the same procedure rewritten in Oracle 8i with Native Dynamic SQL:

CREATE OR REPLACE PROCEDURE Create_Customer(Table_Name VARCHAR2,Customer_ID INTEGER,Customer_Lastname VARCHAR2,Customer_Firstname VARCHAR2,Customer_Address VARCHAR2, Customer_City VARCHAR2,Customer_State VARCHAR2,Customer_Zip VARCHAR2,Customer_Phone VARCHAR2) IScSQL_Statement VARCHAR2(200);BEGIN   cSQL_Statement := ‘INSERT INTO ‘ || LTRIM(RTRIM(Table_Name)) ||      ‘ VALUES(:Id, :Last, :First, :Address, :City,       :State, :Zip, :Phone)’;   EXECUTE IMMEDIATE cSQL_Statement      USING Customer_ID, Customer_Lastname, Customer_Firstname,       Customer_Address, Customer_City, Customer_State, Customer_Zip,      Customer_Phone;EXCEPTION      WHEN OTHERS THEN      RAISE_APPLICATION_ERROR(-20101,          ‘Error in procedure Create_Customer.’);END Create_Customer;

As you can see, this syntax is much more concise and easy to read.

NOCOPY Parameter Option
Prior to Oracle 8i there were three types of parameter-passing options to procedures and functions:

• IN: parameters are passed by reference
• OUT: parameters are implemented as copy-out
• IN OUT: parameters are implemented as copy-in/copy-out

The technique of OUT and IN OUT parameters was designed to protect original values of them in case exceptions were raised, so that changes could be rolled back. Because a copy of the parameter set was made, rollback could be done. However, this method imposed significant CPU and memory overhead when the parameters were large data collections?for example, PL/SQL Table or VARRAY types.

With the new NOCOPY option, OUT and IN OUT parameters are passed by reference, which avoids copy overhead. However, parameter set copy is not created and, in case of an exception rollback, cannot be performed and the original values of parameters cannot be restored.

According to Oracle, benchmarking showed 30 to 200 percent improvements for medium-to-large PL/SQL tables passed as parameters.

Here is an example of using the NOCOPY parameter option:

TYPE Note IS RECORD(   Title VARCHAR2(15),   Created_By VARCHAR2(20),   Created_When DATE,   Memo VARCHAR2(2000));TYPE Notebook IS VARRAY(2000) OF Note;CREATE OR REPLACE PROCEDURE Update_Notes(Customer_Notes IN OUT NOCOPY Notebook) ISBEGIN   …END;

Profiler
An Oracle 8i PL/SQL programmer develops a large number of packages, so the need to identify and solve performance problems becomes critical. Oracle 8i provides a profiler that analyzes PL/SQL code and locates bottlenecks. The DBMS_PROFILER package is an API that provides the capability to gather statistics related to the execution of the PL/SQL program and identify performance problems. The DBMS_PROFILER package is not created by default with the database; you have to generate it with Oracle’s ProfLoad.sql script. This script has to be executed by the SYS user and access has to be granted to PUBLIC. This script calls two other scripts: Dbmspbp.sql and Prvtpbp.plb.

There are three steps in a typical profiler session:

1. Start the profiler to collect statistics for a session.
   
2. Execute PL/SQL program unit to generate profiling data.
   
3. Stop the profiler.

To use the DBMS_PROFILER package, database tables and other structures must be created through the Proftab.sql script. There are three tables to be created:

• PLSQL_PROFILER_DATA
  
• PLSQL_PROFILER_RUNS
  
• PLSQL_PROFILER_UNITS

This script can be executed under the schema executing the DBMS_PROFILER package or under a centralized schema. In case of a centralized schema, you need to create a PUBLIC synonym on the objects and grant SELECT and INSERT privileges on the tables.

The statistics gathered by the profiler include the number of times each line was executed, the total execution time for each line, and minimum and maximum execution times of each line. This information is logged in the database tables and can be retrieved via a SQL query or any report facility. To show how to retrieve this information, Oracle provides two scripts (Profrep.sql and Profsum.sql) located in the PLSQLDemo subdirectory under the Oracle_Home directory.

For further information related to the DBMS_PROFILER package, please refer to the Oracle8i Server On-Line Documentation or the Dbmspbp.sql script comments.