Oracle Database 12c: PL/SQL Fundamentals I and II
CLASS DATE(s):
Request a Class

COURSE LENGTH: 5 Days

COURSE COST: $3295.00

COURSE TIMES: 9:00am - 4:30pm

Printable version of this course
print

COURSE OVERVIEW

This 5 day training begins with an explanation of the intent and usage of the PL/SQL programming language for database applications. Important reasons why one should incorporate PL/SQL modules within the application architecture right from the initial design and planning phase are presented. Next one learns how to begin building executable PL/SQL program units. One learns about each of the major segments of a working program and how these interact with each other during program execution, including the important error or exception handling capabilities of the language.

Part 2 - Explains how database-resident program units can be used as part of the overall database application architecture and the benefits realized by doing so. It then builds upon one's knowledge of database-resident program units and applies these to the development of PL/SQL packages.

AUDIENCE AND PREREQUISITES

The target audience for this textbook is all Oracle professionals. Among the specific groups for whom this textbook will be helpful are:

• Application designers and database developers
• Database administrators
• Web server administrators


Subjects applicable to certification as both an Oracle PL/SQL Developer Certified Associate (OCA) and an Oracle Advanced PL/SQL Developer Certified Professional (OCP). The topics considered are included within "Exam 1Z0-144: Program With PL/SQL".

Oracle Database 12c: SQL - Fundamentals (Levels I & II) or Oracle Database 12c: SQL (Levels I, II & III) are recommended.


  

*Course Cost listed does not include the cost of courseware. Please refer to your Invoice for any additional costs. If you have any questions, please contact us at learn@vtec.org or 207-775-0244. Course is subject to minimum enrollment to run. If the minimum is not met this class may run as a Virtual Instructor Led class.

COURSE TOPICS:


12c PL/SQL I

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 & SUBSTITUTION Variables
Using SQL Developer
Using SQL*PLUS

Choosing A Database Programming Language
What Is Database Programming?
PL/SQL Performance Advantages
Integration With Other Languages

PL/SQL Language Fundamentals
PL/SQL Program Structure
Language Syntax Rules
Embedding SQL
Writing Readable Code
Generating Database Output
SQL*PLUS Input Of A 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 & Branching
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
Extended Cursor Techniques
FOR UPDATE OF Clause
WHERE CURRENT OF Clause
Using FOR…LOOP Cursors

Beyond The Basics: Nested Blocks

Beyond The Basics: DECLARED Subprograms
Using DECLARED Subprograms
DECLARED Procedure
DECLARED Function

PL/SQL II – Intermediate: Develop Program

Introducing Database-Resident Program Units
About Database-Resident Programs
Physical Storage & Execution
Types Of Stored Program Units
Stored Program Unit Advantages
Modular Design Principles

Creating Stored Procedures & 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 & 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 Program Units
Recompiling Programs
Mass Recompilation Using UTL_RECOMP()
Dropping 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

Creating & Maintaining Packages
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 Execution Mechanisms
TRIGGERS 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 An 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