Oracle11g PL/SQL Foundations

Live Online & Classroom Enterprise Training

Location: Bangalore, India | Dubai, UAE

Industry Experienced Instructor  |  Remote Labs  |  Lab Access Post Training

Need help finding the right training?

Facebook Twitter Linkedin

Course Details

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.

Course Prerequisites

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

Course Agenda

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