ORACLE 10G ADVANCED PL/SQL PROGRAMMING

Course Description: PL/SQL is Oracle's embedded procedural database language, allowing developers and administrators to write scripts, stored procedures, functions, packages, and database triggers which run in the optimized environment of the Oracle Database server. This advanced course helps PL/SQL programmers take advantage of language features, advanced techniques, and packages and facilities provided by Oracle to develop and tune efficient and effective PL/SQL subprograms.

Audience: Oracle application developers and database administrators.

Prerequisites: Introduction to Oracle 10g PL/SQL Programming.

Course Contents

The PL/SQL Execution Environment The Server Process PL/SQL Execution The PL/SQL Compiler Compiler Optimization SQL — Parse SQL — Execute and Fetch Server Memory Latches Locks Advanced Cursors Cursor Types Cursors and Storage Spanning Commits Across FETCHes Dynamic SQL in PL/SQL Bulk Operations Bulk Returns Limiting Results Cursor Parameters Cursor Variables Strong and Weak Cursors Using Cursor Variables Cursor Type Errors Cursor Subqueries Dynamic SQL Generating SQL at Runtime Native Dynamic SQL vs. DBMS_SQL Package The EXECUTE IMMEDIATE Statement Using Bind Variables Multi-row Dynamic Queries Bulk Operations with Dynamic SQL Using DBMS_SQL for DML and DDL Using DBMS_SQL for Queries Retrieving Meta Information with DBMS_SQL Object-Oriented Oracle Introducing Object-Oriented Oracle Defining Object Types and Tables in SQL Querying and Modifying Object Data Object Method Inheritance Type Evolution Object Views Object Types in PL/SQL REF Pointers Object Functions and Operators Tuning PL/SQL PL/SQL vs SQL PL/SQL Performance Tips Tuning Goals Monitoring Wait Events DBMS_PROFILER DBMS_TRAC Execution Plans Interpreting Explain Plan Results Execution Plan Details Trace Files TKPROF Using trcsess DBMS_APPLICATION_INFO

Debugging and Error Handling Exception Management Exception Propagation User-Defined Exceptions Exception Error Messages Stack Management Debugging with DBMS_OUTPUT Debugging with a Table Using UTL_FILE Using DBMS_DEBUG SQL Developer Avoiding Bugs Advanced Programming Topics Autonomous Transactions Invoker's Rights Fine-Grained Access Control with DBMS_RLS Creating Pipes with DBMS_PIPE Writing to and Reading from a Pipe Table Functions Pipelined Table Functions Enabling parallel execution DETERMINISTIC Functions Interfacing with External Code External Programs and Procedures External Procedure Architecture Configure Oracle For External Procedures Creating a java Stored Procedure Security and External Programs The Job Scheduler Manage and Drop External Jobs Native Compilation of PL/SQL Code The Oracle Call Interface (OCI and OCCI) Pro*C and Pro*C++ Using Pro*C and Pro*C++ Perl DBI/DBD Architecture Perl and Stored Procedures ODBC Using ODBC JDBC Working With XML Databases and XML Schema Validation Unstructured and Structured Storage The XMLType Datatype XPath Expressions Extracting XML Data Generating XML XMLQuery XMLType Views Oracle XML DB Repository