Live Chat
Monday - Friday 8am - 6pm EST Chat Now
Contact Us
Monday - Friday 8am - 8pm EST 1-866-716-6688 Other Contact Options
Checkout

Cart () Loading...

    • Quantity:
    • Delivery:
    • Dates:
    • Location:

    $

Oracle 11g PL/SQL Intermediate

GK# 8404 Vendor# OR125

Course Overview

TOP

In this course, you will receive hands-on instruction in the intermediate and advanced features of Oracle11g PL/SQL procedural language for SQL. You will learn how to control data sharing and locking, debug and use error reporting procedures use Oracle supplied packages, and develop database triggers, stored procedures, and functions for future reuse.

What You'll Learn

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

Outline

TOP
Viewing outline for:

Virtual Classroom Live Outline

1. Introduction to PL/SQL

  • Features and benefits of PL/SQL
  • Relationship of PL/SQL to SQL
  • PL/SQL development tools
  • Native compilation
  • PL/SQL system/session params
  • PL/SQL anonymous block Variable declarations
  • PL/SQL types and Records
  • Declaring variable datatypes dynamically
  • Modifying database data (DML)
  • Transaction control statements
  • Declaring explicit cursors
  • Implicit cursor attributes
  • Exception handling
  • Creating procedures, functions, packages, and DML triggers
  • Debugging with SQL Developer
  • Conditional compilation
  • Warnings

2. Advanced Cursors

  • Cursor parameters
  • Parameter defaults
  • Taking advantage of a weak cursor variable
  • OPEN FOR, FETCH, and CLOSE
  • Using the FOR UPDATE clause
  • Using PL/SQL collections and nested collections

3. Advanced Packages

  • Initializing variables
  • Module overloading
  • Recursion
  • Purity levels
  • Using the "Persistent Stateto advantage
  • One time only procedures
  • Forward declarations
  • Using Persistent State
  • Code encapsulation
  • Constant and exception standardization

4. Advanced Triggers

  • Trigger limitations
  • Mutating and constraining tables
  • Using CALL and client triggers
  • DDL / SERVERERROR triggers
  • LOGON/LOGOFF, SUSPEND, STARTUP/SHUTDOWN triggers
  • Schema vs. Database triggers
  • Using alternative events and levels
  • INSTEAD OF triggers on views

5. PL/SQL Composite Datatypes and Collections

  • PL/SQL records
  • Associative arrays (INDEX BY)
  • Nested tables
  • VARRAYs
  • Built-in type methods
  • Arrays of composite types
  • Using PL/SQL record variables
  • PL/SQL collections

6. Bulk-Bind Data Loading Using PL/SQL

  • Defining bulk binds
  • BULK COLLECT/FORALL
  • Error handling with bulk binds - SAVE EXCEPTIONS

7. Using Oracle Supplied Packages

  • DBMS_OUTPUT package
  • UTL_FILE package (file i/o)
  • DBMS_ALERT package
  • DBMS_PIPE package
  • DBMS_JOB package
  • DBMS_SCHEDULER package
  • DBMS_STATS package
  • DBMS_UTILITY package
  • UTL_SMTP package
  • UTL_MAIL package
  • DBMS_SQL package

8. Writing Native Dynamic SQL

  • EXECUTE IMMEDIATE
  • Benefits of NDS

9. PL/SQL Wrapper

  • PL/SQL wrapper (source code encryption)

10. Understanding Dependencies

  • Defining dependencies
  • Local vs. remote
  • Viewing dependencies
  • Effect of breaking dependency chain

11. Large Object Management in PL/SQL

  • Differences between LONG/LONG RAW and LOBs
  • Creating and using BFILEs
  • Creating tables with LOBs
  • LOBs and PL/SQL
  • DBMS_LOB capabilities
  • Temporary LOBs

12. Objects

  • Basic objects
  • Object inheritance

13. Java

  • Java in PL/SQL
  • Sample encapsulation

Labs

TOP
Viewing labs for:

Virtual Classroom Live Labs

Lab 1: Creating Procedures

Lab 2: Creating Functions

Lab 3: Creating Packages

Lab 4: Creating Triggers

Lab 5: Embedded Functions and Procedures

Lab 6: Creating Autonomous Transactions

Lab 7: Encrypting Source Code

Lab 8: Using the UTL_FILE Package

Lab 9: Using the DBMS_ALERT Package

Lab 10: Creating Object Types

Lab 11: Creating and Manipulating Object Tables

Lab 12: Working with Collections

Lab 13: Collections And Bulk Binding

Lab 14: DBMS_SQL/Native Dynamic SQL

Lab 15: Working with Large Objects

Lab 16: Load, Publish, and Run Java

Prerequisites

TOP
  • Oracle 11g SQL Basics & SQL*PLUS or equivalent experience
  • Oracle 11g PL/SQL Introduction or equivalent experience

Who Should Attend

TOP

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

Course Delivery

This course is available in the following formats:

Virtual Classroom Live

Experience expert-led online training from the convenience of your home, office or anywhere with an internet connection.

Duration: 3 day

Request this course in a different delivery format.
Enroll