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

Live Online & Classroom Enterprise Certification Training

This Database Program with PL/SQL training shows you how to develop stored procedures, functions, packages and database triggers. You'll learn to manage PL/SQL program units and dependencies, while using of some of the Oracle-supplied packages.

Looking for a private batch ?

REQUEST A CALLBACK

Certified Trainer
Authorized Courseware
Completion Certificate from ATP

Enterprise Reporting
Lifetime Access
CloudLabs
24x7 Support
Real-time code analysis and feedback

What is Program with PL/SQL Ed 2 Certification Training about?

This Oracle Database: Program with PL/SQL training starts with an introduction to PL/SQL and then explores the benefits of this powerful programming language. Through hands-on instruction from expert Oracle instructors, you'll learn to develop stored procedures, functions, packages and more.

Ensure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver a higher quality of service by enabling consolidation onto database clouds.

You will use Oracle SQL Developer to develop these program units. SQL*Plus is introduced in this course as optional tools.

Note: This course is a combination of Oracle Database: PL/SQL Fundamentals and Oracle Database: Develop PL/SQL Program Units courses.

What are the objectives of Program with PL/SQL Ed 2 Certification Training ?

Manage dependencies between PL/SQL subprograms
Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
Create stored procedures and functions
Use conditional compilation to customize the functionality in a PL/SQL application without removing any source code
Use the Oracle supplied PL/SQL packages to generate screen output, file output and mail output
Write dynamic SQL for more coding flexibility
Create overloaded package subprograms for more flexibility
Create triggers to solve business challenges
Create and debug stored procedures and functions
Describe the features and syntax of PL/SQL
Design PL/SQL anonymous blocks that execute efficiently
Design PL/SQL packages to group related constructs
Handle runtime errors

Who is Program with PL/SQL Ed 2 Certification Training for?

Application Developers
Database Administrators
System Analysts
Forms Developer
Developer
Technical Consultant
Portal Developer
PL/SQL Developer

What are the prerequisites for Program with PL/SQL Ed 2 Certification Training?

Suggested Prerequisite

Oracle Database: Introduction to SQL
Previous programming experience

Required Prerequisite

Oracle Database: SQL Workshop II Ed 2
Oracle Database: SQL Workshop I Ed 2

Available Training Modes

Live Online Training

40 Hours

Classroom Training

5 Days

Course Outline Expand All

Expand All

Introduction

PL/SQL development environments available in this course

Introduction to SQL Developer

Course Objectives

Describe the Human Resources (HR) Schema

Course Agenda

Working with Oracle Cloud Exadata Express Cloud Service

Introduction to Oracle Database Exadata Express Cloud Service

Connecting to Exadata Express using Database Clients

Accessing Cloud Database using SQL Workshop

Introduction to PL/SQL

Identify the benefits of PL/SQL Subprograms

Create a Simple Anonymous Block

Overview of PL/SQL

How to generate output from a PL/SQL Block?

Overview of the types of PL/SQL blocks

Declare PL/SQL Variables

Sequences in PL/SQL Expressions

The %TYPE Attribute

Use variables to store data

List the different Types of Identifiers in a PL/SQL subprogram

What are Bind Variables?

Identify Scalar Data Types

Usage of the Declarative Section to Define Identifiers

Write Anonymous PL/SQL Blocks

Describe Nested Blocks

Describe Basic PL/SQL Block Syntax Guidelines

How to convert Data Types?

Learn to Comment the Code

Identify the Operators in PL/SQL

Deployment of SQL Functions in PL/SQL

SQL Statements in a PL/SQL block

Retrieve Data in PL/SQL

Data Manipulation in the Server using PL/SQL

Avoid Errors by using Naming Conventions when using Retrieval and DML Statements

Understand the SQL Cursor concept

Invoke SELECT Statements in PL/SQL

Use SQL Cursor Attributes to Obtain Feedback on DML

Save and Discard Transactions

SQL Cursor concept

Control Structures

Conditional processing using IF Statements

Conditional processing using CASE Statements

Use the Continue Statement

Describe While Loop Statement

Describe simple Loop Statement

Describe For Loop Statement

Composite Data Types

The %ROWTYPE Attribute

Use PL/SQL Records

Insert and Update with PL/SQL Records

Examine INDEX BY Table Methods

INDEX BY Tables

Use INDEX BY Table of Records

Explicit Cursors

Describe the FOR UPDATE Clause and WHERE CURRENT Clause

Declare the Cursor

What are Explicit Cursors?

Fetch data from the Cursor

Cursor FOR loop

Close the Cursor

The %NOTFOUND and %ROWCOUNT Attributes

Open the Cursor

Exception Handling

Trap User-Defined Exceptions

Trap Non-Predefined Oracle Server Errors

Understand Exceptions

RAISE_APPLICATION_ERROR Procedure

Propagate Exceptions

Trap Predefined Oracle Server Errors

Handle Exceptions with PL/SQL

Stored Procedures

List the benefits of using PL/SQL Subprograms

List the differences between Anonymous Blocks and Subprograms

Create a Modularized and Layered Subprogram Design

Implement Procedures Parameters and Parameters Modes

Create, Call, and Remove Stored Procedures

Modularize Development With PL/SQL Blocks

Understand the PL/SQL Execution Environment

