Oracle Advanced PL/SQL Programming Training in Elgin

Enroll in or hire us to teach our Oracle Advanced PL/SQL Programming class in Elgin, Illinois 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.
We offer private customized training for groups of 3 or more attendees.

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)

Prerequisites

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
Latches
Locks

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
The EXECUTE IMMIEDIATE 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
Introducting Object-Oriented Oracle
Defining Object Types and Tables in SQL
Querying and Modifying Object Data
Object Methods
Inheritance
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
DBMS_PROFILER
DBMS_TRACE
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

Bulk Operations
Bulk Binding
BULK COLLECT Clause
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
The DBMS_UTILITY Package
Creating Pipes with DBMS_PIPE
Writing to and Reading from a Pipe
Table Functions
The DBMS_METADATA Package
Pipelined Table Functions
Enabling Parallel Execution
DETERMINISTIC Functions

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
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

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.