Introduction Introduction PL/SQL New Features in Oracle 11g Oracle 11g has introduced a number of new features and improvements for PL/SQL. This introduction briefly describes features not covered in this book and points you to specific chapters for features that are within scope of this book. The list of features described here is also available in the "What''s New in PL/SQL?" section of the PL/SQL Language Reference manual offered as part of Oracle help available online. The new PL/SQL features and enhancements are as follows: Enhancements to regular expression built-in SQL functions SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE datatypes CONTINUE statement Sequences in PL/SQL expressions Dynamic SQL enhancements Named and mixed notation in PL/SQL subprogram invocations Cross-session PL/SQL function result cache More control over triggers Compound triggers Database resident connection pool Automatic subprogram inlining PL/Scope PL/SQL hierarchical profiler PL/SQL native compiler generates native code directly Enhancements to Regular Expression Built-In SQL Functions In this release Oracle has introduced a new regular expression built-in function, REGEXP_COUNT. It returns the number of times a specified search pattern appears in a source string. For Example SELECT REGEXP_COUNT (''Oracle PL/SQL By Example Updated for Oracle 11g'', ''ora'', 1, ''i'') FROM dual; REGEXP_COUNT(''ORACLEPL/SQLBYEXAMPLEUPDATEDFORORACLE11G'',''ORA'',1,''I'') -------------------------------------------------------------------- 2 The REGEXP_COUNT function returns how many times the search pattern ''ora'' appears in the source string ''Oracle PL/SQL.'' 1 indicates the position of the source string where the search begins, and ''i'' indicates case-insensitive matching. The existing regular expression built-in functions, REGEXP_INSTR and REGEXP_SUBSTR, have a new parameter called SUBEXPR.

This parameter represents a subexpression in a search pattern. Essentially it is a portion of a search pattern enclosed in parentheses that restricts pattern matching, as illustrated in the following example. For Example SELECT REGEXP_INSTR (''Oracle PL/SQL By Example Updated for Oracle 11g'', ''((ora)(cle))'', 1, 2, 0, ''i'') FROM dual; REGEXP_INSTR(''ORACLEPL/SQLBYEXAMPLEUPDATEDFORORACLE11G'',.) ------------------------------------------------------------ 38 The REGEXP_INSTR function returns the position of the first character in the source string ''Oracle PL/SQL.'' corresponding to the second occurrence of the first subexpression ''ora'' in the seach pattern (ora)(cle). 1 indicates the position of the source string where the search begins, 2 indicates the occurrence of the subexpression in the source string, 0 indicates that the position returned corresponds to the position of the first character where the match occurs, and ''i'' indicates case-insensitive matching and REGEXP_SUBSTR. SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Datatypes These datatypes are predefined subtypes of the PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively. As such, they have the same range as their respective base types.

In addition, these subtypes have NOT NULL constraints. These subtypes provide significant performance improvements over their respective base types when the PLSQL_CODE_TYPE parameter is set to NATIVE. This is because arithmetic operations for these subtypes are done directly in the hardware layer. Note that when PLSQL_CODE_TYPE is set to INTERPRETED (the default value), the performance gains are significantly smaller. This is illustrated by the following example. For Example SET SERVEROUTPUT ON DECLARE v_pls_value1 PLS_INTEGER := 0; v_pls_value2 PLS_INTEGER := 1; v_simple_value1 SIMPLE_INTEGER := 0; v_simple_value2 SIMPLE_INTEGER := 1; -- Following are used for elapsed time calculation -- The time is calculated in 100th of a second v_start_time NUMBER; v_end_time NUMBER; BEGIN -- Perform calculations with PLS_INTEGER v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1.50000000 LOOP v_pls_value1 := v_pls_value1 + v_pls_value2; END LOOP; v_end_time := DBMS_UTILITY.

GET_TIME; DBMS_OUTPUT.PUT_LINE (''Elapsed time for PLS_INTEGER: '' (v_end_time - v_start_time)); -- Perform the same calculations with SIMPLE_INTEGER v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1.50000000 LOOP v_simple_value1 := v_simple_value1 + v_simple_value2; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE (''Elapsed time for SIMPLE_INTEGER: '' (v_end_time - v_start_time)); END; This script compares the performance of the PLS_INTEGER datatype with its subtype SIMPLE_INTEGER via a numeric FOR loop. Note that for this run the PLSQL_CODE_TYPE parameter is set to its default value, INTERPRETED. Elapsed time for PLS_INTEGER: 147 Elapsed time for SIMPLE_INTEGER: 115 PL/SQL procedure successfully completed.

