Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: DESIGNING PL/SQL CODE FOR PERFORMANCE

• Oracle Predefined Data Types
• Create Subtypes Based on Existing Types for an Application
• Guidelines for Cursor Design
• Use Cursor Variables
• Pass Cursor Variables as Program Parameters
• Compare Cursor Variables to Static Cursors

Session 2: USE COLLECTIONS

• Overview of Collections
• Associative Arrays
• Nested Tables
• Varrays
• Create PL/SQL Programs that use Collections
• Use Collections Effectively

Session 3: ADVANCED INTERFACE METHODS

• The Benefits of External Routines
• External Routine Components
• Call an OS Supplied DLL
• Call Java from PL/SQL
• Call C from PL/SQL
• Call Java from PL/SQL

Session 4: VPD AND FINE-GRAINED ACCESS CONTROL

• Overview of Fine-Grained Access Control
• The Features of Fine-Grained Access Control
• Overview of an Application Context
• Create an Application Context
• Set an Application Context
• Describe the Package DBMS_RLS
• Implement a Policy
• Query the Data Dictionary for Fine-Grained Access Control
• Information

Session 5: MANIPULATE LARGE OBJECTS

• Overview of a LOB Object
• Manage Internal LOBs
• Character Large Objects
• Binary Large Objects
• External Large Objects - BFILEs
• Create and Use the DIRECTORY Object to Access and Use BFILEs
• The DBMS_LOB Package
• Delete LOBs
• Create a Temporary LOB using the Package DBMS_LOB

Session 6: ADMINISTER SECUREFILE LOBS

• Overview of SecureFile LOBs
• Configure the Environment for SecureFile LOBs
• Store Documents using SecureFile LOBs
• Convert BasicFile LOBS to SecureFile LOBs
• Evaluate the Performance of SecureFile LOBs
• Establish Space Utilization of a LOB File
• Set up Encryption
• Enable Compression and Deduplication

Session 7: TUNING PL/SQL CODE FOR PERFORMANCE

• Understand the PL/SQLCompiler
• Influence the Compiler
• Tuning PL/SQL Programs
• The DBMS_UTILITY.GET_TIME Function
• Tuning SQL
• Reducing the Number of SQL Calls
• PL/SQL Tuning
• PL/SQL Native Compilation

Session 8: USE SQL AND PL/SQL CACHING TO IMPROVE PERFORMANCE

• The Importance of the Shared Pool
• Library Cache
• The Data Dictionary Cache
• Pinning Code in the Shared Pool
• SQL and PL/SQL Caching
• PL/SQL Function Cache

Session 9: ANALYZE PL/SQL CODE

• Use Data Dictionary Views to View Coding Information
• Use Supplied Packages to View Coding Information
• Use the DBMS_METADATA Package to View Metadata as XML
• Use the DBMS_METADATA Package to Create DDL that can be used to Re-create Objects
• Determine Identifier Types and Usages with PL/Scope

Session 10: PROFILE AND TRACE PL/SQL CODE

• Trace PL/SQL Program Execution
• Profile PL/SQL Applications
• Access the Trace Data
• DBMS_PROFILER

Session 11: SAFEGUARD CODE

• Overview of SQL Injections
• Detecting Attacks
• Review of Dynamic SQL
• The Attack Surface
• Typical SQL Attacks
• Reduce Attack Surfaces
• Use the DBMS_ASSERT Package
• Design Immune Code
• Test Code for SQL Injection Flaws