Oracle Advanced PL/SQL Programming Training in Clifton

Enroll in or hire us to teach our Oracle Advanced PL/SQL Programming class in Clifton, New Jersey by calling us @303.377.6176. Like all HSG classes, Oracle Advanced PL/SQL Programming may be offered either onsite or via instructor led virtual training. Consider looking at our public training schedule to see if it is scheduled: Public Training Classes
Provided there are enough attendees, Oracle Advanced PL/SQL Programming may be taught at one of our local training facilities.

Answers to Popular Questions:

Yes, this class can be tailored to meet your specific training needs.
Yes, we provide consulting services.
Yes, group discounts are provided.

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.
Course Length: 2 Days
Course Tuition: $790 (US)


A solid understanding of SQL and 3GL programming is required. It is recommended the student understand Oracle 10g SQL features.

Course Outline


Course Introduction
Course Objectives
Course Overview
Using the Workbook
Suggest References

PL/SQL Execution Environment
The Server Process
PL/SQL Execution
PL/SQL Compiler
Compiler Optimization
SQL - Parse
SQL - Execute and Fetch
Server Memory

Advanced Cursors
Cursor Types
Cursor and Storage
Spanning Commits Across FETCHes
Dynamice 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 ackage
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
Introducting Object-Oriented Oracle
Defining Object Types and Tables in SQL
Querying and Modifying Object Data
Object Methods
Type Evolution
Object Views
Object Types in PL/SQL
REF Pointers
Object Functions and Operators

Tuning PL/SQL
PL/SQL Performance Tips
Tuning Goals
Monitoring Wait Events
Execution Plans
Interpreting Explain Plan Results
Execution Plan Details
Trace Files
Using trcsess

Debugging and Error Handling
Exception Management
Exception Propagation
User-Defined Exceptions
Exception Error Messages
Stack Management
Debugging with DBMS_OUTPUT
Debugging with a Table
SQL Developer
Avoiding Bugs

Bulk Operations
Bulk Binding
FORALL Statement
FORALL Variations
Bulk Returns
Bulk Fetching with Cursors

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

Creating Packages
Structure of a Package
The Package Interface and Implementation
Package Variables and Package State
Overloading Package Functions and Procedures
Forward Declarations
Strong REF CURSOR Variables
Weak REF CURSOR Variables

Interfacing with External Code
External Programs and Procedures
External Procedure Architecture
Configure Oracle for External Procedures
Creating a Java Stored Procedure
The Job Scheduler
Manage and Drop External Jobs
Temporary LOBs
Native Compilation of PL/SQL Code
The Oracle Call Interface
Pro*C and Pro*C+
Perl DBI/DBD Architecture
Perl and Stored Procedures
Using ODBC

Working With XML
Databases and XML
Schema Validation
Unstructured and Structured Storage
The XMLType Datatype
XPath Expressions
Extracting XML Data
Generating XML
XMLType Views
Oracle XML DB Repository

Course Directory [training on all levels]

Upcoming Classes
Gain insight and ideas from students with different perspectives and experiences.

Interesting Reads Take a class with us and receive a book of your choosing for 50% off MSRP.