Oracle 11g Advanced PL/SQL

Course code: O023

3 days £1,185.00

Oracle 11g 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.

Course content >

Who Should Attend?

  • Oracle Analyst/Programmers
  • Oracle Technicians
  • Oracle DBA's
  • Oracle Developers


Course Reviews

iTrain also offer all of these courses as closed courses on your site. Please call 020 7608 1835


Course Contents

This advanced Oracle 11g PL/SQL training Course will provide hands on experience for students to be able to design and create PL/SQL packages and program units that execute efficiently.

Students will also learn how to write and tune PL/SQL code and learn to create Oracle PL/SQL applications that use collections.

On this Oracle 11g Advanced PL/SQL course you will implement a virtual private database with fine-grained access control and perform code analysis to find program ambiguities, test, trace, and profile PL/SQL code.

This iTrain Oracle 11g Advanced PL/SQL course is suitable for users of 9i, 10g and 11g PL/SQL who want to gain a greater understanding of the techniques, enhancements and programming concepts of PL/SQL.



Oracle 11g Advanced PL/SQL Training Course Contents


  • Course objectives
  • Course agenda

PL/SQL Programming Review

  • 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 varrays.
  • 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.
  • PL/SQL Collections.
  • Associated Arrays.
  • Nested Tables.
  • Bulk Binds.
  • Bulk Collect.
  • Bulk Exceptions.
  • Caching Data.


  • 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 finegrained access control.
  • Describe an application context.
  • Set up a logon trigger.
  • View the results.
  • Query the dictionary views holding information on finegrained access.

Performance and Tuning

  • Tune PL/SQL code
  • 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 Ref Cursors.

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.

11g PL/SQL Feats & Enhancement

  • SIMPLE_INTEGER Datatype.
  • CONTINUE Statement.
  • Cross-Session PL/SQL.
  • Function Result Cache Enhancements to Regular Expression Built-in SQL Functions.
  • Sequences in PL/SQL.
  • Expressions.
  • Dynamic SQL Enhancements.
  • Names and Mixed Notation in PL/SQL subprogram.
  • Invocations.
Contact us for more information