English Delivery Only: Oracle Advanced PL/SQL

Who will the Course Benefit?

Software development personnel and Oracle database administrators who need to take advantage of the advanced features of PL/SQL in Oracle Database.

Practical experience of Oracle SQL or SQL Developer and a good working knowledge of the fundamentals of the PL/SQL programming language are required.

Versions supported 12cR2,18c and 19c.

Course 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.

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

Requirements

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.

Further Learning

• Oracle 19c SQL Performance Tuning
• Oracle Database 19c Administration