
Oracle Advanced PL/SQL
Overview
This course, designed for Oracle database administrators and software development personnel who need to gain practical experience of the advanced features of PL/SQL such as program design, packages, cursors, large objects and collections.
It also introduces some of the Oracle-supplied packages.
This Oracle Advanced PL/SQL course is suitable for users of Oracle Database 11g R2 and Oracle Database 12c.
Prerequisites
Practical experience of Oracle SQL and the PL/SQL programming language is required. This can be gained by attendance on the pre-requisite courses. Familiarity with the Oracle Database is also required.
Objectives
By the end of this Oracle Advanced PL/SQL course delegates will be able to effectively tune PL/SQL code; create subtypes; create and use collections; execute external C programs and Java programs from PL/SQL; use fine-grained access control; use LOB data types and the DBMS_LOB package; use SecureFile LOBs; effectively design cursors; improve memory usage with PL/SQL result caching and SQL result sets and apply coding standards to avoid SQL injection attacks.
Outline
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
- White List
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
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/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
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
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