Course Icon ORACLE Training

Oracle Advanced PL/SQL

0800 1337 401

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
See all of the available Technical Courses