Oracle Database 12c R2: Program with PL/SQL Ed 2

Duration : 5 Days (40 Hours)

Oracle Database 12c R2: Program with PL/SQL Ed 2 Course Overview:

The Oracle Database 12c R2: Program with PL/SQL certification validates a candidate’s proficiency in utilizing the PL/SQL programming language in conjunction with Oracle’s 12c database. This certification entails mastering the creation, execution, and management of PL/SQL blocks and subprograms, data type manipulation, and exception handling. It also encompasses ensuring data integrity through SQL transaction control and optimizing performance via caching and SQL function results. Industries globally adopt this certification to recruit highly skilled professionals capable of crafting, testing, debugging, and overseeing robust, database-focused PL/SQL applications. This aids in maximizing productivity, performance, scalability, and security within Oracle applications.

Intended Audience:

• Database Administrators
• Database Developers
• Database Designers
• Technical Consultants
• Data Warehouse Administrators
• Data Warehouse Developers
• IT Professionals working with SQL and PL/SQL
• Individuals looking to expand knowledge on Oracle 12c Database SQL and PL/SQL programming

Learning Objectives of Oracle Database 12c R2: Program with PL/SQL Ed 2:

The Oracle Database 12c R2: Program with PL/SQL Ed 2 course is designed to achieve specific learning objectives. Participants will develop the skills and knowledge required to effectively utilize the PL/SQL database language. The course emphasizes a comprehensive understanding of PL/SQL, Oracle’s procedural extension language for SQL and the Oracle relational database. Participants will focus on creating PL/SQL blocks of application code, which can be shared across multiple applications, forms, reports, and data management systems. Additionally, students will learn about managing exceptions, variable declaration, creating anonymous PL/SQL blocks, working with cursors and triggers, and effectively using PL/SQL control structures and other essential functions. The course aims to equip learners with the capabilities to proficiently work with PL/SQL in various database-related tasks.

 Module 1: Introduction

  • Course Objectives
  • Course Agenda
  • Describe the Human Resources (HR) Schema
  • PL/SQL development environments available in this course
  • Introduction to SQL Developer

 Module 2: Introduction to PL/SQL

  • Overview of PL/SQL
  • Identify the benefits of PL/SQL Subprograms
  • Overview of the types of PL/SQL blocks
  • Create a Simple Anonymous Block
  • How to generate output from a PL/SQL Block?

 Module 3: Declare PL/SQL Identifiers

  • List the different Types of Identifiers in a PL/SQL subprogram
  • Usage of the Declarative Section to Define Identifiers
  • Use variables to store data
  • Identify Scalar Data Types
  • The %TYPE Attribute
  • What are Bind Variables?
  • Sequences in PL/SQL Expressions

 Module 4: Write Executable Statements

  • Describe Basic PL/SQL Block Syntax Guidelines
  • Learn to Comment the Code
  • Deployment of SQL Functions in PL/SQL
  • How to convert Data Types?
  • Describe Nested Blocks
  • Identify the Operators in PL/SQL

 Module 5: Interaction with the Oracle Server

  • Invoke SELECT Statements in PL/SQL
  • Retrieve Data in PL/SQL
  • SQL Cursor concept
  • Avoid Errors by using Naming Conventions when using Retrieval and DML Statements
  • Data Manipulation in the Server using PL/SQL
  • Understand the SQL Cursor concept
  • Use SQL Cursor Attributes to Obtain Feedback on DML
  • Save and Discard Transactions

 Module 6: Control Structures

  • Conditional processing using IF Statements
  • Conditional processing using CASE Statements
  • Describe simple Loop Statement
  • Describe While Loop Statement
  • Describe For Loop Statement
  • Use the Continue Statement

 Module 7: Composite Data Types

  • Use PL/SQL Records
  • The %ROWTYPE Attribute
  • Insert and Update with PL/SQL Records
  • INDEX BY Tables
  • Examine INDEX BY Table Methods
  • Use INDEX BY Table of Records

 Module 8: Explicit Cursors

  • What are Explicit Cursors?
  • Declare the Cursor
  • Open the Cursor
  • Fetch data from the Cursor
  • Close the Cursor
  • Cursor FOR loop
  • The %NOTFOUND and %ROWCOUNT Attributes
  • Describe the FOR UPDATE Clause and WHERE CURRENT Clause

 Module 9: Exception Handling

  • Understand Exceptions
  • Handle Exceptions with PL/SQL
  • Trap Predefined Oracle Server Errors
  • Trap Non-Predefined Oracle Server Errors
  • Trap User-Defined Exceptions
  • Propagate Exceptions

 Module 10: Stored Procedures

  • Create a Modularized and Layered Subprogram Design
  • Modularize Development With PL/SQL Blocks
  • Understand the PL/SQL Execution Environment
  • List the benefits of using PL/SQL Subprograms
  • List the differences between Anonymous Blocks and Subprograms
  • Create, Call, and Remove Stored Procedures
  • Implement Procedures Parameters and Parameters Modes
  • View Procedure Information

Oracle Database 12c R2: Program with PL/SQL Ed 2 Course Prerequisites:

• Basic knowledge of Oracle Database SQL certified associate
• Familiarity with relational database concepts
• Experience with basic programming concepts
• Understanding of data retrieval
• Ability to write queries in SQL
• Proficiency in Database management system
• Hands-on experience with Oracle Database 12c R2

Discover the perfect fit for your learning journey

Choose Learning Modality

Live Online

  • Convenience
  • Cost-effective
  • Self-paced learning
  • Scalability


  • Interaction and collaboration
  • Networking opportunities
  • Real-time feedback
  • Personal attention


  • Familiar environment
  • Confidentiality
  • Team building
  • Immediate application

Training Exclusives

This course comes with following benefits:

  • Practice Labs.
  • Get Trained by Certified Trainers.
  • Access to the recordings of your class sessions for 90 days.
  • Digital courseware
  • Experience 24*7 learner support.

Got more questions? We’re all ears and ready to assist!

Request More Details

Please enable JavaScript in your browser to complete this form.

Subscribe to our Newsletter

Please enable JavaScript in your browser to complete this form.