8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

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

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
Sequences in PL/SQL Expressions
Dynamic SQL Enhancements
Generalized Invocation
Named and Mixed Notation in PL/SQL Subprogram Invocations
PL/SQL Native Compiler Generates Native Code Directly
New PL/SQL Compiler Warning
PLS-00436 Restriction in FORALL Statements Removed

Topics covered in separate articles on this site:

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 processing result in a significant reduction in overhead compared to PLS_INTEGER.

Sequences in PL/SQL Expressions

The NEXTVAL and CURRVAL sequence pseudocolumns can now be accessed in PL/SQL expressions as well as queries. This makes the code look simpler, and the documentation suggests it improves performance. The following example compares the speed of the original and new methods of accessing these sequence values.

CREATE SEQUENCE test1_seq START WITH 1000000;

SET SERVEROUTPUT ON
DECLARE
  l_start NUMBER;
  l_loops NUMBER := 100000;
  l_value NUMBER;
BEGIN

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    SELECT test1_seq.NEXTVAL
    INTO   l_value
    FROM dual;
  END LOOP;

  DBMS_OUTPUT.put_line('NEXTVAL SELECT=' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_value := test1_seq.NEXTVAL;
  END LOOP;

  DBMS_OUTPUT.put_line('NEXTVAL Expression=' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    SELECT test1_seq.CURRVAL
    INTO   l_value
    FROM dual;
  END LOOP;

  DBMS_OUTPUT.put_line('CURRVAL SELECT=' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_value := test1_seq.CURRVAL;
  END LOOP;

  DBMS_OUTPUT.put_line('CURRVAL Expression=' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

END;
/
NEXTVAL SELECT=2196 hsecs
NEXTVAL Expression=2203 hsecs
CURRVAL SELECT=1007 hsecs
CURRVAL Expression=1003 hsecs

PL/SQL procedure successfully completed.

SQL>

You can see that as far as elapsed time is concerned, there is little difference between the two methods. If you trace the PL/SQL code, you can see the direct use of an expression still causes a recursive query against dual, so they have just hidden the query from you, which is why there is no performance improvement.

Dynamic SQL Enhancements

Native dynamic SQL and the DBMS_SQL package now support dynamic SQL statements larger than 32 KB. The EXECUTE IMMEDIATE statement, OPEN-FOR statement and DBMS_SQL.PARSE procedure all accept SQL statements in the form of CLOBs.

The DBMS_SQL.TO_REFCURSOR function converts a DBMS_SQL cursor ID into a REF CURSOR.

SET SERVEROUTPUT ON
DECLARE
  l_cursor       NUMBER;
  l_return       NUMBER;

  l_ref_cursor   SYS_REFCURSOR;
  TYPE t_emp_tab IS TABLE OF emp%ROWTYPE;
  l_emp_tab      t_emp_tab;
BEGIN
  l_cursor := DBMS_SQL.open_cursor;

  DBMS_SQL.parse(l_cursor, 'SELECT * FROM emp', DBMS_SQL.NATIVE);

  l_return := DBMS_SQL.EXECUTE(l_cursor);

  -- Connvert from DBMS_SQL to a REF CURSOR.
  l_ref_cursor := DBMS_SQL.to_refcursor(l_cursor);
  FETCH l_ref_cursor BULK COLLECT INTO l_emp_tab;

  DBMS_OUTPUT.put_line('Employee Count: ' || l_emp_tab.count);

  CLOSE l_ref_cursor;
END;
/
Employee Count: 14

PL/SQL procedure successfully completed.

SQL>

The DBMS_SQL.TO_CURSOR_NUMBER function converts a REF CURSOR into a DBMS_SQL cursor ID.

SET SERVEROUTPUT ON
DECLARE
  l_ref_cursor   SYS_REFCURSOR;

  l_cursor       NUMBER;
  l_count        NUMBER := 0;
BEGIN
  OPEN l_ref_cursor FOR 'SELECT * FROM emp';

  l_cursor := DBMS_SQL.to_cursor_number(l_ref_cursor);

  WHILE DBMS_SQL.fetch_rows(l_cursor) > 0 LOOP
    l_count := l_count + 1;
  END LOOP;

  DBMS_OUTPUT.put_line('Employee Count: ' || l_count);

  DBMS_SQL.close_cursor(l_cursor);
END;
/
Employee Count: 14

PL/SQL procedure successfully completed.

SQL>

In addition, the DBMS_SQL package now supports all datatypes supported by native dynamic SQL.

Generalized Invocation

Generalized invocation allows a subtype to invoke a method of a parent type (supertype) using the following syntax.

(SELF AS supertype_name).method_name

The following example shows this in action.

First, we create a type with some attributes and a member function.

CREATE OR REPLACE TYPE my_type AS OBJECT (
  id          NUMBER,
  description VARCHAR2(50),
  MEMBER FUNCTION show_attributes RETURN VARCHAR2)
  NOT FINAL;
/

CREATE OR REPLACE TYPE BODY my_type AS

  MEMBER FUNCTION show_attributes RETURN VARCHAR2 IS
  BEGIN
    RETURN 'id=' || id || '  description=' || description;
  END;

END;
/

Next, we create a subtype of this object, which adds a new attribute and method as well as overriding the show_attributes member function.

CREATE OR REPLACE TYPE my_subtype UNDER my_type (
  short_desc VARCHAR2(10),
  OVERRIDING MEMBER FUNCTION show_attributes RETURN VARCHAR2,
  MEMBER FUNCTION show_parent_attributes RETURN VARCHAR2);
/

CREATE OR REPLACE TYPE BODY my_subtype AS

  OVERRIDING MEMBER FUNCTION show_attributes RETURN VARCHAR2 IS
  BEGIN
    RETURN (self AS my_type).show_attributes || '  short_desc=' || short_desc;
  END;

  MEMBER FUNCTION show_parent_attributes RETURN VARCHAR2 IS
  BEGIN
    RETURN (self AS my_type).show_attributes;
  END;

END;
/

Notice the method of the parent type is accessed using the generalized invocation syntax. The code below creates a subtype and invokes its member functions.

SET SERVEROUTPUT ON
DECLARE
  l_subtype my_subtype := my_subtype(1, 'Long description for 1', 'S Desc 1');
BEGIN
  DBMS_OUTPUT.put_line('show_attributes=' || l_subtype.show_attributes);
  DBMS_OUTPUT.put_line('show_parent_attributes=' || l_subtype.show_parent_attributes);
END;
/
show_attributes=id=1  description=Long description for 1  short_desc=S Desc 1
show_parent_attributes=id=1  description=Long description for 1

PL/SQL procedure successfully completed.

SQL>

A type can invoke the member functions of any parent type in this way, regardless of the depth of the inheritance.

Named and Mixed Notation in PL/SQL Subprogram Invocations

Prior to 11g, PL/SQL invoked from SQL had to have its parameters passed using positional notation, making it difficult to determine the meaning of parameters. Oracle 11g allows positional, named and mixed notation to be used when calling PL/SQL from SQL, as shown below.

-- Build a test function with multiple parameters.
CREATE OR REPLACE FUNCTION test_func(p_value_1  IN  NUMBER DEFAULT 0,
                                     p_value_2  IN  NUMBER DEFAULT 0)
  RETURN NUMBER AS
BEGIN
  RETURN p_value_1 + p_value_2;
END test_func;
/

Function created.

SQL> -- Positional Notation.
SQL> SELECT test_func(10, 20) FROM dual;

TEST_FUNC(10,20)
----------------
              30

1 row selected.

SQL> -- Mixed Notation.
SQL> SELECT test_func(10, p_value_2 => 20) FROM dual;

TEST_FUNC(10,P_VALUE_2=>20)
---------------------------
                         30

1 row selected.

SQL> -- Named Notation.
SQL> SELECT test_func(p_value_1 => 10, p_value_2 => 20) FROM dual;

TEST_FUNC(P_VALUE_1=>10,P_VALUE_2=>20)
--------------------------------------
                                    30

1 row selected.

SQL>

PL/SQL Native Compiler Generates Native Code Directly

Native compilation of PL/SQL code as been possible since Oracle 9i. See:

ALTER SESSION SET PLSQL_CODE_TYPE='NATIVE';
ALTER PROCEDURE test_speed COMPILE;

In these versions of the database, the PL/SQL code was converted to C code, compiled and run as external procedures. Using natively compiled PL/SQL needed a C compiler on the server and intervention from the DBA. In addition, using natively compiled PL/SQL in a RAC environment could prove problematic.

In Oracle 11g, PL/SQL native compilation requires no C compiler, no DBA intervention and is fully supported in a RAC environment. By setting the PLSQL_CODE_TYPE to a value of NATIVE, rather than the default value of INTERPRETED, code is compiled directly to machine code and stored in the SYSTEM tablespace. When the code is called, it is loaded into shared memory, making it accessible for all sessions in that instance. The %_PLSQL_OBJECT_SETTINGS views include the current PLSQL_CODE_TYPE setting for each PL/SQL object.

Remember, native compilation will improve the speed of procedure code, but has no effect on the performance of SQL. When code performs lots of mathematical operations, like the SIMPLE_INTEGER example, native compilation can produce considerable performance improvements. If code is predominantly performing SQL, little improvement will be noticed.

As with previous database versions, it is possible to natively compile all PL/SQL code in the database, provided you follow the supported procedure.

ALTER PACKAGE your_package_name COMPILE;

New PL/SQL Compiler Warning

A new PL/SQL compiler warning has been added to identify WHEN OTHERS exception handlers that do no re-raise errors using RAISE or RAISE_APPLICATION_ERROR. Such exception handlers can often hide code failures that result in hard to identify bugs. The example below shows the expected compiler warning when the PLSQL_WARNINGS flag is set.

SQL> ALTER SESSION SET plsql_warnings = 'enable:all';

Session altered.

SQL> CREATE OR REPLACE PROCEDURE others_test AS
  2  BEGIN
  3    RAISE_APPLICATION_ERROR(-20000, 'Force and exception');
  4  EXCEPTION
  5    WHEN OTHERS THEN
  6      NULL;
  7  END;
  8  /

SP2-0804: Procedure created with compilation warnings

SQL> SHOW ERRORS
Errors for PROCEDURE OTHERS_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/8      PLW-06009: procedure "OTHERS_TEST" OTHERS handler does not end in
         RAISE or RAISE_APPLICATION_ERROR

SQL>

This is only a warning message, so it only identifies possible problem code, it doesn't prevent it.

PLS-00436 Restriction in FORALL Statements Removed

The PLS-00436 restriction has been removed, which means you can now reference the individual elements of a collection within the SET and WHERE clauses of a DML statement in a FORALL construct. To see this in action, create and populates a test table using the following code.

CREATE TABLE forall_test (
  id          NUMBER,
  description VARCHAR2(50)
);

INSERT INTO forall_test VALUES (1, 'ONE');
INSERT INTO forall_test VALUES (2, 'TWO');
INSERT INTO forall_test VALUES (3, 'THREE');
INSERT INTO forall_test VALUES (4, 'FOUR');
INSERT INTO forall_test VALUES (5, 'FIVE');
COMMIT;

The PL/SQL block below populates a collection with the existing data, amends the data in the collection, then updates the table with the amended data. The final query displays the changed data in the table.

DECLARE
  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
  l_tab t_forall_test_tab;
BEGIN
  -- Retrieve the existing data into a collection.
  SELECT *
  BULK COLLECT INTO l_tab
  FROM   forall_test;

  -- Alter the data in the collection.
  FOR i IN l_tab.first .. l_tab.last LOOP
    l_tab(i).description := 'Description for ' || i;
  END LOOP;

  -- Update the table using the collection.
  FORALL i IN l_tab.first .. l_tab.last
    UPDATE forall_test
    SET    description = l_tab(i).description
    WHERE  id          = l_tab(i).id;

  COMMIT;
END;
/

SELECT * FROM forall_test;

        ID DESCRIPTION
---------- ---------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5

5 rows selected.

SQL>

Notice both the SET and WHERE clauses contain references to individual columns in the collection. This makes using bulk-binds for DML even easier as we no longer need to maintain multiple collections if we need to reference columns in the WHERE clause. It can also improve performance of updates, as previous versions required updates of the whole row using the ROW keyword, which included potentially unnecessary updates of primary key and foreign key columns.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.