Back to Search Results

Training Course Summary:

Design PL/SQL packages and program units that execute efficiently.
Create PL/SQL applications that use collections.
Write and tune PL/SQL code effectively to maximize performance.
Implement a virtual private database with fine-grained access control.
Perform code analysis to find program ambiguities, test, trace, and profile PL/SQL code.
This course is suitable for users of both 9i and 10g PL/SQL.

Pre-Requisites:

HTML/Javascript Knowledge
Oracle 10g PL/SQL (O021)
Oracle 9i PL/SQL (O020)

Who Should Attend:

PL/SQL Developer
Technical Consultant
Database Designers

Training Course Overview/Content:

Introduction
Course objectives
Course agenda
back to top
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
back to top
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.
back to top
Collections
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.
back to top
Web PL/SQL
Describe and use Web PL/SQL.
Write code and content for Web PL/SQL pages.
Run Web PL/SQL via a URL.
back to top
Dynamic SQL
Methods
Performing Dynamic SQL with DBMS_SQL Package
Native Dynamic SQL
back to top
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.
back to top
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.
back to top
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.