Oracle 10g Advanced PL/SQL (O022) Training Course
Training Course Objectives
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.
Who Should Attend
- PL/SQL Developer
- Technical Consultant
- Database Designers
Training Course Prerequisites
- HTML/Javascript Knowledge
- Oracle 9i PL/SQL (O020)
- Oracle 10g PL/SQL (O021)
Training Course Duration
3 Days
Training Course Contents
Introduction
- 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.
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.
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.
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.
