SkillBuilders Welcome  |  Log in
All Oracle Classes

Advanced Oracle 11g PL/SQL Programming
Two Day Onsite / 14 Hour Online Hands-On Training.

Oracle PL/SQL developers will learn to use advanced features enabling improved programming and enhanced performance.

Upon successful completion of this course, class attendees should, with the aid of appropriate documentation be able to:

  • Use advanced features of Oracle-supplied packages to enrich applications
  • Maximize performance of PL/SQL through design and tuningUse Oracle object features including User-Defined Types, Nested Tables,Varrays, REFs, and Object Views via PL/SQL
  • Use advanced data handling functionality including collections and bulk bind
  • Know when dynamic SQL may be used to improve performance or functionality
  • Create PL/SQL that uses external applications and the operating system
  • Design and use PL/SQL packages, procedures, functions, and triggers to maximize performance
  • Become aware of PL/SQL's new features and how to use them
  • Use XML from PL/SQL
Average Student Rating:
Review of PL/SQL
  • Exception Handling
  • Block Structure, Syntax and Datatypes
  • Procedures, Functions, Packages, Local subprograms
New PL/SQ:Features You Might Have Missed
Invoker Rights (AUTHID)
Autonomous Transactions
Computer Exercise: Use Invoker Rights and Autonomous Transactions
Function Optimization Hints (NOCOPY) Using Purity Features (WNDS, RNDS, RNPS, WNPS, TRUST)
Using Overloading
Triggers, Compound Triggers, and Triggers with FOLLOWS
Computer Exercise: Use Advanced Features of Stored PL/SQL
Oracle-Supplied Packages: DBMS_LOB, UTL_FILE, UTL_MAIL, others
Fine-grained security: DBMS_RLS, DBMS_SESSION, others
Database object information and DBMS_METADATA
Computer Exercise: Working with Supplied Packages
Dynamic SQL
Computer Exercise: Building SQL Dynamically
  • Implicit and Explicit Cursors
  • Cursor design
Cursor variables and REF Cursors
Cursor variables as parameters
Cursor variables vs static cursors
Computer Exercise: Creating and Using REF Cursor
Oracle Object Support
Types and Subtypes
Computer Exercise: Creating Subtypes
Large Objects (BLOB, CLOB, BFILE)
  • SQL and PL/SQL (DBMS_LOB) support for LOBs
  • Directory Objects
  • SecureFile and BasicFile
Optional Exercise: Using Large Objects        
XML DB Concepts and Features
Using XMLType data (Binary, Structured, Unstructured)
XML Indexing
XML Functions (ISO and Oracle)
Loading data into XMLType
Using XMLType methods
Computer Exercise: Create XMLType Tables and Columns
Collections of Data
  • Associative Arrays (PL/SQL tables)
  • Nested Tables
Collection Exceptions
Choosing collection type
Exception handling
Using Packages for Constants
Computer Exercise: Working with PL/SQL Collections
Bulk-Bind in PL/SQL
Computer Exercise: PL/SQL Savings with Bulk Bind
Interfacing with non-PL/SQL
Using External C Routines and Operating System Commands
Create Java "Wrapper" to public Java class
Using Java classes from PL/SQL
Advanced Interface Methods
Computer Exercise: Calling Java Subroutines
Performance and PL/SQL
  • NOCOPY hint
  • Using appropriate numeric types
  • Package groupings
PL/SQL and SQL Results Cache
Learning the System Global Area (SGA) and the Impact on PL/SQL Processing
Understanding Memory Issues with Packages, Procedures, and Functions
"Pinning" PL/SQL
Tracing PL/SQL Execution
Profiling PL/SQL Applications
Identification and Tuning of Memory Issues
PL/SQL Compiler
Computer Exercise: PL/SQL Performance Planning and Tuning
Wrapping PL/SQL Code
Design Considerations for PL/SQL Program Units

Overview of Edition Based Redefinition (EBR)

SkillBuilders Sample Video