[NEW] Oracle PL/SQL - An Ultimate Bootcamp - 21c

Oracle PL/SQL Training Syllabus 

<< Note: Brand New Course Published on 16th Sep 2023 >>

Introduction to PL/SQL

• PL/SQL Overview

• Benefits of PL/SQL Subprograms

• Overview of the Types of PL/SQL blocks

• Create a Simple Anonymous Block

• Generate Output from a PL/SQL Block

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

Write Executable Statements

• Describe Basic PL/SQL Block Syntax Guidelines

• Comment Code

• Deployment of SQL Functions in PL/SQL

• How to convert Data Types?

• Nested Blocks

• Identify the Operators in PL/SQL

Interaction with the Oracle Server

• Invoke SELECT Statements in PL/SQL to Retrieve data.

• Data Manipulation in the Server Using PL/SQL

• SQL Cursor concept

• Usage of SQL Cursor Attributes to Obtain Feedback on DML

• Save and Discard Transactions

Control Structures

• Conditional processing Using IF Statements

• Conditional processing Using CASE Statements

• Use simple Loop Statement

• Use While Loop Statement

• Use For Loop Statement

• Describe the Continue Statement

Explicit Cursors

• What are Explicit Cursors?

• Declare the Cursor

• Open the Cursor

• Fetch data from the Cursor

• Close the Cursor

• Cursor FOR loop

• Explicit Cursor Attributes

• FOR UPDATE Clause and WHERE CURRENT Clause

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

• RAISE_APPLICATION_ERROR Procedure

Stored Procedures and Functions

• Understand Stored Procedures and Functions

• Differentiate between anonymous blocks and subprograms.

• Create a Simple Procedure

• Create a Simple Procedure with IN parameter.

• Create a Simple Function

• Execute a Simple Procedure

• Execute a Simple Function

Create Stored Procedures

• Create a Modularized and Layered Subprogram Design

• Modularize Development With PL/SQL Blocks

• Describe the PL/SQL Execution Environment

• Identity the benefits of Using PL/SQL Subprograms

• List the differences Between Anonymous Blocks and Subprograms

• Create, Call, and Remove Stored Procedures Using the CREATE Command and SQL Developer

• Implement Procedures Parameters and Parameters Modes

• View Procedures Information Using the Data Dictionary Views and SQL Developer

Create Stored Functions

• Create, Call, and Remove a Stored Function Using the CREATE Command and SQL Developer

• Identity the advantages of Using Stored Functions in SQL Statements

• List the steps to create a stored function.

• Implement User-Defined Functions in SQL Statements

• Identity the restrictions when calling Functions from SQL statements.

• Control Side Effects when calling Functions from SQL Expressions

• View Functions Information

Create Packages

• Identity the advantages of Packages

• Describe Packages

• List the components of a Package.

• Develop a Package

• How to enable visibility of a Package's components?

• Create the Package Specification and Body Using the SQL CREATE Statement and SQL Developer

• Invoke Package Constructs

• View PL/SQL Source Code Using the Data Dictionary

Implement Oracle-Supplied Packages in Application Development

• What is Oracle-Supplied Packages?

• Examples of Some of the Oracle-Supplied Packages

• How Does the DBMS_OUTPUT Package Work?

• Use the UTL_FILE Package to Interact With Operating System Files

• Invoke the UTL_MAIL Package

• Write UTL_MAIL Subprograms

Triggers

• Describe Triggers

• Identify the Trigger Event Types and Body

• Business Application Scenarios for Implementing Triggers

• Create DML Triggers Using the CREATE TRIGGER Statement and SQL Developer

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

• Statement Level Triggers Versus Row Level Triggers

• Create Instead of and Disabled Triggers

• How to Manage, Test, and Remove Triggers?

Create Compound, DDL, and Event Database Triggers

• What are Compound Triggers?

• Identify the Timing-Point Sections of a Table Compound Trigger

• Compound Trigger Structure for Tables and Views

• Implement a Compound Trigger to Resolve the Mutating Table Error

• Compare Database Triggers to Stored Procedures

• Create Triggers on DDL Statements

• Create Database-Event and System-Event Triggers

• System Privileges Required to Manage Triggers

Composite Data Types - Collections

• Use PL/SQL Records

• The %ROWTYPE Attribute

• Insert and Update with PL/SQL Records

• Associative Arrays (INDEX BY Tables)

• Examine INDEX BY Table Methods

• Use INDEX BY Table of Records

Dynamic SQL

• The Execution Flow of SQL

• What is Dynamic SQL?

• Declare Cursor Variables

• Dynamically executing a PL/SQL Block

• Configure Native Dynamic SQL to Compile PL/SQL Code

• Invoke DBMS_SQL Package

• Implement DBMS_SQL with a Parameterized DML Statement

• Dynamic SQL Functional Completeness

PL/SQL Advanced

Packages

• Overloading Subprograms in PL/SQL

• Use Forward Declarations to Solve illegal Procedure Reference

• Persistent State of Packages Variables

• Persistent State of a Package Cursor

Manage Dependencies

• Overview of Schema Object Dependencies

• Query Direct Object Dependencies using the USER_DEPENDENCIES View

• Query an Object's Status

• Invalidation of Dependent Objects

• Display the Direct and Indirect Dependencies

• Understand Remote Dependencies

• Recompile a PL/SQL Program Unit

Pipeline functions

• What are pipeline functions?

• Create is pipeline functions.

• Check PGA memory consumption pipeline functions.

• Error handling pipeline functions

The Trainer is an Architect with over 19+ years of experience, he has spent 10+ years working with PL/SQL

An Oracle Certified Professions (OCP)

Oracle Technology Network (OTN) Points: 12,975 Level: Master