Oracle Advanced PL/SQL - In-house Training

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.

The delegate will learn and acquire skills as follows:

• Writing code to interface with external applications and the operating system
• Creating PL/SQL applications that use collections
• Implementing a virtual private database with fine-grained access control
• Manipulating large objects
• Administering SecureFile LOBs
• Designing PL/SQL packages and program units for execution efficiency
• Tuning PL/SQL Code for Performance
• Improving memory usage with SQL and PL/SQL Result Caching
• Analysing PL/SQL Code
• Profiling and Tracing PL/SQL Code
• Safeguarding SQL code
• Miscellaneous New 12c Features

This Oracle Advanced PL/SQL course will teach the major topics required for the delegate to work towards achieving the following Oracle examinations:

• Oracle Database 12c: Advanced PL/SQL 1Z0-148
• Oracle Database 11g: Advanced PL/SQL 1Z0-146

Exams are not included as part of the 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
• DBMS_SQL Functions and New 12c Security Features
• Specify a White List of PL/SQL Units to Access a Package
• PL/SQL Bind Types

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

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 Utilisation 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
• 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

DAY 3 Session 9: ANALYSE 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

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

Session 12: MISCELLANEOUS NEW 12c NEW FEATURES

• Grant Roles to Standalone Programs and PL/SQL Packages
• BEQUEATH CURRENT_USER Views
• Enable INHERENT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges
• Edition Based Redefinition
• Invisible Columns and the %ROWTYPE Attribute

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

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

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.

Pre-Requisite Courses

• Oracle SQL
• Oracle PL/SQL
• Oracle PL/SQL Stored Program Units