Description

Duration

3 Days

18 CPD hours

This course is intended for

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.

Overview

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

Oracle 19c PL/SQL Fundamentals is a three-day, hands-on 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.

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

Course Images

Oracle 19c PL/SQL Fundamentals (TTOR12019)

By Nexus Human

Booking options

Highlights

Description

About The Provider

Tags

Reviews