Course Contents
By the end of this advanced course for Oracle 11g PL/SQL users, delegates will have learned how to:
-- 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 9i, 10g and 11g PL/SQL who want to gain a greater understanding of the techniques, enhancements and programming concepts of PL/SQL.
Training Course Contents
Overview
- 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.
Collections
- 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.
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 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.