Data, Analytics & AI Category Banner Image

Oracle Database PL/SQL (OCPS)

  • Length 3 days
  • Price  NZD 3500 exc GST
Course overview
View dates &
book now

Why study this course

Oracle Database PL/SQL is a complete, hands-on course presenting the fundamental principles of PL/SQL, the Oracle® database’s procedural language extension to SQL.

In this course, you will explore development environments, best practices, and design principles used to create reusable program units, such as procedures and functions used to implement business logic and provide additional data validation.

This course assumes basic SQL knowledge. Oracle Database SQL course or similar knowledge is a recommended prerequisite.

Request Course Information

What you’ll learn

After completing this course, students should be able to:

  • Identify the programming extensions provided by PL/SQL

  • Write code to interface with an Oracle database

  • Use PL/SQL programming constructs to implement business logic

  • Use built-in, and user-defined data types

  • Handle runtime errors

  • Create reusable program units

  • Understand PL/SQL best practices

Oracle corporate PNG logo

Oracle at Lumify Work

Oracle offers a complete enterprise cloud designed to modernise your business, including cloud applications and cloud infrastructure services. Lumify Work offers courses in Oracle Database and web app development so you can utilise the world's leading converged, multimodel database management system in the cloud.

Who is the course for?

Anyone seeking to learn the fundamental principles of PL/SQL, the Oracle database’s procedural language extension to SQL.

Course subjects

  1. Introduction to PL/SQL

    • About PL/SQL

    • PL/SQL Block Structure

    • Anonymous Blocks

    • Enabling Output

  2. Declaring PL/SQL Variables

    • Variables

    • Identifier name requirements

    • Declaration and initialisation

    • SQL and PL/SQL Data Types

    • Data Type Conversion

  3. Writing Executable Statements

    • PL/SQL Block Syntax

    • Comments

    • Use of SQL Functions in PL/SQL

    • Using Sequences in PL/SQL blocks

    • Operators

  4. Using SQL Statements within a PL/SQL block

    • DML statements usage in PL/SQL

    • Implicit cursors

    • Cursor attributes SQL%FOUND, SQL%NOTFOUND, and SQL%ROWCOUNT

  5. Writing Control Structures

    • If Statement

    • Case Expressions

    • Basic Loop

    • For Loop

    • While Loop

    • Continue and Exit

  6. Working with Composite Data Types

    • PL/SQL records

    • %ROWTYPE attribute

    • Introduction to Collections

  7. Using Explicit Cursors

    • Controlling Explicit Cursors

    • Cursor declaration

    • Fetching Data from cursors

    • Cursor For Loops

    • Explicit Cursor Attributes

  8. Exceptions Handling

    • Understanding Exception with PL/SQL

    • Trapping an Exception


    • Predefined Exceptions


  9. Stored Procedures

    • Benefits of code modularisation

    • Stored Procedure definition

    • Creating and compiling procedures

    • Calling a procedure

    • IN, OUT, and IN OUT parameter modes

    • Default parameters

  10. Functions

    • Functions definition

    • Procedures and Functions comparison

    • Creating and invoking a function

    • Calling User-Defined functions in SQL statements

  11. PL/SQL Packages

    • Advantages of Packages

    • Package specification

    • Package body

    • Invoking package subprograms

  12. Database Triggers

    • Defining triggers

    • Trigger event types

    • Using conditional predicates

    • CALL statements in triggers

    • Statement-level and row-level triggers

    • Using OLD and NEW qualifiers

    • INSTEAD OF triggers


  • This course assumes basic SQL knowledge

  • Oracle Database SQL course or similar knowledge is recommended

Terms & Conditions

The supply of this course by Lumify Work is governed by the booking terms and conditions. Please read the terms and conditions carefully before enrolling in this course, as enrolment in the course is conditional on acceptance of these terms and conditions.

Request Course Information

Personalise your schedule with Lumify USchedule

Interested in a course that we have not yet scheduled? Get in touch, and ask for your preferred date and time. We can work together to make it happen.