This two-day training, sponsored by RMOUG, covers the most critical features you need to know about if you are going to write PL/SQL programs that fully utilize the capabilities of PL/SQL, including the most important enhancements of Oracle Database 11g for PL/SQL developers.
Detailed Description of Topics to be Covered
- Bulk Processing with BULK COLLECT and FORALL: the most important performance optimization feature of PL/SQL, bulk processing turbo-charges the speed of repeated execution of the same SQL statement (think: DML inside a loop) by reducing the number of context switches. We'll explore every nook and cranny of BULK COLLECT and FORALL.
- The Function Result Cache (11g): a fantastically elegant and high-impact feature, the function result cache provides an SGA-based cache that helps you optimize the performance of queries
across an entire instance that repeatedly retrieve the same rows of data.
- Row-Level Error Suppression in DML: added in Oracle Database 10g Release 2, the LOG ERRORS clause (along with the built-in package, DBMS_ERRLOG) offers row-level suppression of errors in DML statements, especially helpful when modifying large numbers of rows.
- PL/Scope (11g): new to Oracle Database 11g, PL/Scope analyzes the way that all identifiers (variables, constants, subprograms, exceptions, types, etc.) are used in your code. It allows us to perform analysis on our code in a way never before possible. For example, you can implement your own scripts to check code for compliance with naming conventions - no parser required!
- The NOCOPY Hint: so simple, and yet so impactful. Add one keyword to your IN OUT parameter definition and get a significant boost in performance. Along the way, learn about the
difference between formal parameters and actual argument values, passing by reference vs. value.
- Dynamic SQL Advanced Features: Oracle Database 11g includes many enhancements for dynamic SQL, especially for Method 4 dynamic SQL.
- Advanced Collection Features: the year is 2012 and all PL/SQL developers should be aware of and use collections (PL/SQL's version of arrays). So we'll skip the basics and concentrate on advanced features like use of the TABLE operator, string-indexed associative arrays, and nested collections.