Oracle Advanced PL/SQL (STA_OAP)

Oracle Advanced PL/SQL Training Course

Course Contents - DAY 1

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
• • Select the Appropriate Collection Type
• • Create PL/SQL Programs that use Collections
• • Use Collections Effectively

Session 3: ADVANCED INTERFACE METHODS

• • The Benefits of External Routines
• • External Routine Components
• • Define the Library to Oracle
• • Register the External Function
• • Networking Files
• • The Shared Library
• • Call an Operating System Supplied DLL from PL/SQL
• • Create Secure External Procedures
• • Call C Program 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

Oracle Advanced PL/SQL Training Course

Course Contents - DAY 2

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/SQL Compiler
• • Influence the Compiler
• • Tuning PL/SQL Programs
• • The DBMS_UTILITY.GET_TIME Function
• • Tuning SQL
• • Reduce the Number of SQL Calls
• • PL/SQL Tuning
• • The FETCH FIRST Clause and Bulk Collections
• • The DBMS_EXECUTE_PARALLEL Package
• • PL/SQL Functions that Run Faster in SQL
• • 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
• • Invoker's Right Function Result Caching
• • The Invalidation of Cache Results
• • The DBMS_RESULTS_CACHE Package

Oracle Advanced PL/SQL Training Course

Course Contents - DAY 3

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
• • The UTL_CALL_STACK Package
• • Compile Time Warnings
• • Conditional Compilation

Session 10: PROFILE AND TRACE PL/SQL CODE

• • Trace PL/SQL Program Execution
• • Profile PL/SQL Applications
• • Access the Trace Data
• • DBMS_PROFILER
• • The Hierarchical Profiler
• • PL/SQL Predefined Enquiry Directives

Session 11: SECURE CODE

• • Grant Roles to Standalone Programs and PL/SQL Packages
• • BEQUEATH CURRENT_USER Views
• • Enable INHERENT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges
• • White Lists using the ACCESSIBLE BY Clause

Session 12: 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

Session 13: MISCELLANEOUS NEW 12c NEW FEATURES

• • Edition Based Redefinition
• • Invisible Columns and the %ROWTYPE Attribute