PL/SQL Enhancements in Oracle Database 10g

Oracle 10g includes many PL/SQL enhancements including:

• PL/SQL Native Compilation
• FORALL Support for Non-Consecutive Indexes (Sparse Collections)
• New IEEE Floating-Point Types
• Improved Overloading With Numeric Types
• Nested Table Enhancements
• Compile-Time Warnings
• Quoting Mechanism for String Literals
• Implicit Conversion Between CLOB and NCLOB
• Regular Expressions
• Flashback Query Functions
• UTL_COMPRESS
• UTL_MAIL

PL/SQL Native Compilation

The process of PL/SQL native compilation has been simplified in Oracle 10g. The compiled shared libraries are now stored in the database and extracted as necessary. This means they form part of the normal backup and recovery process, require no manual maintenance and are available in Real Application Cluster (RAC) configurations. Native compliation of the package specification and body are independant of each other, meaning either one, the other or both can be natively compiled.

The PLSQL_NATIVE_LIBRARY_DIR parameter is the only one which must be set to use native compilation. All other parameters have been obsoleted. The associated compiler commands are stored in the $ORACLE_HOME/plsql/spnc_commands file which should not need to be modified.

Native compilation is switched on and off using the PLSQL_CODE_TYPE parameter which can be set at instance and session level using the ALTER SYSTEM and ALTER SESSION commands respectively. The following is an example of native PL/SQL compilation.

-- Set the PLSQL_NATIVE_LIBRARY_DIR parameter.
CONN / AS SYSDBA
ALTER SYSTEM SET PLSQL_NATIVE_LIBRARY_DIR='/u01/app/oracle/native/' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

-- Switch on native compilation and compile a procedure.
CONN scott/tiger
ALTER SESSION SET PLSQL_CODE_TYPE='NATIVE';

CREATE OR REPLACE PROCEDURE test_speed AS
  v_number  NUMBER;
BEGIN
  FOR i IN 1 .. 10000000 LOOP
    v_number := i / 1000;
  END LOOP;
END;
/

SET TIMING ON
EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.19

-- Switch off native compilation and recompile the procedure.
ALTER SESSION SET PLSQL_CODE_TYPE='INTERPRETED';
ALTER PROCEDURE test_speed COMPILE;
EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.03

-- Clean up.
DROP PROCEDURE test_speed;

FORALL Support for Non-Consecutive Indexes (Sparse Collections)

Oracle 10g introduces support for the FORALL syntax with non-consecutive indexes in collections. The INDICES OFclause allows the FORALL syntax to be used with sparse collections, while the VALUE OF clause is used for collections of indexes pointing to other collections. The following are examples of their usage.

DECLARE
  TYPE t_tab1 IS TABLE OF tab1%ROWTYPE;
  TYPE t_tab2 IS TABLE OF BINARY_INTEGER;

  l_tab1  t_tab1 := t_tab1();
  l_tab2  t_tab2 := t_tab2();
BEGIN
  FOR i IN 1 .. 1000 LOOP
    l_tab1.extend;
    l_tab1(l_tab1.last).id := i;
    IF MOD(i, 100) = 0 THEN
      l_tab2.extend;
      l_tab2(l_tab2.last) := i;
    END IF;
  END LOOP;

  l_tab1.delete(301);
  l_tab1.delete(601);
  l_tab1.delete(901);

  -- This would fail due to sparse collection.
  --FORALL i IN l_tab.first .. l_tab.last
  --  INSERT INTO tab1 VALUES l_tab(i);

  -- This works fine with sparse collections.
  FORALL i IN INDICES OF l_tab1
    INSERT INTO tab1 VALUES l_tab1(i);

  -- This works fine for collections of indexes
  -- pointing to elements of another collection.
  FORALL i IN VALUES OF l_tab2
    INSERT INTO tab1 VALUES l_tab1(i);
END;
/

New IEEE Floating-Point Types

Oracle 10g introduces the new IEEE floating-point types BINARY_FLOAT and BINARY_DOUBLE. The types are extremely efficient for heavy floating point computations as the work is passed directly to the operating system. Literal assignments can be perfomed using the "f" and "d" suffixes or conversion functions TO_BINARY_FLOAT and TO_BINARY_DOUBLE.

DECLARE
  l_binary_float   BINARY_FLOAT;
  l_binary_double  BINARY_DOUBLE;
BEGIN
  l_binary_float  := 1.1f;
  l_binary_double := 1.00001d;

  l_binary_float  := TO_BINARY_FLOAT(1.1);
  l_binary_double := TO_BINARY_DOUBLE(1.00001);
END;
/

Rather than raise exceptions, the resulting values of computations may equate to the following constants that can be tested for.

