PL/SQL New Features and Enhancements in Oracle Database 11g

Oracle 11g includes a substantial number of PL/SQL new features and enhancements. In order to prevent this article becoming too large some of these features have been split off into separate articles, but the following sections represent an explanation of the all the new features listed in the What's New in PL/SQL? section of the PL/SQL Language Reference manual.

Topics covered in this article:

• Enhancements to Regular Expression Built-in SQL Functions
• SIMPLE_INTEGER Datatype
• CONTINUE Statement
• Sequences in PL/SQL Expressions
• Dynamic SQL Enhancements
• Generalized Invocation
• Named and Mixed Notation in PL/SQL Subprogram Invocations
• Automatic Subprogram Inlining
• PL/Scope
• PL/SQL Native Compiler Generates Native Code Directly
• New PL/SQL Compiler Warning
• PLS-00436 Restriction in FORALL Statements Removed

Enhancements to Regular Expression Built-in SQL Functions

The REGEXP_INSTR and REGEXP_SUBSTR functions include a new SUBEXPR parameter that limits the pattern match to a specific subexpression in the search pattern.

SQL> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 1) FROM dual;

REGEXP_INSTR('1234567890','(123)(4(56)(78))',1,1,0,'I',1)
---------------------------------------------------------
                                                        1

1 row selected.

SQL> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3) FROM dual;

REGEXP_INSTR('1234567890','(123)(4(56)(78))',1,1,0,'I',3)
---------------------------------------------------------
                                                        5

1 row selected.

SQL> SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 0) FROM dual;

REGEXP_S
--------
12345678

1 row selected.

SQL> SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1) FROM dual;

REG
---
123

1 row selected.

SQL> SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 3) FROM dual;

RE
--
56

1 row selected.

SQL>
The new REGEXP_COUNT function returns the number of times the search pattern appears in source string.

SQL> SELECT REGEXP_COUNT('123 123 123 123', '123', 1, 'i') FROM dual;

REGEXP_COUNT('123123123123','123',1,'I')
----------------------------------------
                                       4

1 row selected.

SQL> SELECT REGEXP_COUNT('123 123 123 123', '123', 9, 'i') FROM dual;

REGEXP_COUNT('123123123123','123',9,'I')
----------------------------------------
                                       2

1 row selected.

SQL>  

SIMPLE_INTEGER Datatype

The SIMPLE_INTEGER datatype is a subtype of the PLS_INTEGER datatype and can dramatically increase the speed of integer arithmetic in natively compiled code, but only shows marginal performance improvements in interpreted code. The following procedure compares the performance of the SIMPLE_INTEGER and PLS_INTEGER datatypes.

CREATE OR REPLACE PROCEDURE simple_integer_test_proc AS
  l_start               NUMBER;
  l_loops               NUMBER := 10000000;
  l_pls_integer         PLS_INTEGER := 0;
  l_pls_integer_incr    PLS_INTEGER := 1;
  l_simple_integer      SIMPLE_INTEGER := 0;
  l_simple_integer_incr SIMPLE_INTEGER := 1;
BEGIN

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_pls_integer := l_pls_integer + l_pls_integer_incr;
  END LOOP;

  DBMS_OUTPUT.put_line('PLS_INTEGER: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_simple_integer := l_simple_integer + l_simple_integer_incr;
  END LOOP;

  DBMS_OUTPUT.put_line('SIMPLE_INTEGER: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');


END simple_integer_test_proc;
/

When run in the default interpreted mode the performance improvement of the SIMPLE_INTEGER datatype is not spectacular.

SQL> SET SERVEROUTPUT ON
SQL> EXEC simple_integer_test_proc;
PLS_INTEGER: 47 hsecs
SIMPLE_INTEGER: 44 hsecs

PL/SQL procedure successfully completed.

SQL>

We natively compile the procedure by altering the PLSQL_CODE_TYPE value for the session and recompiling the procedure.

ALTER SESSION SET PLSQL_CODE_TYPE=NATIVE;
ALTER PROCEDURE simple_integer_test_proc COMPILE;

Natively compiling the procedure produces dramatic speed improvements for both datatypes, but more so for the SIMPLE_INTEGER datatype.

SQL> SET SERVEROUTPUT ON
SQL> EXEC simple_integer_test_proc;
PLS_INTEGER: 10 hsecs
SIMPLE_INTEGER: 2 hsecs

PL/SQL procedure successfully completed.

SQL>

The speed improvements are a result of two fundamental differences between the two datatypes. First, SIMPLE_INTEGER and PLS_INTEGER have the same range (-2,147,483,648 through 2,147,483,647), but SIMPLE_INTEGER wraps round when it exceeds its bounds, rather than throwing an error like PLS_INTEGER.

SET SERVEROUTPUT ON
DECLARE
  l_simple_integer SIMPLE_INTEGER := 2147483645;
BEGIN
  FOR i IN 1 .. 4 LOOP
    l_simple_integer := l_simple_integer + 1;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_simple_integer, 'S9999999999'));
  END LOOP;

  FOR i IN 1 .. 4 LOOP
    l_simple_integer := l_simple_integer - 1;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_simple_integer, 'S9999999999'));
  END LOOP;
END;
/
+2147483646
+2147483647
-2147483648
-2147483647
-2147483648
+2147483647
+2147483646
+2147483645

PL/SQL procedure successfully completed.

SQL>

Second, SIMPLE_INTEGER can never have a NULL value, either when it is declared, or by assignment.

DECLARE
  l_simple_integer SIMPLE_INTEGER;
BEGIN
  NULL;
END;
/
                   *
ERROR at line 2:
ORA-06550: line 2, column 20:
PLS-00218: a variable declared NOT NULL must have an initialization assignment

SQL>

DECLARE
  l_simple_integer SIMPLE_INTEGER := 0;
BEGIN
  l_simple_integer := NULL;
END;
/
                      *
ERROR at line 4:
ORA-06550: line 4, column 23:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

SQL>

The removal of overflow and NULL checking result in a significant reduction in overhead compared to PLS_INTEGER.