CONTINUE Statement Similar to the EXIT statement, the CONTINUE statement controls loop iteration. Whereas the EXIT statement causes a loop to terminate and passes control of the execution outside the loop, the CONTINUE statement causes a loop to terminate its current iteration and passes control to the next iteration of the loop. The CONTINUE statement is covered in detail in Chapter 7, "Iterative Control--Part 2." Sequences in PL/SQL Expressions Prior to Oracle 11g, the sequence pseudocolumns CURRVAL and NEXTVAL could be accessed in PL/SQL only through queries. Starting with Oracle 11g, these pseudocolumns can be accessed via expressions. This change not only improves PL/SQL source code, it also improves runtime performance and scalability. For Example CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1; Sequence created. SET SERVEROUTPUT ON DECLARE v_seq_value NUMBER; BEGIN v_seq_value := test_seq.

NEXTVAL; DBMS_OUTPUT.PUT_LINE (''v_seq_value: ''v_seq_value); END; This script causes an error when executed in Oracle 10g: v_seq_value := test_seq.NEXTVAL; * ERROR at line 4: ORA-06550: line 4, column 28: PLS-00357: Table,View Or Sequence reference ''TEST_SEQ.NEXTVAL'' not allowed in this context ORA-06550: line 4, column 4: PL/SQL: Statement ignored and it completes successfully when executed in Oracle 11g: v_seq_value: 1 PL/SQL procedure successfully completed. Consider another example that illustrates performance improvement when the PL/SQL expression is used to manipulate sequences: For Example SET SERVEROUTPUT ON DECLARE v_seq_value NUMBER; -- Following are used for elapsed time calculation v_start_time NUMBER; v_end_time NUMBER; BEGIN -- Retrieve sequence via SELECT INTO statement v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1.10000 LOOP SELECT test_seq.NEXTVAL INTO v_seq_value FROM dual; END LOOP; v_end_time := DBMS_UTILITY.

GET_TIME; DBMS_OUTPUT.PUT_LINE (''Elapsed time to retrieve sequence via SELECT INTO: '' (v_end_time-v_start_time)); -- Retrieve sequence via PL/SQL expression v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1.10000 LOOP v_seq_value := test_seq.NEXTVAL; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE (''Elapsed time to retrieve sequence via PL/SQL expression: '' (v_end_time-v_start_time)); END; Elapsed time to retrieve sequence via SELECT INTO: 52 Elapsed time to retrieve sequence via PL/SQL expression: 43 PL/SQL procedure successfully completed. Dynamic SQL Enhancements In this version, Oracle has introduced a number of enhancements to the native dynamic SQL and DBMS_SQL package.

Native dynamic SQL enables you to generate dynamic SQL statements larger than 32K. In other words, it supports the CLOB datatype. Native dynamic SQL is covered in detail in Chapter 17, "Native Dynamic SQL." The DBMS_SQL package now supports all datatypes that native dynamic SQL supports. This includes the CLOB datatype. In addition, two new functions, DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER, enable you to switch between the native dynamic SQL and DBMS_SQL package.

Named and Mixed Notation in PL/SQL Subprogram Invocations Prior to Oracle 11g, a SQL statement invoking a function had to specify the parameters in positional notation. In this release, mixed and named notations are allowed as well. Examples of positional, named, and mixed notations can be found in Chapter 21, "Packages," and Chapter 23, "Object Types in Oracle." Consider the following example: For Example CREATE OR REPLACE FUNCTION test_function (in_val1 IN NUMBER, in_val2 IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN (in_val1'' - ''in_val2); END; Function created. SELECT test_function(1, ''Positional Notation'') col1, test_function(in_val1 => 2, in_val2 => ''Named Notation'') col2, test_function(3, in_val2 => ''Mixed Notation'') col3 FROM dual; COL1 COL2 COL3 ----------------------- ------------------ ------------------ 1 - Positional Notation 2 - Named Notation 3 - Mixed Notation Note that mixed notation has a restriction: positional notation may not follow named notation. This is illustrated by the following SELECT: SELECT test_function(1, ''Positional Notation'') col1, test_function(in_val1 => 2, in_val2 => ''Named Notation'') col2, test_function(in_val1 => 3, ''Mixed Notation'') col3 FROM dual; test_function(in_val1 => 3, ''Mixed Notation'') col3 * ERROR at line 4: ORA-06553: PLS-312: a positional parameter association may not follow a named association Cross-Session PL/SQL Function Result Cache A result-cached function is a function whose parameter values and result are stored in the cache. This means that when such a function is invoked with the sam.