• [BINARY_FLOAT|BINARY_DOUBLE]_NAN
• [BINARY_FLOAT|BINARY_DOUBLE]_INFINITY
• [BINARY_FLOAT|BINARY_DOUBLE]_MAX_NORMAL
• [BINARY_FLOAT|BINARY_DOUBLE]_MIN_NORMAL
• [BINARY_FLOAT|BINARY_DOUBLE]_MAX_SUBNORMAL
• [BINARY_FLOAT|BINARY_DOUBLE]_MIN_SUBNORMAL

The constants for NaN and infinity are also available in SQL.

Improved Overloading With Numeric Types

Oracle 10g includes improved overloading of numeric types like the following.

-- Create package specification.
CREATE OR REPLACE PACKAGE numeric_overload_test AS
  PROCEDURE go (p_number  NUMBER);
  PROCEDURE go (p_number  BINARY_FLOAT);
  PROCEDURE go (p_number  BINARY_DOUBLE);
END;
/

-- Create package body.
CREATE OR REPLACE PACKAGE BODY numeric_overload_test AS
  PROCEDURE go (p_number  NUMBER) AS
  BEGIN
    DBMS_OUTPUT.put_line('Using NUMBER');
  END;

  PROCEDURE go (p_number  BINARY_FLOAT) AS
  BEGIN
    DBMS_OUTPUT.put_line('Using BINARY_FLOAT');
  END;

  PROCEDURE go (p_number  BINARY_DOUBLE) AS
  BEGIN
    DBMS_OUTPUT.put_line('Using BINARY_DOUBLE');
  END;
END;
/

-- Test it.
SET SERVEROUTPUT ON
BEGIN
  numeric_overload_test.go(10);
  numeric_overload_test.go(10.1f);
  numeric_overload_test.go(10.1d);
END;
/

It is important to check that the correct overload is being used at all times. The appropriate suffix or conversion function will make the engine to pick the correct overload.

Nested Table Enhancements

Nested tables in PL/SQL now support more operations than before. Collections can be assigned directly to the value of another collection of the same type, or to the result of a set expression.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_colors IS TABLE OF VARCHAR2(10);
  l_col_1 t_colors := t_colors('Red', 'Green', 'Blue', 'Green', 'Blue');
  l_col_2 t_colors := t_colors('Red', 'Green', 'Yellow', 'Green');
  l_col_3 t_colors;

  PROCEDURE display (p_text  IN  VARCHAR2,
                     p_col   IN  t_colors) IS
  BEGIN
    DBMS_OUTPUT.put_line(CHR(10) || p_text);
    FOR i IN p_col.first .. p_col.last LOOP
      DBMS_OUTPUT.put_line(p_col(i));
    END LOOP;
  END;
BEGIN
  -- Basic assignment.
  l_col_3 := l_col_1;
  display('Direct Assignment:', l_col_3);

  -- Expression assignments.
  l_col_3 := l_col_1 MULTISET UNION l_col_2;
  display('MULTISET UNION:', l_col_3);

  l_col_3 := l_col_1 MULTISET UNION DISTINCT l_col_2;
  display('MULTISET UNION DISTINCT:', l_col_3);

  l_col_3 := l_col_1 MULTISET INTERSECT l_col_2;
  display('MULTISET INTERSECT:', l_col_3);

  l_col_3 := l_col_1 MULTISET INTERSECT DISTINCT l_col_2;
  display('MULTISET INTERSECT DISTINCT:', l_col_3);

  l_col_3 := l_col_1 MULTISET EXCEPT l_col_2;
  display('MULTISET EXCEPT:', l_col_3);

  l_col_3 := l_col_1 MULTISET EXCEPT DISTINCT l_col_2;
  display('MULTISET EXCEPT DISTINCT:', l_col_3);
END;
/

Comparisons between collections have also improved with the addition of NULL checks, equality operators and set operations.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_colors IS TABLE OF VARCHAR2(10);
  l_col_1 t_colors := t_colors('Red', 'Green', 'Blue');
  l_col_2 t_colors := t_colors('Red', 'Green', 'Green');
  l_col_3 t_colors;
BEGIN
  IF (l_col_3 IS NULL) AND (l_col_1 IS NOT NULL) THEN
    DBMS_OUTPUT.put_line(CHR(10) || '(l_col_3 IS NULL) AND (l_col_1 IS NOT NULL): TRUE');
  END IF;

  l_col_3 := l_col_1;

  IF (l_col_3 = l_col_1) AND (l_col_3 != l_col_2) THEN
    DBMS_OUTPUT.put_line(CHR(10) || '(l_col_3 = l_col_1) AND (l_col_3 != l_col_2): TRUE');
  END IF;

  IF (SET(l_col_2) SUBMULTISET l_col_1) AND (l_col_1 NOT SUBMULTISET l_col_2) THEN
    DBMS_OUTPUT.put_line(CHR(10) || '(SET(l_col_2) SUBMULTISET l_col_1) AND (l_col_1 NOT SUBMULTISET l_col_2): TRUE');
  END IF;

  DBMS_OUTPUT.put_line(CHR(10) || 'CARDINALITY(l_col_2): ' || CARDINALITY(l_col_2));

  DBMS_OUTPUT.put_line(CHR(10) || 'CARDINALITY(SET(l_col_2)): ' || CARDINALITY(SET(l_col_2)) || ' - Duplicates removed');

  IF l_col_2 IS NOT A SET THEN
    DBMS_OUTPUT.put_line(CHR(10) || 'l_col_2 IS NOT A SET: TRUE - Contains duplicates');
  END IF;

  IF l_col_3 IS NOT EMPTY THEN
    DBMS_OUTPUT.put_line(CHR(10) || 'l_col_3 IS NOT EMPTY: TRUE');
  END IF;
