Enhancements for PL/SQL Developers in Oracle 11g

1. 11g New Features for PL/SQL Developers Tim Hall Oracle ACE Director Oracle ACE of the Year 2006OCP DBA (7, 8, 8i, 9i, 10g, 11g) OCA PL/SQL Developer http://www.oracle-base.com Oracle PL/SQL Tuning (Rampant) Oracle Job Scheduling (Rampant) http://www.oracle-base.com

2. http://www.oracle-base.com

3. Named and Mixed Notation in SQL • Prior to 11g, PL/SQL invoked from SQL had to have its parameters passed using positional notation • This made 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. -- Positional Notation. SELECT test_func(10, 20) FROM dual; -- Mixed Notation. SELECT test_func(10, p_value_2 => 20) FROM dual; -- Named Notation. SELECT test_func(p_value_1 => 10, p_value_2 => 20) FROM dual; http://www.oracle-base.com

4. 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. SELECT test1_seq.NEXTVAL INTO l_value FROM dual; l_value := test1_seq.NEXTVAL; SELECT test1_seq.CURRVAL INTO l_value FROM dual; l_value := test1_seq.CURRVAL; http://www.oracle-base.com

5. SIMPLE_INTEGER • SIMPLE_INTEGER is a new subtype of PLS_INTEGER. • It shows only marginal performance improvements in interpreted code, but shows considerable improvements in natively compiled code. • Demo • Improved performance over PLS_INTEGER because: • It wraps round (-2,147,483,648 to 2,147,483,647), rather than doing a bounds check. • It cannot be declared as or set to NULL. http://www.oracle-base.com

6. CONTINUE statement • The CONTINUE statement jumps out of the current loop interation and starts the next one. • It can be used on its own, or as part of a CONTINUE WHEN statement, as shown below. FOR i IN 1 .. 100 LOOP IF MOD(i,2) = 0 THEN CONTINUE; END IF; -- Do something here! END LOOP; FOR i IN 1 .. 100 LOOP CONTINUE WHEN MOD(i,2) = 0; -- Do something here! END LOOP; http://www.oracle-base.com

7. 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. • DBMS_SQL.TO_REFCURSOR function converts a DBMS_SQL cursor ID into a REF CURSOR. • DBMS_SQL.TO_CURSOR_NUMBER function converts a REF CURSOR into a DBMS_SQL cursor ID. • The DBMS_SQL package now supports all datatypes supported by native dynamic SQL. http://www.oracle-base.com

8. Generalized Invocation • Generalized invocation allows a subtype to invoke a method of a parent type (supertype) using:(SELF AS supertype_name).method_name • A type can invoke the member functions of any parent type in this way, regardless of the depth of the inheritance. CREATE OR REPLACE TYPE child_type UNDER parent_type ( short_desc VARCHAR2(10), OVERRIDING MEMBER FUNCTION show_attributes RETURN VARCHAR2); / CREATE OR REPLACE TYPE BODY child_type AS OVERRIDING MEMBER FUNCTION show_attributes RETURN VARCHAR2 IS BEGIN RETURN (self AS parent_type).show_attributes || ' short_desc=' || short_desc; END; END; / http://www.oracle-base.com

