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

Course:  OR11D2
Duration:  3 Days
Level:  II
Course Summary

You will receive hands-on instruction in the intermediate and advanced features of Oracle11g and/or 12c PL/SQL procedural language for SQL. You will cover 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.

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

« Hide The Details
Topics Covered In This Course

Review Of 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

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

Advanced Packages

  • Initializing variables
  • Module Overloading
  • Recursion
  • Purity levels
  • Using the ?Persistent State? to advantage
  • One Time Only Procedures
  • Forward Declarations
  • Using Persistent State
  • Code Encapsulation
  • Constant and Exception Standardization

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

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

Bulk-Bind Data Loading Using PL/SQL

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

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

Writing Native Dynamic SQL

  • EXECUTE IMMEDIATE
  • Benefits Of NDS

PL/SQL Wrapper

  • PL/SQL wrapper (source code encryption)

Understanding Dependencies

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

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

Objects

  • Basic Objects
  • Object Inheritance

Java

  • Java in PL/SQL
  • Sample encapsulation
What You Can Expect

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

  • The 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
  • Understanding Advanced PL/SQL Features
Who Should Take This Course

Application Developers and Database Administrators who need a comprehensive understanding of Oracle 10g PL/SQL language and those seeking the OCA or OCP certifications.

Recommended Prerequisites

Participants should have a thorough understanding of SQL and SQL*Plus, along with some experience with Oracle PL/SQL.

Training Style

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

« Hide The Details
Related Courses
Code Course Title Duration Level
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
OR11D
Oracle 11g/12c Developer: PL/SQL Complete
5 Days
I
Details
ORAPEX
Oracle APEX Version 5.0.4
5 Days
II
Details
ORPLSA
Oracle 11g Advanced PL/SQL
3 Days
III
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 [email protected]