Length: 4 Days Prerequisites:
Students must have a working knowledge of Oracle. Students
should be taking this training as an introduction to
the new features in Oracle 10g, not as an introduction to
Oracle. Those needing introduction to to Oracle should
attend the "Introduction to Oracle and SQL course."
Overview
In this hands-on training class you
will learn how to implement the new features provided with
Oracle10g. Both Oracle10g Release 1 and Release 2 features
are covered. We optionally review Oracle9i features that
may be new to many developers. Through evenly split
lecture and hands-on workshops, you will gain a practical
understanding of these features.
Key Benefits: After completing this
course you will be able to:
Utilize the
MERGE and MODEL statement enhancements
Work with the
new 10g features for Collections and Bulk DML
Understand how
to set the PL/SQL compiler for optimal performance
Better profile,
tune, and trace PL/SQL programs
Request PL/SQL
compiler warning messages
Code better
PL/SQL error routines
Obfuscate
(hide) PL/SQL code
Utilize new
Fine Grained Access Control(FGAC)/Virtual Private
Database(VPD) features
Utilize
Transparent Data Encryption(TDE) for
Sarbanes-Oxley(SOX) compliance
See changes
made to data with Flashback Version Query
Mine details of
transactions with Flashback Transaction Query
Undo changes to
tables with Flashback Table
And much more…
Recover dropped tables with the Oracle Recycle Bin
Perform DML on
XML data in tables using SQL, XQuery, and PL/SQL
Use DML Error
Logging to recover from errors in Bulk DML statements
Code case
insensitive sorts and searches
Search data
with Regular Expressions in SQL statements
Easily send
emails from PL/SQL program units using UTL_MAIL
Utilize
additional tuning enhancements such as the SQL Tuning
Advisor
Audit DML
statements with Fine Grained Auditing (FGA)
Use the new
Data Pump utility to export and import data
Audience: Oracle Developers
with hands-on experience with Oracle9i will benefit from
this training
Outline
Chapter 1. SQL Enhancements
9i Multi-Table INSERT
Oracle9i MERGE Statement
MERGE Enhancements
MERGE Enhancements Examples
MERGE Statement Workshop
MODEL Clause Concepts
MODEL Components
MODEL: Examples
MODEL Clause Workshop
Intro to 9i Datetime
Data Type: TIMESTAMP
Time Zones
TIMESTAMP WITH TIME ZONE
TIME STAMP WITH LOCAL TIME ZONE
Datetime Conversions
Datetime Workshop
Chapter 2. PL/SQL Enhancements
Associative Arrays
Associative Arrays Workshop
Record-Based DML
Bulk Processing
Bulk Collect
Bulk Collect: SELECT INTO
Bulk Collect: Multiple Arrays
Bulk Collect: Array of Records
Bulk Collect: FETCH with LIMIT
Bulk Collect Workshop
Bulk DELETE
Bulk INSERT
Returning into Arrays
FORALL Improvements
Collection Types: VARRAY
Collection Types: NESTED TABLE
Collection Methods
What is a VARRAY?
Creating a VARRAY
Inserting into VARRAYs
Querying VARRAYs
Initializing VARRAYS
What is a Nested Table?
Available PL/SQL Exceptions
Collections Dictionary Views
Nested Table Enhancements
Multi-Level Collections
Pipelined Functions
Review: 8i Table Functions
Building a Table Function
Using a Table Function
Pipelined Functions
Pipelined Example
Test Data Generator Example
Pipelined Table Functions Workshop
PL/SQL Performance
Performance: Compiler
Optimization Comparison
Introduction to Tuning PL/SQL
DBMS_PROFILER
Profiler Setup
Using Profiler
Profiler Testing
Profiler Reporting
Benchmarking
Profile & Tune PLSQL Workshop
DBMS_APPLICATION_ INFO
Obfuscation of PL/SQL Code
DBMS_APPLICATION_ INFO & PL/SQL Obfuscation
Workshop
Oracle
10g New Features for Developers