Oracle 11g Advanced PL/SQL

Oracle 11g Advanced PL/SQL Training Course ContentsOverview

• Course objectives
• Course agenda

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

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

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

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

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

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

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