END;
/

The SET function removes duplicate entries from your nested table, in a similar way to the SQL DISTINCT aggregate function.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_colors IS TABLE OF VARCHAR2(10);
  l_col_1 t_colors := t_colors('Red', 'Green', 'Blue', 'Green', 'Blue');
  l_col_2 t_colors;

  PROCEDURE display (p_text  IN  VARCHAR2,
                     p_col   IN  t_colors) IS
  BEGIN
    DBMS_OUTPUT.put_line(CHR(10) || p_text);
    FOR i IN p_col.first .. p_col.last LOOP
      DBMS_OUTPUT.put_line(p_col(i));
    END LOOP;
  END;
BEGIN
  -- Basic assignment.
  l_col_2 := l_col_1;
  display('Direct Assignment:', l_col_2);

  -- SET assignments.
  l_col_2 := SET(l_col_1);
  display('MULTISET UNION:', l_col_2);
END; 
/

Compile-Time Warnings

Oracle can now produce compile-time warnings when code is ambiguous or inefficient be setting the PLSQL_WARNINGS parameter at either instance or session level. The categories ALL, SEVERE, INFORMATIONAL and PERFORMANCE can be used to alter the type of warnings that are produced.

-- Instance and session level.
ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL';
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:PERFORMANCE';

-- Recompile with extra checking.
ALTER PROCEDURE hello COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE';

-- Set mutiple values.
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE','DISABLE:PERFORMANCE','DISABLE:INFORMATIONAL';

-- Use the DBMS_WARNING package instead.
EXEC DBMS_WARNING.SET_WARNING_SETTING_STRING('ENABLE:ALL' ,'SESSION');

The current settings associated with each object can be displayed using the [USER|DBA|ALL]_PLSQL_OBJECT_SETTINGS views.

To see a typical example of the warning output run the following code.

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

CREATE OR REPLACE PROCEDURE test_warnings AS
  l_dummy  VARCHAR2(10) := '1';
BEGIN
  IF 1=1 THEN
    SELECT '2'
    INTO   l_dummy
    FROM   dual;
  ELSE
    RAISE_APPLICATION_ERROR(-20000, 'l_dummy != 1!');
  END IF;
END;
/

SP2-0804: Procedure created with compilation warnings

SHOW ERRORS

LINE/COL ERROR
-------- ---------------------------
9/5      PLW-06002: Unreachable code

The errors can be queried using the %_ERRORS views.

Quoting Mechanism for String Literals

Oracle 10g allows you to define your own string delimiters to remove the need to double up any single quotes. Any character that is not present in the string can be used as the delimiter.

