Home About Courses Schedule Services Webinars Contact Search

Oracle Advanced PL/SQL Programming

SEE SCHEDULE

Duration: 2 Days

Method: Instructor led, Hands-on workshops

Price: $1195.00

Course Code: OR5470



View Related Video

Audience

Programmers and Database Administrators responsible for the development or maintenance of application systems which access an Oracle database.

Description

The course provides attendees with the background to develop stored PL/SQL programs including functions, procedures, packages, and triggers.Commonly used predefined packages provided with the Oracle DBMS are examined. Discussions include how these packages could be used as part of in-house applications. Additional PL/SQL features such as overloading programs, collections (arrays), bulk processing and autonomous transactions are also presented. Workshops provide students with the opportunity to use these features and evaluate their potential role in an application.This course can be taught for any Oracle version.

Prerequisites

OR5460 Oracle PL/SQL Programming or equivalent experience

Topics

  1. PL/SQL on the Server
    • Review Stored Procedures, Functions, Packages
    • Overloading Programs
      • Benefits of Overloading
      • Overloading Rules
    • Definer’s versus Invoker’s Rights
    • Review Triggers
    • Viewing Source Code
  2. Advanced Features
    • Collections
      • PL/SQL Associative Arrays
      • The VARRAY Type
      • Nested Tables
    • Array Methods
    • Benefits of Using Arrays
    • Pros and Cons of each Array type
  3. BULK Operations
    • BULK Operations Benefits
    • SELECT … BULK COLLECT
    • FORALL Statement
      • INSERT, UPDATE, DELETE
    • BULK Options
    • BULK Behavior
  4. Oracle Supplied Packages
    • UTL_FILE
      • Prerequisites
      • Creating / Using a Directory
    • DBMS_RESUMABLE
      • Benefits
    • DBMS_ALERT
    • DBMS_FLASHBACK
      • Viewing Data from the Past
    • DBMS_METADATA
      • Retrieve DDL for Objects
    • DBMS_SCHEDULER
      • Scheduler Pieces
      • Programs
      • Schedules
      • Jobs
    • Other Packages
  5. Autonomous Programs / Transactions
    • Behavior
    • Usage Reasons
    • Declaring
      • The PRAGMA Statement
    • Using in Stored Procedures
    • Using in Triggers