651-905-3729 Microsoft Silver Learning Partner EC Counsel Reseller compTIA Authorized Partner

Oracle 19c PL/SQL Fundamentals Virtual Classroom Live March 24, 2025

Price: $2,100

This course runs for a duration of 3 Days.

The class will run daily from 10:00 AM EST to 6:00 PM EST.

Class Location: Virtual LIVE Instructor Led - Virtual Live Classroom.

Enroll today to reserve your spot!

Space is limited. Enroll today.

Enroll Now

Description

Oracle 19c PL/SQL Fundamentals is a three-day, hands-on technical course that introduces Oracle database programming using the PL/SQL programming language. Throughout the course students will explore the core syntax, structure and features of the language. This course will also lay the foundation for the entire Oracle PL/SQL programming series, allowing one to progress from introductory topics to advanced application design and programming and finally onto writing complex high-performance applications. The course also explores applying the newly learned skills to the development of database applications. Participants will learn how to use database-resident stored program units such as procedures, functions, packages and database triggers. Students will also learn about the latest features in Oracle 19c.

Course Objectives

Working within in a hands-on learning environment, guided by our expert team, attendees will develop a practical approach to Oracle Database Technology. Throughout the course participants will explore:
•    Using PL/SQL programming language for database applications and development
•    incorporating PL/SQL modules within the application architecture from the initial design and planning phase
•    The essentials of building executable PL/SQL program units
•    Each of the major segments of a workin g program and how these interact with each other during program execution,
•    Important error or exception handling capabilities of the language.
•    How database-resident program units can be used as part of the overall database application architecture
•    Applying these new skills to the development of PL/SQL packages.
•    Advanced database programming capabilities and benefits
•    How database triggers can be used as part of an advanced database application design

Audience:

This course is geared for anyone needing to interface with an Oracle database such as end users, business analysts, application developers and database administrators / DBAs. Attendees should have incoming basic SQL experience, as well as an understanding of databases (required to manipulate it with PL/SQL).

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:

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.