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

Course:   OR11D
Duration:   5 Days
Level:   I
On our website at:   http://www.verhoef-training.com/courses/OR11D.html
 
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.

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%).

Related Courses
Code Course Title Duration Level
OR18NEW
Oracle12c -18c New Features For Administrators (Five Days)
5 Days
I
Details
OR18SQL
Oracle18c SQL
5 Days
I
Details
OR18DBA1
Oracle 18c DBA I
5 Days
I
Details
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.