top of page


Time:  40 Hours




Prior database basic concepts such as store/read/update/delete records. Prior exposure to object-oriented programming concepts is not required, but definitely beneficial.


Course overview:


The Oracle PL/SQL language is a flexible procedural extension to SQL and increases productivity, performance, scalability, portability and security. In this course, you will gain the practical knowledge to write PL/SQL programs. You will learn to build stored procedures, design and execute modular applications, and increase the efficiency of data movement.


Course Content:

Introduction and Overview


PL/SQL fundamentals

  • Declaring and anchoring variables to database definitions

  • Flow control constructs

  • DML commands : Select, Insert, Update, Delete

  • DDL commands: Create, Alter Tables/Views/Sequence

  • TCL commands: Grant, Revoke

  • Joins

  • Trigger

  • Stored procedure



Oracle 11g and 12c PL/SQL features

  • PL/SQL in Oracle 11g

  • Returning implicit cursor results from stored procedures in Oracle 12c

  • Declaring local functions within SELECT statements in Oracle 12c


Data Manipulation Techniques

  • Maintaining data with DML statements

  • Employing the RETURNING INTO clause

  • Solving the fetch-across-commit problem


Managing data retrieval with cursors

  • Implications of explicit and implicit cursors

  • Simplifying cursor processing with cursor FOR LOOPs

  • Embedding cursor expressions in SELECT statements


Cursor variables

  • Strong vs. weak cursor variables

  • Passing cursor variables to other programs

  • Defining REF CURSORS in packages

  • Developing Well-Structured and Error-Free Code


Error handling using EXCEPTIONs

  • Propagation and scope

  • "Retrying" problem transactions with EXCEPTION processing


Debugging PL/SQL blocks

  • Simplifying testing and debugging with conditional compilation

  • Interpreting compiler messages

  • Applying structured testing techniques

  • Building and applying a test bed

  • Leveraging the debugging facilities in SQL Developer

  • Achieving Maximum Reusability


Writing stored procedures and functions

  • Calling and invoking server-side logic

  • Passing input and output parameters

  • Implementing an autonomous transaction


Coding user-written functions

  • Calling PL/SQL functions from SQL

  • Building table-valued functions


Developing safe triggers

  • Employing :OLD and :NEW variables

  • Avoiding unreliable trigger constructs

  • Exploiting schema and database triggers


Exploiting Complex Datatypes

  • Collection types

  • PL/SQL tables, nested tables, VARRAYs

  • Stepping through dense and nonconsecutive collections


Bulk binding for high performance

  • Moving data into and out of PL/SQL blocks


  • BULK cursor attributes

  • BULK EXCEPTION handling

  • Invoking Native Dynamic SQL


Finessing the compiler

  • The EXECUTE IMMEDIATE statement

  • The RETURNING INTO clause


Types of dynamic SQL

  • Building SQL statements during runtime

  • Autogenerating standard code


Package Tips and Techniques

  • Package structure: SPEC and BODY

  • Eliminating dependency problems

  • Overloading for polymorphic effects

  • Evaluating application frameworks

  • Declaring and using persistent global variables


Who should go for this course?


Programmers and others working with PL/SQL who have a working knowledge of SQL


Special notes:


  • Hands-on labs for each topic

  • Real life projects at the course completion



For more info contact ITEXPS

bottom of page