Oracle 11g Advanced PL/SQL

Course: ORPLSA

Duration: 3 Days

Level: III

Course Summary

This class covers advanced topics related to Oracle PL/SQL. This class provides the technical expertise necessary to utilize these powerful components of Oracle.

Attention in this class is given to some aspects of PL/SQL that are often not clearly understood (collections and overloading) or fully utilized (default values for program units, package startup code) along with advanced topics such as autonomous transactions, REF cursors and bulk binding. Some of the newer features of PL/SQL (the MERGE and CASE statements) are introduced. Techniques for measuring PL/SQL performance and features to enhance the performance of PL/SQL are also presented.

The functionality of a number of highly leverable Oracle supplied PL/SQL packages is covered.

This class can be conducted over a span of 2 or 3 days. See the Course Topics sections for the exact contents of each class duration.

« Hide The Details

Topics Covered In This Course

Oracle's SQL tools

SQL*Plus
iSQL*Plus
SQL*Developer

New Oracle features

Boolean variables
CASE statement
MERGE statement
Timestamp datatype
Interval datatype
Related functions
CAST function
Regular expressions

Working with block hierarchies

Understanding block hierarchies
Variable scope in hiararchies
Using block labels
Referencing out-of-scope variables
Navigating block hierarchies
The (dreaded) GOTO statement

Advanced exception handling

Capturing unexpected exceptions
Error trapping functions
User defined exceptions
Overriding an exception
Escalate an exception
Continuing after an exception

Maintaining PL/SQL stored program units

Using the Oracle data dictionary
Compiling, recompiling, dropping program units
Understanding object dependencies

Advanced features of packages

Overloading
Forward referencing
Package start up code

Advanced features of triggers

DML trigger directives
Autonomous triggers
INSTEAD OF triggers

Advanced cursor topics

Cursor parameters
Embedded cursors
Cursor variables (REF cursors)
Using anticipatory locking in cursors

Oracle supplied packages

UTL_FILE
DBMS_JOB
DBMS_PIPE
DBMS_DDL
DBMS_LOCK

PL/SQL composite variables

Records
Working with collections
Associative arrays
VARRAYs
Nested tables

Dynamic SQL

Native dynamic SQL
Using DBMS_SQL

Working with large objects (LOBs)

Understanding LOBs
Creating LOBs
Manipulating LOBs with DBMS_LOB
LOBs and NULL values

PL/SQL performance tuning

Using DBMS_TRACE
Using DBMS_PROFILER
NOCOPY hint
Using DBMS_SHARED_POOL

Advanced compilation techniques

Native compilation
Controlling compile error reporting
Conditional compilation

Who Should Take This Course

Intermediate Oracle PL/SQL developers and DBAs.

Recommended Prerequisites

Solid understanding of PL/SQL, including the use of program units (procedures, functions, packages and triggers) is required to succeed in this class.

Training Style

Lecture with frequent hands on labs and several multi-group projects.

« Hide The Details

Related Courses

Code	Course Title	Duration	Level
OR11D1	Oracle 11g/12c Developer: Introduction to PL/SQL	2 Days	I	Details
OR11E	Oracle 11g/12c Essentials: SQL, SQL*Plus and PL/SQL	5 Days	I	Details
OR11D	Oracle 11g/12c Developer: PL/SQL Complete	5 Days	I	Details
ORAPEX	Oracle APEX Version 5.0.4	5 Days	II	Details
OR11D2	Oracle 11g/12c Developer: Intermediate PL/SQL	3 Days	II	Details
ORSQLA	Oracle 11g New and Advanced SQL Features For Developers	3 Days	III	Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.

Notify me the next time this course is confirmed!

Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]