Introduction to Oracle 12c PL/SQL Programming
Private Training

Course Summary

A Relational Database Management System (RDBMS) is a software system that allows you to create and manage a relational database. Minimum requirements for such a system are defined by both ANSI and ISO. PL/SQL is Oracle’s Procedural Language for SQL. It is Oracle’s database programming language for creating stored procedures, functions, database triggers, and object methods. PL/SQL can be used for implementing business rules, computing algorithms, manipulating data, and for stand-alone programs. Oracle 12c focuses on Oracle’s cloud-based offerings with a new architecture – Plugable Databases (PDBs) – designed for rapid deployment and migration in a multi-tenant environment. This courseware is designed to teach both end-users and developers accessible but powerful SQL query and data manipulation (DML) skills, then teach critical programming and optimization techniques using advanced PL/SQL features.

Application developers and database administrators.
Course Length
3 Days
Introduction to Oracle 12c SQL Programming is required. Programming experience in a high-level language, such as COBOL, Java, or Perl is also required.


Create triggers on database tables.

Use PL/SQL’s datatypes for database and program data.

Use program structure and control flow to design and write PL/SQL programs.

Create PL/SQL stored procedures and functions.

Write robust programs that handle runtime exceptions.

Use PL/SQL’s collection datatypes.

Use cursors to work with database data.

Use the packages supplied with Oracle.

Design and write your own packages.

Maintain and evolve your PL/SQL programs.

Manage the security of your stored PL/SQL programs.

Detailed Outline

  1. Course Introduction
    • Course Objectives
    • Course Overview
    • Using the Workbook
    • Suggested References
  2. Triggers
    • Beyond Declarative Integrity
    • Triggers
    • Types of Triggers
    • Trigger Sequencing
    • Row-Level Triggers
    • Trigger Predicates
    • Trigger Conditions
    • Using SEQUENCEs
    • Cascading Triggers and Mutating Tables
    • Generating an Error
    • Maintaining Triggers
  3. PL/SQL Variables and Datatypes
    • Anonymous Blocks
    • Declaring Variables
    • Datatypes
    • Subtypes
    • Character Data
    • Dates and Timestamps
    • Date Intervals
    • Anchored Types
    • Assignment and Conversions
    • Selecting into a Variable
    • Returning into a Variable
  4. PL/SQL Syntax and Logic
    • Conditional Statements – IF/THEN
    • Conditional Statements – CASE
    • Comments and Labels
    • Loops
    • WHILE and FOR Loops
    • SQL in PL/SQL
    • Local Procedures and Functions
  5. Stored Procedures and Functions
    • Stored Subprograms
    • Creating a Stored Procedure
    • Procedure Calls and Parameters
    • Parameter Modes
    • Named Parameter Notation
    • Default Arguments
    • Creating a Stored Function
    • Stored Functions and SQL
    • Invoker’s Rights
  6. Exception Handling
    • Exception Handlers
    • Nesting Blocks
    • Scope and Name Resolution
    • Declaring and Raising Named Exceptions
    • User-Defined Exceptions
  7. Records and Collections
    • Record Variables
    • Using the %ROWTYPE Attribute
    • User-Defined Object Types
    • VARRAY and Nested TABLE Collections
    • Using Nested TABLEs
    • Using VARRAYs
    • Collections in Database Tables
    • Associative Array Collections
    • Collection Methods
    • Iterating Through Collections
  8. Cursors
    • Multi-Row Queries
    • Declaring and Opening Cursors
    • Fetching Rows
    • Closing Cursors
    • The Cursor FOR Loop
    • FOR UPDATE Cursors
    • Cursor Parameters
    • The Implicit (SQL) Cursor
  9. Bulk Operations
    • Bulk Binding
    • BULK COLLECT Clause
    • FORALL Statement
    • FORALL Variations
    • Bulk Returns
    • Bulk Fetching with Cursors
  10. Using Packages
    • Packages
    • Oracle-Supplied Packages
    • The DBMS_OUTPUT Package
    • The DBMS_UTILITY Package
    • The UTL_FILE Package
    • The DBMS_METADATA Package
    • XML Packages
    • Networking Packages
    • Other Supplied Packages
  11. Creating Packages
    • Structure of a Package
    • The Package Interface and Implementation
    • Package Variables and Package State
    • Overloading Package Functions and Procedures
    • Forward Declarations
    • Strong REF CURSOR Variables
    • Weak REF CURSOR Variables
  12. Working with LOBs
    • Large Object Types
    • Oracle Directories
    • LOB Locators
    • Internal LOBs
    • LOB Storage and SECUREFILEs
    • External LOBs
    • Temporary LOBs
    • The DBMS_LOB Package
  13. Maintaining PL/SQL Code
    • Privileges for Stored Programs
    • Data Dictionary
    • PL/SQL Stored Program Compilation
    • Conditional Compilation
    • Compile-Time Warnings
    • The PL/SQL Execution Environment
    • Dependencies and Validation
    • Maintaining Stored Programs
  14. Appendix A – Dynamic SQL
    • Generating SQL at Runtime
    • Native Dynamic SQL vs. DBMS_SQL Package
    • The EXECUTE IMMEDIATE Statement
    • Using Bind Variables
    • Multi-row Dynamic Queries
    • Bulk Operations with Dynamic SQL
    • Using DBMS_SQL
    • DBMS_SQL Subprograms