Course 265:
Advanced Oracle PL/SQL Programming

(4 days)

 

Course Description

Create database-level applications using Oracle PL/SQL. Participants will be exposed to various advanced PL/SQL techniques for building and tuning robust business applications. Exercises and lab sessions reinforce the learning objectives and provide participants the opportunity to gain practical hands-on experience.

Learning Objectives

  • Develop efficient PL/SQL programs to access Oracle databases
  • Use some of the Oracle supplied PL/SQL packages to generate screen and file outputs
  • Design modular applications using packages
  • Invoke native dynamic SQL to build runtime SQL statements
  • Manage data retrieval with cursors and cursor variables
  • Enhance performance using collection datatypes and bulk operations
  • Manage dependency issues and collect PL/Scope data
  • Improve performance with native compilation
  • Create triggers to solve business challenges and enforce business rules
  • Write code to interface with LOBs and SecureFiles
  • Investigate techniques for tuning your PL/SQL code

Who Should Attend

Database administrators, developers, and anyone needing a working knowledge of PL/SQL.

Prerequisites

  • A working knowledge of SQL
  • Familiarity of SQL*Plus and SQL Developer
  • A basic understanding of PL/SQL

Course Outline

Introduction and Overview

  • Course Objectives
  • Describe the Course Database Schema
  • Advanced SQL Review Using SQL Developer

PL/SQL Concepts Review

  • Review the PL/SQL Block Structure
  • How to Generate Output from a PL/SQL Block
  • Variable Declaration, Assignment, Data Types and Scoping
  • Control Structures
  • Creating Procedures, Functions, and Packages
  • Handling Exceptions

The PL/SQL Compiler

  • Understand and Influence the Compiler
  • PLSQL_Optimize_Level
  • Controlling Compilation Messages
  • PL/SQL Native Compilation
  • Fine-Grained Dependency
  • Gathering PL/Scope Data for Debugging and Analysis
  • Wrapping Code for Security Purposes

Advanced Techniques with Procedures, Functions, Packages, and Triggers

  • OUT Parameters and the NOCOPY Hint
  • Definers Rights/Invokers Rights Subprograms
  • Autonomous Transactions
  • Utilizing the Initialization Section
  • Overloading Programs
  • User-Written SQL Functions
  • Table-Valued functions
  • Solving Mutating Table Problem with Compound Triggers

Cursor Variables and Expressions

  • Implicit and Explicit Cursors
  • Cursor Variables
  • Cursor Expressions

Using Collections

  • About Collections
  • Associative Arrays, Nested Tables, and VARRAYs
  • Coding with the Collection Methods
  • Using the Collection Multiset Operators
  • Comparing the Collection Types
  • Enhance Performance with Bulk Operations

Manipulating Large Objects

  • The LOB Datatypes
  • Anatomy of a LOB
  • Working with DBMS_LOB
  • Observer and Mutator Routines
  • Using SecureFiles

Oracle-Supplied Packages

  • Job Scheduling with DBMS_SCHEDULER
  • Table Redefinition with DBMS_REDEFINITION
  • Retrieving Object Metadata with DBMS_METADATA
  • Online Application Upgrade

Native Dynamic SQL

  • Syntax of NDS
  • Using the EXECUTE IMMEDIATE Statement
  • The Dangers of Injection and How to Avoid

Profiling and Tuning PL/SQL

  • The USER/ALL/DBA_IDENTIFIERS Dictionary View
  • Using DBMS_UTILITY.FORMAT_CALL_STACK
  • Tune PL/SQL Code
  • Using the Result Cache
  • Identify and Tune Memory Issues
  • Recognize Network Issues
  • Configuring the Profiler
  • Managing Profiler Runs
  • Analyzing Profiler Data
  • Interpreting the Results

Please Contact Your ROI Representative to Discuss Course Tailoring!