Oracle 19c PL/SQL Fundamentals

Course Overview

Selection & Setup of the Database Interface

• Considering Available Tools
• Selecting the Appropriate Tool
• Oracle Net Database Connections
• Oracle PAAS Database Connections
• Setup SQL Developer
• Setup SQL *Plus
• Setup JDeveloper
• About BIND and Substitution Variables
• Using SQL Developer
• Using SQL *Plus

Choosing a Database Programming Language

• What is Database Programming
• PL/SQL Programming
• PL/SQL Performance Advantages
• Integration with Other Languages

PL/SQL Language Essentials

• PL/SQL Program Structure
• Language Syntax Rules
• Embedding SQL
• Writing Readable Code
• Generating Readable Code
• Generating Database Output
• SQL * Plus Input of Program Block

DECLARE Section

• About the Declare Section
• Declare Primitive Types
• Declaration Options
• Not Null
• Constant
• Data Dictionary Integration
• % Type
• Declare Simple User-Defined Types
• Type …  Table
• Type … Record
• Extended User Defined Types

BEGIN Section

• About the Begin Section
• Manipulating Program Data
• Logic Control & Braching
• GOTO
• LOOP
• IF-THEN-ELSE
• CASE

EXCEPTION Section

• About the Exception Section
• Isolating the Specific Exception
• Pragma Exception_INIT
• SQLCODE &SQLERRM Example
• SQL%ROWCOUNT & Select … Into

Beyond the Basics : Explicit Cursors

• About Explicit Cursors
• Extend Cursor Techniques
• For Update of Clause
• Where Current of Clause
• Using for … Loop Cursors

Introduction Database Resident Programming Units

• About Database – Resident Programs
• Physical Storage & Execution
• Types of Stored Program Units
• Stored Program Unit Advantages
• Modular Design Principles

Creating Stored Procedures and Functions

• Stored Procedures & Functions
• Create Procedure / Create Function
• Creating Procedures & Functions
• Raise_Salary() Procedure
• Salary_Valid() function
• The Parameter Specification
• Default Clause
• System & Object Privileges
• Using the Development Tools

Executing Stored Procedures and Functions

• Calling Procedures & Functions
• Unit Testing with Execute
• Anonymous Block Unit Testing
• Specifying a Parameter Notation
• SQL Worksheet Unit Testing
• Calling Functions from SQL

Maintaining Stored Programming Units

• Recompiling Programs
• Mass Recompilation Using UTL_RECOMP()
• Dropping Procedures & Functions
• Drop Procedures & Functions
• Drop Procedure / Function
• Data Dictionary Metadata
• Using USER_OBJECTS
• Using USER_SOURCE
• Using USER_ERRORS
• Using USER_OBJECT_SIZE
• Using USER_DEPENDENCIES

Managing Dependencies

• Dependency Internals
• Tracking Dependencies
• The Dependency Tracking Utility
• SQL Developer Dependency Info
• Dependency Strategy Checklists

Creating & Maintaining

• About Packages
• Creating Packages
• Maintaining Packages
• Performance Considerations

Advanced Package Capabilities

• Definer & Invoker Rights
• White Lists & Accessible By
• Persistent Global Objects
• Defining Initilization Logic
• Object Orientation Support

Advanced Cursor Techniques

• Using Cursor Variables
• Using SYS_REFCURSOR
• Using_Cursor Expressions

Using System Supplied Packages

• DBMS_OUTPUT()
• UTL_FILE()
• FOPEN() Example

Database Trigger Concepts

• About Database Triggers
• DML Event Trigger Sub-Types
• Database Trigger Scenario
• Trigger Exhaustion Mechanisms
• Trigger within SQL Worksheet

Creating Database Triggers

• Statement Level Triggers
• Using Raise Application_Error()
• Row-Level Triggers
• Examples of Triggers
• Employee_Salary_Check Example
• Employee_Journal Example
• Budget_Event Example
• Instead of Triggers
• Triggers within and Application

Maintaining Database Triggers

• Call Syntax
• Trigger Maintenance Tasks
• Show Errors Trigger
• Drop Trigger
• Alter Trigger
• Multiple Triggers for a Table
• Handling Mutating Table Issues

Implementing System Event Triggers

• What are System Event Triggers
• Defining the Scope
• Available System Events
• System Event Attributes

Prerequisites

Attendees should have skills equivalent to the topics in the course listed below:

• Introduction to Writing SQL Queries

Course Comments

NOTE: This training is NOT Official Oracle University training. This is independent, adjustable content that aligns with current topics, skills and tools that participants need to excel in these areas.