Course Details:
Oracle 19c PL/SQL Fundamentals

<< Back to courses

Course Overview:

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.

View Course Details

Course Description:

Audience profile
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).

At course completion
After completing this course, students will be able to:

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 working 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.

Course Outline

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 Initialization 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

Course Title

Oracle 19c PL/SQL Fundamentals

Course Number

TTOR12019

Course Outline

Click here to open course outline

Duration

3 days

Price

$2195.00

Course Details:Oracle 19c PL/SQL Fundamentals

Course Overview:

Course Description:

Course Details:
Oracle 19c PL/SQL Fundamentals