OR130: Oracle11g PL/SQL Foundations

$2,695.00


  • Virtual Classroom

  • Onsite

← Continue Searching

Duration: 5 Days

This course will cover the Oracle 11g PL/SQL programming language. You will learn to control data sharing and locking, develop an understanding of multi-user and concurrent transactions, and learn to develop triggers, procedures, functions, and packages.

What You Will Learn

  • PL/SQL Environment
  • PL/SQL Program Structure
  • Native Compilation
  • Update, Insert, and Delete Statements
  • Variable Scope Rules
  • Error Functions / Debugging
  • Logical Comparisons
  • Defining, Executing, and Testing Procedures
  • Defining, Executing, and Testing Functions
  • Creating Package Specifications and Bodies
  • Creating Triggers of All Types
  • Using Oracle Supplied Packages
  • Advanced PL/SQL Features

Audience

Application developers and database administrators who need a comprehensive understanding of Oracle 10g PL/SQL language

Prerequistes

Oracle 11g Foundation: SQL Basics & SQL*PLUS or equivalent experience

Course Outline

1. Introduction to PL/SQL

  • History of PL/SQL
  • Features and Benefits of PL/SQL
  • Relationship of PL/SQL to SQL
  • PL/SQL Development Tools
  • Native Compilation

2. PL/SQL Basics

  • PL/SQL Anonymous Block Structure, Lexical Units, Variable Declarations, Types, and Records
  • SQL*Plus Development Environment
  • Displaying Messages with DBMS_OUTPUT
  • Object Naming Rules
  • PL/SQL Style Guide and Coding Conventions

3. Working with Database Data

  • SELECTing Single Rows
  • Declaring Variable Datatypes Dynamically
  • Modifying Database Data (DML)
  • Transaction Control Statements

4. Selecting Multiple Rows Using Cursors

  • Declaring Explicit Cursors
  • Implicit Cursor Attributes
  • Using the Cursor FOR LOOP

5. Exception Handling

  • Writing an Exception Handler Section
  • Handling Predefined Exceptions
  • Controlling Exception Processing - Exception Propagation
  • RAISE_APPLICATON_ERROR Use
  • Preventing Unhandled Exceptions
  • Exception Propagation
  • Using PRAGMA EXCEPTION_INIT

6. Advanced Cursors

  • Cursor Parameters
  • Taking Advantage of a Weak Cursor Variable
  • OPEN FOR, FETCH, and CLOSE
  • Using the FOR UPDATE Clause
  • Using PL/SQL Collections and Nested Collections

7. Introduction to Procedures and Functions

  • Creating Stored PL/SQL Objects, Procedures, Functions

8. Creating Packages

  • Creating Package Specifications and Bodies
  • One Time Only Procedures
  • Persistent State

9. Creating DML Triggers

  • Triggering Events
  • Trigger Behavior
  • Correlation Identifiers
  • Multi-Statement Triggers
  • Trigger Firing Behavior
  • Enabling/Disabling Triggers

10. Advanced Packages

  • Initializing Variables
  • Module Overloading
  • Recursion
  • Purity Levels

11. Advanced Triggers

  • Trigger Limitations
  • Mutating and Constraining Tables
  • Using CALL
  • Client Triggers
  • DDL Triggers
  • Using SERVERERROR Event
  • Schema vs. Database Triggers
  • Using Alternative Events and Levels
  • INSTEAD OF Triggers on Views
  • PL/SQL Composite Datatypes and Collections
  • PL/SQL Records, PL/SQL Associative Arrays, and Arrays of Records
  • Using PL/SQL Record Variables
  • PL/SQL Collections

12. Bulk-Bind Data Loading Using PL/SQL

  • Defining Bulk Binds
  • Error Handling with Bulk Binds

13. Using Oracle Supplied Packages

  • DBMS_OUTPUT Package
  • UTL_FILE Package (file i/o)
  • DBMS_ALERT/PIPE Packages
  • DBMS_JOB Package
  • DBMS_SCHEDULER Package
  • DBMS_STATS Package
  • DBMS_UTILITY Package
  • UTL_SMTP/MAIL Packages
  • DBMS_SQL Package

14. Writing Native Dynamic SQL

  • EXECUTE IMMEDIATE

15. PL/SQL Wrapper

  • PL/SQL Wrapper (source code encryption)

16. Dependencies

  • Viewing Dependencies
  • Effect of Breaking Dependency Chain

17. Large Object Management in PL/SQL

  • LONG/LONG RAW vs. LOBs
  • Creating and using BFILEs
  • Tables with LOBs
  • LOBs and PL/SQL
  • DBMS_LOB Capabilities
  • Temporary LOBs

18. Objects

  • Basic Objects
  • Object Inheritance

19. Java in PL/SQL

Course Labs

Lab 1: Using PL/SQL to Create an Anonymous Block

Lab 2: PL/SQL Program Control

Lab 3: Selecting and Updating Database Data

Lab 4: Using Explicit Cursors

Lab 5: Handling Exceptions

Lab 6: Creating Procedures

Lab 7: Creating Functions

Lab 8: Creating Packages

Lab 9: Creating Triggers

Lab 10: Embedded Functions and Procedures

Lab 11: Creating Autonomous Transactions

Lab 12: Encrypting Source Code

Lab 13: Using the UTL_FILE Package

Lab 14: Using the DBMS_ALERT Package

Lab 15: Creating Object Types

Lab 16: Creating and Manipulating Object Tables

Lab 17: Working with Collections

Lab 18: Collections and Bulk Binding

Lab 19: DBMS_SQL / Native Dynamic SQL

Lab 20: Working with Large Objects

Lab 21: Load, Publish, and Run Java