Oracle 12c PL/SQL Intermediate

Course Description

Thank you for reading this post, don't forget to subscribe!

In this course, you will learn about intermediate and advanced features of Oracle 12c PL/SQL procedural language for SQL. You will also learn how to control data sharing and locking, debug and use error reporting procedures use Oracle supplied packages, as well as develop database triggers, stored procedures, and functions.

Audience Profile

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

Prerequisites

Oracle Database 12c: SQL I – Introduction

Learning Objectives

• 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
• Using Oracle Supplied Packages
• Understanding Advanced PL/SQL Features

Course Outline

Module 1: Review of Introduction to PL/SQL
Lessons
• Features and Benefits of PL/SQL
• Relationship of PL/SQL to SQL
• PL/SQL Development Tools
• Native Compilation
• PL/SQL System/Session Parameters
• 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
Lab: Creating Procedures

Module 2: Advanced Cursors
Lessons
• 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
Lab: Creating Functions

Module 3: Advanced Packages
Lessons
• 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
Lab: Creating Packages

Module 4: Advanced Triggers
Lessons
• 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
Lab: Creating Triggers

Module 5: PL/SQL Composite Datatypes and Collections
Lessons
• 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
Lab: Embedded Functions and Procedures

Module 6: Bulk-Bind Data Loading Using PL/SQL
Lessons
• Defining Bulk Binds
• BULK COLLECT / FORALL
• Error Handling with Bulk Binds – SAVE EXCEPTIONS
Lab: Creating Autonomous Transactions

Module 7: Using Oracle Supplied Packages
Lessons
• 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
Lab: Encrypting Source Code

Module 8: Writing Native Dynamic SQL
Lessons
• DBMS_SQL Package
• EXECUTE IMMEDIATE
• Benefits Of NDS
Lab: Using the UTL_FILE Package

Module 9: PL/SQL Wrapper
Lessons
• PL/SQL Wrapper (Source Code Encryption)
Lab: Using the DBMS_ALERT Package

Module 10: Understanding Dependencies
Lessons
• Defining Dependencies
• Local vs. Remote
• Viewing Dependencies
• Effect of Breaking Dependency Chain
Lab: Creating Object Types

Module 11: Large Object Management in PL/SQL
Lessons
• Differences between LONG/LONG RAW and LOBs
• Creating and Using BFILEs
• Creating Tables with LOBs
• LOBs and PL/SQL
• DBMS_LOB Capabilities
• Temporary LOBs
Lab: Creating and Manipulating Object Tables

Module 12: Objects
Lessons
• Basic Objects
• Object Inheritance
Lab: Working with Collections
Lab: Collections and Bulk Binding
Lab: DBMS_SQL / Native Dynamic SQL
Lab: Working with Large Objects