Oracle 11g/12c Developer: PL/SQL Complete

Course:  OR11D
Duration:  5 Days
Level:  I
Course Summary

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

This course can also be presented with content specific to Oracle 10g.

« Hide The Details
Topics Covered In This Course

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

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, and PL/SQL style guide and coding conventions

Working with Database Data

  • SELECTing single rows
  • Declaring variable datatypes dynamically
  • Modifying database data (DML)
  • Transaction control statements

Selecting Multiple Rows Using Cursors

  • Declaring explicit cursors
  • Implicit cursor attributes
  • Using the cursor FOR LOOP

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

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

Introduction to Procedures and Functions

  • Creating stored PL/SQL objects, procedures, functions

Creating Packages

  • Creating package specifications and bodies
  • One Time Only Procedures
  • Persistent State

Creating DML Triggers

  • Triggering events, Trigger behavior
  • Correlation identifiers, Multi-statement triggers
  • Trigger firing behavior, Enabling/Disabling triggers

Advanced Packages

  • Initializing variables
  • Module Overloading
  • Recursion
  • Purity levels

Advanced Triggers

  • Trigger limitations, Mutating and Constraining Tables
  • Using CALL, and 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

Bulk-Bind Data Loading Using PL/SQL

  • Defining bulk binds
  • Error handling with bulk binds

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

Writing Native Dynamic SQL

  • EXECUTE IMMEDIATE

PL/SQL Wrapper

  • PL/SQL wrapper (source code encryption)

Understanding Dependencies

  • Viewing dependencies
  • Effect of breaking dependency chain

Large Object Management in PL/SQL

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

Objects

  • Basic Objects, Object Inheritance

Java in PL/SQL

What You Can Expect

On successfully completing this course, participants can expect to be able to:

Who Should Take This Course

Application Developers and Database Administrators who need a comprehensive understanding of Oracle 11gR2 PL/SQL language and those seeking OCA or OCP certification.

Recommended Prerequisites

Oracle 11gR2 Foundation: SQL Basics & SQL*Plus or equivalent experience.

Training Style

Short lecture sessions (60%) along with hands-on lab sessions (40%).

« Hide The Details
Related Courses
Code Course Title Duration Level
OR11D1
Oracle 11g/12c Developer: Introduction to PL/SQL
2 Days
I
Details
OR11F
Oracle 11g and 12c Foundations: SQL and SQL*Plus
5 Days
I
Details
OR11E
Oracle 11g/12c Essentials: SQL, SQL*Plus and PL/SQL
5 Days
I
Details
ORDSCP
ORACLE 10g / 11g BI DISCOVERER PLUS for End Users
2 Days
I
Details
ORAPEX
Oracle APEX Version 5.0.4
5 Days
II
Details
OR11D2
Oracle 11g/12c Developer: Intermediate PL/SQL
3 Days
II
Details
ORSQLA
Oracle 11g New and Advanced SQL Features For Developers
3 Days
III
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at info@verhoef.com