SET SERVEROUTPUT ON
BEGIN
  -- Orginal syntax.
  DBMS_OUTPUT.put_line('This is Tim''s string!');

  -- New syntax.
  DBMS_OUTPUT.put_line(q'#This is Tim's string!#');
  DBMS_OUTPUT.put_line(q'[This is Tim's string!]');
END;
/

This is Tim's string!
This is Tim's string!
This is Tim's string!
 
PL/SQL procedure successfully completed.

Implicit Conversion Between CLOB and NCLOB

Oracle 10g now supports implicit conversions between CLOBs and NCLOBs and vice-versa. As with all type conversions it is still better to be explicit and use the conversion functions TO_CLOB and TO_NCLOB for clarity.

Regular Expressions

Oracle 10g supports regular expressions in SQL and PL/SQL with the following functions:

• REGEXP_INSTR - Similar to INSTR except it uses a regular expression rather than a literal as the search string.
• REGEXP_LIKE - Similar to LIKE except it uses a regular expression as the search string.
• REGEXP_REPLACE - Similar to REPLACE except it uses a regular expression as the search string.
• REGEXP_SUBSTR - Returns the string matching the regular expression. Not really similar to SUBSTR.

The following examples show how these functions can be used with a simple regular expression ('[0-9]{4} [0-9]{4} [0-9]{4} [0-9]{4}') for basic credit card validation.

SET SERVEROUTPUT ON
DECLARE
  l_text           VARCHAR2(100) := 'My credit card details are: 1234 1234 1234 1234';
  l_regular_expr   VARCHAR2(50)  := '[0-9]{4} [0-9]{4} [0-9]{4} [0-9]{4}';

  l_credit_card_1  VARCHAR2(50)  := '1234 1234 1234 1234';
  l_credit_card_2  VARCHAR2(50)  := '123c 1234 1234 1234';
BEGIN
  -- REGEXP_INSTR
  IF REGEXP_INSTR(l_text, l_regular_expr) > 0 THEN
    DBMS_OUTPUT.put_line('REGEXP_INSTR: Your input contains credit card details, this is a security risk!');
  END IF;

  -- REGEXP_LIKE
  IF REGEXP_LIKE(l_credit_card_1, l_regular_expr) THEN
    DBMS_OUTPUT.put_line('REGEXP_LIKE: Good Credit Card: ' || l_credit_card_1);
  END IF;
  IF NOT REGEXP_LIKE(l_credit_card_2, l_regular_expr) THEN
    DBMS_OUTPUT.put_line('REGEXP_LIKE: Bad Credit Card : ' || l_credit_card_2);
  END IF;

  -- REGEXP_REPLACE
  DBMS_OUTPUT.put_line('REGEXP_REPLACE: Before: ' || l_text);
  DBMS_OUTPUT.put_line('REGEXP_REPLACE: After : ' || REGEXP_REPLACE(l_text, l_regular_expr, '**** **** **** ****'));

  -- REGEXP_SUBSTR
  DBMS_OUTPUT.put_line('REGEXP_SUBSTR: Matching String : ' || REGEXP_SUBSTR(l_text, l_regular_expr));
END;
/

Building regular expressions to match your requirements can get a little confusing and this is beyond the scope of this article.

Flashback Query Functions

The TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions have been added to SQL and PL/SQL to simplify flashback operations.

SELECT *
FROM   emp AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);

SELECT *
FROM   emp AS OF TIMESTAMP SCN_TO_TIMESTAMP(993240);

DECLARE
  l_scn        NUMBER;
  l_timestamp  TIMESTAMP;
BEGIN
  l_scn       := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
  l_timestamp := SCN_TO_TIMESTAMP(l_scn);
END;
/

UTL_COMPRESS

The UTL_COMPRESS package provides an API to allow compression and decompression of binary data (RAW, BLOB and BFILE). It uses the Lempel-Ziv compression algorithm which is equivalent to functionality of the gzip utility. A simple example of it's use is shown below.

SET SERVEROUTPUT ON
DECLARE
  l_original_blob      BLOB;
  l_compressed_blob    BLOB;
  l_uncompressed_blob  BLOB;
BEGIN
  -- Initialize both BLOBs to something.
  l_original_blob     := TO_BLOB(UTL_RAW.CAST_TO_RAW('1234567890123456789012345678901234567890'));
  l_compressed_blob   := TO_BLOB('1');
  l_uncompressed_blob := TO_BLOB('1');
  
  -- Compress the data.
  UTL_COMPRESS.lz_compress (src => l_original_blob,
                            dst => l_compressed_blob);
  
  -- Uncompress the data.
  UTL_COMPRESS.lz_uncompress (src => l_compressed_blob,
                              dst => l_uncompressed_blob);
  
  -- Display lengths.
  DBMS_OUTPUT.put_line('Original Length    : ' || LENGTH(l_original_blob));
  DBMS_OUTPUT.put_line('Compressed Length  : ' || LENGTH(l_compressed_blob));
  DBMS_OUTPUT.put_line('Uncompressed Length: ' || LENGTH(l_uncompressed_blob));
  
  -- Free temporary BLOBs.             
  DBMS_LOB.FREETEMPORARY(l_original_blob);
  DBMS_LOB.FREETEMPORARY(l_compressed_blob);
  DBMS_LOB.FREETEMPORARY(l_uncompressed_blob);
END;
/

UTL_MAIL

The UTL_MAIL package provides a simple API to allow email to be sent from PL/SQL. In prior versions this was possible using the UTL_SMTP package (shown here), but this required knowledge of the SMTP protocol.

The package is loaded by running the following scripts.

CONN sys/password AS SYSDBA
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

In addition the SMTP_OUT_SERVER parameter must be set to identify the SMTP server.

CONN sys/password AS SYSDBA
ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

With the configuration complete we can now send a mail.

BEGIN
  UTL_MAIL.send(sender     => 'me@domain.com',
                recipients => 'person1@domain.com,person2@domain.com',
                cc         => 'person3@domain.com',
                bcc        => 'myboss@domain.com',
                subject    => 'UTL_MAIL Test',
                message    => 'If you get this message it worked!');
END;
/

The package also supports sending mails with RAW and VARCHAR2 attachments.