Oracle 12c: PL/SQL Foundations

Live Online & Classroom Enterprise Training

Location: Bangalore, India | Dubai, UAE

Industry Experienced Instructor  |  Remote Labs  |  Lab Access Post Training

Need help finding the right training?

Course Details

In this course, you will learn about the Oracle 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.

Course Prerequisites

Oracle 12c Foundations: SQL and SQL*PLUS (OR103) or equivalent experience

Course Agenda

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

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

3. Working with Database Data

  • SELECTing Single Rows
  • Declaring Variable Datatypes Dynamically
  • Modifying Database Data (DML)
  • Transaction Control Statements

4. Selecting Multiple Rows Using Cursors

  • Declaring Explicit Cursors
  • Implicit Cursor Attributes
  • Using the Cursor FOR LOOP

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

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

7. Introduction to Procedures and Functions

  • Creating Stored PL/SQL Objects, Procedures, Functions

8. Creating Packages

  • Creating Package Specifications and Bodies
  • One Time Only Procedures
  • Persistent State

9. Creating DML Triggers

  • Triggering Events and Trigger Behavior
  • Correlation Identifiers and Multi-statement Triggers
  • Trigger Firing Behavior and Enabling/Disabling Triggers

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

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

13. Bulk-Bind Data Loading Using PL/SQL

  • Defining Bulk Binds
  • Error Handling with Bulk Binds

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

15. Writing Native Dynamic SQL

  • EXECUTE IMMEDIATE

16. PL/SQL Wrapper

  • PL/SQL Wrapper (source code encryption)

17. Understanding Dependencies

  • Viewing Dependencies
  • Effect of Breaking Dependency Chain

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

19. Objects

  • Basic Objects
  • Object Inheritance