Course Description

This 2-day Oracle PL/SQL Programming Advanced training course will help the PL/SQL student take advantage of language features, advanced techniques, and packages and facilities provided by Oracle to develop and tune efficient and effective PL/SQL subprograms.

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.

While this course utilises the Oracle 11g release for practical purposes, it is highly suitable for users of earlier or more recent releases of the Oracle RDBMS.

Pre-requisites

Attendance at our Oracle PL/SQL Programming Introduction training course, or equivalent experience.

Who is this course for?

For Application Developers, Database Administrators, System Administrators and users who write applications and procedures that access an Oracle database.

Course content

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_TRACE
Execution Plans
Interpreting Explain Plan Results
Oracle® 11g Advanced
PL/SQL Programming
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