View Procedure Information

Stored Functions

Identify the steps to create a stored function

Control side effects when calling Functions

Create, Call, and Remove a Stored Function

View Functions Information

Restrictions when calling Functions

Identify the advantages of using Stored Functions

Invoke User-Defined Functions in SQL Statements

Debugging Subprograms

Debugging through SQL Developer

How to debug Functions and Procedures?

Packages

Describe Packages

What are the components of a Package?

Develop a Package

Invoke the Package Constructs

View the PL/SQL Source Code using the Data Dictionary

How to enable visibility of a Packages Components?

Listing the advantages of Packages

Create the Package Specification and Body using the SQL CREATE Statement and SQL Developer

Deploying Packages

Persistent State of a Package Cursor

Overloading Subprograms in PL/SQL

Use Forward Declarations to solve Illegal Procedure Reference

Control side effects of PL/SQL Subprograms

Use the STANDARD Package

Persistent State of Packages

Invoke PL/SQL Tables of Records in Packages

Implement Package Functions in SQL and Restrictions

Implement Oracle-Supplied Packages in Application Development

Examples of some of the Oracle-Supplied Packages

Invoke the UTL_MAIL Package

Use the UTL_FILE Package to Interact with Operating System Files

How does the DBMS_OUTPUT Package work?

What are Oracle-Supplied Packages?

Write UTL_MAIL Subprograms

Dynamic SQL

The Execution Flow of SQL

Declare Cursor Variables

Configure Native Dynamic SQL to Compile PL/SQL Code

Dynamic SQL Functional Completeness

What is Dynamic SQL?

Dynamically Executing a PL/SQL Block

How to invoke DBMS_SQL Package?

Implement DBMS_SQL with a Parameterized DML Statement

Design Considerations for PL/SQL Code

Understand Local Subprograms

Implement the NOCOPY Compiler Hint

The Cross-Session PL/SQL Function Result Cache

Usage of Bulk Binding to Improve Performance

Standardize Constants and Exceptions

The DETERMINISTIC Clause with Functions

Write Autonomous Transactions

Invoke the PARALLEL_ENABLE Hint

Triggers

Identify the Trigger Event Types and Body

How to Manage, Test and Remove Triggers?

Identify the Trigger Event Types, Body, and Firing (Timing)

Differences between Statement Level Triggers and Row Level Triggers

Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer

Describe Triggers

Business Application Scenarios for Implementing Triggers

Create Instead of and Disabled Triggers

Creating Compound, DDL, and Event Database Triggers

Implement a Compound Trigger to Resolve the Mutating Table Error

Identify the Timing-Point Sections of a Table Compound Trigger

Comparison of Database Triggers to Stored Procedures

What are Compound Triggers?

Create Database-Event and System-Events Triggers

Understand the Compound Trigger Structure for Tables and Views

System Privileges Required to Manage Triggers

Create Triggers on DDL Statements

PL/SQL Compiler

Overview of PL/SQL Compile Time Warnings for Subprograms

List the PL/SQL Compile Time Warning Messages Categories

Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBMS_WARNING Package Subprograms

View Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views

List the benefits of Compiler Warnings

What is the PL/SQL Compiler?

Describe the Initialization Parameters for PL/SQL Compilation

List the new PL/SQL Compile Time Warnings

Manage Dependencies

Query Direct Object Dependencies using the USER_DEPENDENCIES View

Overview of Schema Object Dependencies

Invalidation of Dependent Objects

Recompile a PL/SQL Program Unit

Understand Remote Dependencies

Query an Objects Status

Fine-Grained Dependency Management in Oracle Database 12c

Display the Direct and Indirect Dependencies

Who is the instructor for this training?

The Trainer is Oracle certified Instructor with extensive domain experience, including years of experience training & mentoring professionals in the industry.

Program with PL/SQL Ed 2 Certification Training - Certification & Exam

SpringPeople is the Authorized Training Partner of Oracle.
The training fees is exclusive of exam cost.
For any queries; feel free to reach us at oracle@springpeople.com

Reviews

My outlook on training changed completely after attending SpringPeople BPC training. The content, the trainer and infrastructure at SpringPeople were top notch and perfectly in tune with the industry requirements. Regardless to say, training is now something that I look forward to to. Kudos to everyone at SpringPeople!

Shweta Priya

Sony

I attended the 3-day AngularJs training at SpringPeople. The trainer was an industry veteran with vast experience in the subject. Notably, the hands-on training, and the Q&A session stood out. Overall, I found SpringPeople a great place to learn with excellent facilities and great trainers. Would recommend SpringPeople to my colleagues and friends.

Swati Singh

I attended the training on API Design for Mulesoft. The sessions were well planned and value-laden. I benefited immensely from the hands-on experience enabled through virtual labs. I would like to specifically commend the efficiency of the support team who were always available to resolve my concerns.

Nikhil Kohli

Stryker

I attended the jQuery training batch, conducted by Mr. Vijay, an SME who did a thorough coverage of all the essentials. He took us through concepts such as jQuery animations, event handlers, plugins, and jQuery-UI by small programs, very easily. The sessions were useful and well structured. By the end of the training, I was well equipped to develop a SPA on Product Management System. Overall, the learning experience at SpringPeople was great!

Heena Rajan

Mindtree