TypeClassroom Training


Oracle 12c PL-SQL Intermediate


Audience & Prerequisites

Course Outline

Schedule & Fees


Oracle 12c PL/SQL Intermediate Training

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.


  • 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

Intended Audience

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

Course Outline                                                  Duration: 3 Days

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

2. Advanced Cursors

  • Cursor Parameters
  • Parameter Defaults
  • Taking Advantage of a Weak Cursor Variable
  • Using the FOR UPDATE Clause
  • Using PL/SQL Collections and Nested Collections

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

4. Advanced Triggers

  • Trigger Limitations
  • Mutating and Constraining Tables
  • Using CALL and Client Triggers
  • DDL / SERVERERROR Triggers
  • Schema vs. Database Triggers
  • Using Alternative Events and Levels
  • INSTEAD OF Triggers on Views
5. PL/SQL Composite Datatypes and Collections
  • PL/SQL Records
  • Associative Arrays (INDEX BY)
  • Nested Tables
  • Built-In Type Methods
  • Arrays of Composite Types
  • Using PL/SQL Record Variables
  • PL/SQL Collections

6. Bulk-Bind Data Loading Using PL/SQL

  • Defining Bulk Binds
  • Error Handling with Bulk Binds – SAVE EXCEPTIONS

7. Using Oracle Supplied Packages

  • UTL_FILE Package (file i/o)
  • DBMS_ALERT Package
  • DBMS_PIPE Package
  • DBMS_JOB Package
  • DBMS_STATS Package
  • DBMS_UTILITY Package
  • UTL_SMTP Package
  • UTL_MAIL Package

8. Writing Native Dynamic SQL

  • DBMS_SQL Package
  • Benefits Of NDS

9. PL/SQL Wrapper

  • PL/SQL Wrapper (Source Code Encryption)

10. Understanding Dependencies

  • Defining Dependencies
  • Local vs. Remote
  • Viewing Dependencies
  • Effect of Breaking Dependency Chain

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

12. Objects

  • Basic Objects
  • Object Inheritance

Please write to us at info@itstechschool.com & contact us at +91-9870480053 for the course price & certification cost, schedule & location

Drop Us a Query

For more info kindly contact us.