Oracle 10g Advanced PL/SQL

Course code: O022

2 days £790.00

This Advanced Oracle 10g PL/SQL training course is suitable for users of Oracle 9i & Oracle 10g wanting to gain a greater understanding of the techniques to design PL/SQL packages and program units efficiently.

Course content >

Who Should Attend?

  • PL/SQL Developer
  • Technical Consultant
  • Database Designers


Course Contents

This Oracle 10g Advanced PL/SQL Training Course is designed to provide hands on experience for students wishing to Design PL/SQL packages and program units that execute efficiently.

Students attending the Advanced Oracle 10g PL/SQL course will learn to create PL/SQL applications that use collections. Students will be shown how to write and tune PL/SQL code effectively to maximize performance.

On completion of this Oracle 10g Advanced PL/SQL course you will be able to perform code analysis to find program ambiguities, test, trace, and profile PL/SQL code on a implemented virtual private database.

Oracle 10g Advanced PL/SQL Training Course Contents


  • Course objectives
  • Course agenda

PL/SQL Programming Concepts

  • Identify PL/SQL block structure
  • Create procedures
  • Create functions
  • Create packages
  • Use cursors
  • Handle exceptions
  • Understand dependencies
  • Identify the Oracle supplied packages

Design Considerations

  • List the different guidelines for cursor design.
  • Describe cursor variables
  • Pass cursor variables as program parameters.
  • Compare cursor variables to static cursors.
  • Describe the predefined data types.
  • Create subtypes based on existing types for an application.


  • Describe and use nested tables
  • Describe and use varrays
  • Describe and use associative arrays
  • Describe and use string indexed collections.
  • Describe and use nested collections
  • Write PL/SQL programs that use collections.
  • Describe the common collection exceptions and how to code for them.
  • Compare associative arrays to collections.


  • Describe and use Web PL/SQL.
  • Write code and content for Web PL/SQL pages.
  • Run Web PL/SQL via a URL.

Dynamic SQL

  • Methods
  • Performing Dynamic SQL with DBMS_SQL Package
  • Native Dynamic SQL

Fine Grained Access Control

  • Understand how fine-grained access control works.
  • Describe the features of fine-grained access control.
  • Describe an application context.
  • Set up a logon trigger.
  • View the results.
  • Query the dictionary views holding information on fine-grained access.

Performance and Tuning

  • Write smaller executable sections of code.
  • Compare SQL to PL/SQL on performance.
  • Understand how bulk binds can improve performance.
  • Handle exceptions with the FORALL syntax.
  • Identify data type and constraint issues.
  • Recognize network issues.

Analyzing PL/SQL Code

  • Use the supplied packages and dictionary views to find coding information.
  • dbms_describe supplied package.
  • Use supplied packages to find error information.
  • Trace PL/SQL programs using the dbms_trace supplied package.
  • Read and interpret trace information.
  • Profile PL/SQL using dbms_profiler supplied package.
  • Read and interpret profiler information.
Contact us for more information