Oracle PL/SQL Advanced

Course details

The course starts at revising SQL & PL/SQL and progresses to teach many complicated structures and methodologies to write efficient database PL/SQL.

It introduces many advanced concepts like dynamic SQL and shows how to take advantage of these to make everyday life of the developer easier.

Who Should Take This Course:

PL/SQL is the de facto language used in Oracle database. This course is intended for Database Developers in particular who need to work with PL/SQL everyday.

This course needs prior knowledge of database and SQL / PLSQL.

Course Content:

Module 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

Module 2: Use Collections

• Overview of Collections
• Associative Arrays
• Nested Tables Vs arrays
• Create PL/SQL Programs that use Collections
• Use Collections Effectively

Module 3: Advanced Interface Method

• 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

Module 4: Using Dynamic SQL

• Execute Immediate - Basic usage
• Usage of bind variables in Dynamic SQL
• REF Cursors and Dynamic SQL

Module 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

Module 6: Administer SecureFile LOBs

• Overview of SecureFile LOBs
• Configure the Environment for SecureFile LOBs
• Store Documents using SecureFile LOBs
• Store Documents using SecureFile LOBs
• Convert BasicFile LOBS to Secure File LOBs
• Evaluate the Performance of SecureFile LOBs
• Establish Space Utilization of a LOB File
• Set up Encryption
• Enable Compression and Deduplication

Module 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
• Reducing the Number of SQL Calls
• PL/SQL Tuning
• PL/SQL Native Compilation

Module 8: Use SQL & 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

Module 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

Module 10: Profile & Trace PL/SQL Code

• Trace PL/SQL Program Execution
• Profile PL/SQL Applications
• Access the Trace Data
• DBMS_PROFILER