Oracle Advanced Pl/sql

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. Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.

Audience

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.

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.

Contents

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

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

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

Prerequisities

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.