SYSTEMS & SOFTWARE
Training Limited
Oracle™ 10g Advanced PL/SQL
This course is suitable for anyone new to Oracle™ 10g
Course Content 1 Day

Overview

All attendees will learn how to:
Understand the PL/SQL execution environment
Use advanced cursor techniques
Generate dynamic SQL
Use PL/SQL's object-oriented features
Tune PL/SQL
Debug and handle errors in PL/SQL
Interface with external code
Work with XML via PL/SQL

Outline

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 (optional)

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  (optional)

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 (optional)

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
Conclusion