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