Advanced Oracle PL/SQL Programming
Overview
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.
Target Audience:
Oracle application developers and database administrators.
Prerequisites:
Introduction to Oracle PL/SQL Programming.
Advanced Oracle PL/SQL Programming Training Class
Objectives
- Use detailed understanding of the PL/SQL execution environment
in your application design and tuning.
- Develop programs that make sophisticated and effective use of
cursors.
- Use all kinds of dynamic SQL in your PL/SQL code.
- Design and write solutions using Oracle's object types.
- Use Oracle's tools and supplied packages to trace, profile, and
tune your PL/SQL programs.
- Use a variety of techniques and tools for debugging PL/SQL
code.
- Write programs that interface between PL/SQL, and external
procedures and programs.
- Use package state to solve application problems.
- Use autonomous transactions in stored subprograms and
triggers.
- Choose which user's application context and rights will apply
when a stored subprogram runs.
- Write high-performance code using NOCOPY and pipelined table
functions.
- Create functions to implement fine-grained access control.
- Use DBMS_PIPE to set up inter-session communication between
PL/SQL programs.
1. Course Introduction
- Course Objectives
- Overview
- Suggested References
2. 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
3. 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
4. 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
5. 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
6. 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
7. 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
8. 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
9. 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
10. 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