9. Automatic Subprogram Inlining • Every call to a procedure or function causes a slight performance overhead, which is especially noticeable when the subprogram is called within a loop. • Automatic subprogram inlining replaces procedure calls with a copy of the code at compile time. • PLSQL_OPTIMIZE_LEVEL=2 : “PRAGMA INLINE (func, 'YES')” is used to indicate code to be inlined or not. • PLSQL_OPTIMIZE_LEVEL=3 : “PRAGMA INLINE (func, ‘NO')” indicates that code should not be inlined. • Demo • The compiler inlines subprograms early in the optimization process, which may preventing later, more powerful optimizations taking place. FOR i IN 1 .. l_loops LOOP PRAGMA INLINE (add_numbers, 'YES'); l_return := add_numbers(l_num, i); END LOOP; http://www.oracle-base.com

10. PL/Scope • PL/Scope is a tool that gathers information about user defined identifiers at compile time and stores it in the SYSAUX tablespace. • Controlled by the PLSCOPE_SETTINGS parameter. Default "IDENTIFIERS:NONE". Enable collection "IDENTIFIERS:ALL“. • The PL/Scope data is available from the %_IDENTIFIERS views. ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL'; ALTER PROCEDURE test_plscope COMPILE; NAME TYPE USAGE ------------------ ----------- ----------- TEST_PLSCOPE PROCEDURE DECLARATION TEST_PLSCOPE PROCEDURE DEFINITION P_IN FORMAL IN DECLARATION L_VAR VARIABLE DECLARATION L_VAR VARIABLE ASSIGNMENT P_IN FORMAL IN REFERENCE L_VAR VARIABLE ASSIGNMENT L_VAR VARIABLE REFERENCE http://www.oracle-base.com

11. PL/SQL Hierarchical Profiler • The hierarchical profiler includes the DBMS_HPROF package and the “plshprof” command line utility. • The DBMS_HPROF package needs a directory object with read/write privilege and optionally access to some profiler tables (?/rdbms/admin/dbmshptab.sql). • The DBMS_HPROF package profiles and analyzes the data. • The analysis can be queried from the DBMSHP_% tables. • The “plushprof utility” converts the raw data to several HTML reports. BEGIN DBMS_HPROF.start_profiling ('PROFILER_DIR', 'profiler.txt'); procedure_1(p_times => 10); DBMS_HPROF.stop_profiling; END; / NAME FUNCTION FUNCTION_ELAPSED_TIME CALLS ------------------------------ ------------------------- --------------------- ---------- TEST.PROCEDURE_2 PROCEDURE_2 510 10 TEST.PROCEDURE_3 PROCEDURE_3 17681 100 TEST.PROCEDURE_3 __static_sql_exec_line5 83080 1000 http://www.oracle-base.com

12. PL/SQL Native Compiler Enhancements • In previous version, PL/SQL was converted to C code, then compiled, which required a C compiler. • Oracle 11g compiles PL/SQL directly to native code with no need for an additional C compiler. • Setting the PLSQL_CODE_TYPE to NATIVE, rather than the default of INTERPRETED, code is compiled directly to machine code and stored in the SYSTEM tablespace. • Once called, the code 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. ALTER SESSION SET PLSQL_CODE_TYPE=NATIVE; ALTER SESSION SET PLSQL_CODE_TYPE=INTERPRETED; http://www.oracle-base.com

13. 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. ALTER SESSION SET plsql_warnings = 'enable:all'; ALTER PROCEDURE others_test COMPILE; SHOW ERRORS SP2-0804: Procedure created with compilation warnings 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 http://www.oracle-base.com

14. PL/SQL Function Result Cache • The cross-session PL/SQL function result cache boosts the performance of PL/SQL functions by caching results in the SGA. • The cache is accessible to any session calling the same function with the same parameters. • This results in significant performance improvements when functions are called for each row in an SQL query, or within a loop in PL/SQL. • The optional RELIES_ON clause is used to specify dependent tables and views so the result cache can be invalidated if the dependent objects are modified. • Demo • The cache is managed using the RESULT_CACHE_% parameters and the DBMS_RESULT_CACHE package. • Information about the cache is displayed using the V$RESULT_CACHE_% views. CREATE OR REPLACE FUNCTION get_value (p_in IN NUMBER) RETURN NUMBER RESULT_CACHE CREATE OR REPLACE FUNCTION get_value (p_in IN NUMBER) RETURN NUMBER RESULT_CACHE RELIES_ON (res_cache_test_tab) http://www.oracle-base.com

15. Triggers: ENABLE and DISABLE clauses • It has long been possible to enable and disable triggers: • ALTER TRIGGER <trigger-name> DISABLE; • ALTER TRIGGER <trigger-name> ENABLE; • ALTER TABLE <table-name> DISABLE ALL TRIGGERS; • ALTER TABLE <table-name> ENABLE ALL TRIGGERS; • Oracle 11g allows triggers to be created in a DISABLED state, rather than creating it as enabled, then altering it to disabled. CREATE OR REPLACE TRIGGER trigger_control_test_trg BEFORE INSERT ON trigger_control_test FOR EACH ROW DISABLE BEGIN DBMS_OUTPUT.put_line('TRIGGER_CONTROL_TEST_TRG - Executed'); END; / http://www.oracle-base.com

16. Triggers: FOLLOWS clause • Oracle allows more than one trigger to be created for the same timing point, but has never guaranteed the execution order. • The FOLLOWS clause in 11g guarantees execution order of triggers defined with the same timing point. CREATE OR REPLACE TRIGGER test_tab_trigger_2 BEFORE INSERT ON test_tab FOR EACH ROW FOLLOWS test_tab_trigger_1 BEGIN -- Do something. NULL; END; / http://www.oracle-base.com

17. Triggers: Compound triggers CREATE OR REPLACE TRIGGER <trigger-name> FOR <trigger-action> ON <table-name> COMPOUND TRIGGER -- Global declaration. g_global_variable VARCHAR2(10); BEFORE STATEMENT IS BEGIN NULL; -- Do something here. END BEFORE STATEMENT; BEFORE EACH ROW IS BEGIN NULL; -- Do something here. END BEFORE EACH ROW; AFTER EACH ROW IS BEGIN NULL; -- Do something here. END AFTER EACH ROW; AFTER STATEMENT IS BEGIN NULL; -- Do something here. END AFTER STATEMENT; END <trigger-name>; / • A compound trigger allows code for one or more timing points for a specific object to be combined into a single trigger. • Global declaration is in scope for the whole operation. • Complete clean-up once operation is complete, even when exceptions are raised. • Demo http://www.oracle-base.com

18. Miscellaneous Changes • XML DB Native Web Services allow PL/SQL procedures and functions to be published as web services. • Referencing columns in WHERE clause of FORALL no longer causes PLS-00436. Makes using bulk-binds for DML even simpler. • PIVOT and UNPIVOT operators available from SQL. • REGEXP_INSTR and REGEXP_SUBSTR now include sub-expression pattern matches. REGEXP_COUNT counts occurrences of a pattern in a string. • Query Result Cache allows caching of query data, similar to PL/SQL Function Result Cache. • Database Resident Connection Pool (DRCP) brings connection pooling to languages that don’t natively support it. http://www.oracle-base.com

19. Summary • Named and Mixed Notation in PL/SQL Subprogram Invocations • Sequences in PL/SQL Expressions • SIMPLE_INTEGER Datatype • CONTINUE Statement • Dynamic SQL Enhancements • Generalized Invocation • Automatic Subprogram Inlining • PL/Scope • PL/SQL Hierarchical Profiler • PL/SQL Native Compiler Generates Native Code Directly • New PL/SQL Compiler Warning • Cross-Session PL/SQL Function Result Cache • Trigger Enhancements in Oracle Database 11g Release 1 • Miscellaneous additional enhancements. http://www.oracle-base.com

20. The End… • Questions? • References:http://www.oracle-base.com/articles/11g/PlsqlNewFeaturesAndEnhancements_11gR1.php • Demos:http://www.oracle-base.com/workshops/11g-new-features-for-plsql-developers/demos.zip http://www.oracle-base.com