Oracle 18c PL/SQL (Five Days)

Course: OR18PLS5

Duration: 5 Days

Level: I

Course Summary

This course provides a complete, hands-on, comprehensive introduction to PL/SQL including the use of both SQL Developer and SQL*Plus. This coverage is appropriate for both Oracle11g and higher.

« Hide The Details

Topics Covered In This Course

CHAPTER 1 PL/SQL PROGRAM STRUCTURE

PL/SQL VS. SQL

PL/SQL ENGINES AVAILABLE

ANONYMOUS PL/SQL BLOCK STRUCTURE

OBJECT NAMING RULES

VARIABLE DECLARATIONS

AVAILABLE DATATYPES

Scalar Datatypes
Using Extended Datatypes
Object Types

EXECUTABLE STATEMENTS

EXPRESSIONS

PL/SQL QUALIFIED EXPRESSIONS

BLOCK LABELING

VARIABLE SCOPING RULES

COMMENTS IN PROGRAMS AND SCRIPTS

BASIC CODING STANDARDS

LAB 1: PL/SQL PROGRAM STRUCTURE

LAB 1 SOLUTIONS: PL/SQL PROGRAM STRUCTURE

CHAPTER 2 PL/SQL FLOW CONTROL

CONDITIONAL CONTROL

COMPARISON OPERATORS

LOGICAL OPERATORS

Truth Tables

REPETITION CONTROL

The Simple Loop
WHILE Loop
FOR Loop
CONTINUE Statements In Loops
Step Loops

THE GOTO STATEMENT

CASE EXPRESSIONS / STATEMENTS

BIND VARIABLES

SUBSTITUTION VARIABLES

LAB 2: PL/SQL FLOW CONTROL

LAB 2 SOLUTIONS: PL/SQL FLOW CONTROL

CHAPTER 3 SQL DEVELOPER AND PL/SQL

SQL DEVELOPER AND PL/SQL

CREATING AND EXECUTING SCRIPTS

LAB 3: SQL DEVELOPER AND PL/SQL

LAB 3 SOLUTIONS: SQL DEVELOPER AND PL/SQL

CHAPTER 4 SELECT INTO

SELECTING SINGLE ROWS OF DATA

ANCHORING VARIABLES TO DATATYPES

DML IN PL/SQL

RETURNING ? INTO

SEQUENCES IN PL/SQL

TRANSACTION CONTROL IN PL/SQL

AUTONOMOUS TRANSACTIONS

LAB 4: SELECT INTO

LAB 4 SOLUTIONS: SELECT INTO

CHAPTER 5 THE PL/SQL CURSOR

DECLARING EXPLICIT CURSORS

OPENING AND CLOSING EXPLICIT CURSORS

USING EXPLICIT CURSORS TO RETRIEVE VALUES

EXPLICIT CURSOR ATTRIBUTES

USING A LOOP WITH AN EXPLICIT CURSOR

USING %ROWTYPE WITH CURSORS

THE CURSOR FOR LOOP

DBMS_OUTPUT

LAB 5: THE PL/SQL CURSOR

LAB 5 SOLUTIONS: THE PL/SQL CURSOR

CHAPTER 6 OPTIMIZATION

TIMING PL/SQL

FOR UPDATE / WHERE CURRENT OF

LAB 6: OPTIMIZATION

LAB 6 SOLUTIONS: OPTIMIZATION

CHAPTER 7 PL/SQL EXCEPTION HANDLING

THE EXCEPTION SECTION

ORACLE NAMED EXCEPTIONS

PRAGMA EXCEPTION_INIT

USER DEFINED EXCEPTIONS

The Scope Of User-Defined Exceptions

RAISING NAMED EXCEPTIONS

EXCEPTION PROPAGATION

RAISING AN EXCEPTION AGAIN

LIFE AFTER AN EXCEPTION

WHEN OTHERS

TAKING YOUR BALL AND GOING HOME

DBMS_ERRLOG

LAB 7: PL/SQL EXCEPTION HANDLING

LAB 7 SOLUTIONS: PL/SQL EXCEPTION HANDLING

CHAPTER 8 STORED PROCEDURES

PROCEDURES

BENEFITS OF STORED PROCEDURES

Database Security
Performance
Productivity
Portability

PARAMETERS AND STORED PROCEDURES

Parameter Notation

STORED OBJECT CREATION

Syntax For Creating A Procedure

COMPILATION ERRORS

VIEWING COMPILED CODE

DROPPING A PROCEDURE

THE ALTER COMMAND AND STORED PROCEDURES

LAB 8: STORED PROCEDURES

LAB 8 SOLUTIONS: STORED PROCEDURES

CHAPTER 9 CREATING FUNCTIONS IN PL/SQL

FUNCTIONS

PURITY LEVELS

USING WHITE LISTS

OPTIMIZATIONS

PARALLEL_ENABLE

DETERMINISTIC FUNCTIONS

PL/SQL RESULT CACHE

NOCOPY

DBMS_OUTPUT IN FUNCTIONS

USING THE WITH CLAUSE FOR FUNCTIONS

PRAGMA UDF

PRAGMA INLINE

THE IMPACT OF DATA-BOUND COLLATION

USING SQL DEVELOPER WITH STORED PROCEDURES

DEBUGGING

LAB 9: FUNCTIONS

LAB 9 SOLUTIONS: FUNCTIONS

CHAPTER 10 PACKAGES

CREATING PACKAGES

PACKAGE BENEFITS

Security
Persistent State
I/O Efficiency

A SIMPLE PACKAGE

OVERLOADING

BODILESS PACKAGES

SOURCE CODE ENCRYPTION

CREATING PACKAGES FROM PROCEDURES AND FUNCTIONS

LAB 10: PACKAGES

LAB 10 SOLUTIONS: PACKAGES

CHAPTER 11 CREATING DML TRIGGERS

DML TRIGGERS

DML TRIGGER STRUCTURE

CONDITIONAL TRIGGERING PREDICATES

TRIGGERS FOR BUSINESS RULES ENFORCEMENT

MUTATING AND CONSTRAINING TABLES

COMPOUND TRIGGERS

CONTROLLING FIRING ORDER

DDL FOR TRIGGERS

VIEWING TRIGGER SOURCE

INSTEAD OF TRIGGERS

LAB 11: DML TRIGGERS

LAB 11 SOLUTIONS: DML TRIGGERS

CHAPTER 12 ADVANCED CONCEPTS

EMBEDDED PROCEDURES

THE OPTIMIZING COMPILER

PL/SQL COMPILER WARNINGS

COMPILING FOR DEBUGGING

CONDITIONAL COMPILATION / INQUIRY DIRECTIVES

Error Directives
Inquiry Directives
Using Static Constants

DBMS_DB_VERSION

NATIVE COMPILATION

Recompiling All Database Objects

LAB 12: ADVANCED CONCEPTS

LAB 12 SOLUTIONS: ADVANCED CONCEPTS

CHAPTER 13 FILE OPERATIONS

MOVING FILES BETWEEN DATABASES

DIRECTORY ACCESS

FILE MANIPULATION

FCLOSE Procedure
FCLOSE_ALL Procedure
FCOPY Procedure
FFLUSH Procedure
FGETATTR Procedure
FGETPOS Function
FOPEN Function
FREMOVE Procedure
FRENAME Procedure
FSEEK Procedure
GET_LINE Procedure
GET_RAW Procedure
IS_OPEN Function
NEW_LINE Procedure
PUT Procedure
PUT_LINE Procedure
PUTF Procedure
PUT_RAW Procedure

LAB 13: FILE OPERATIONS

LAB 13 SOLUTIONS: FILE OPERATIONS

CHAPTER 14 COMMUNICATIONS

DBMS_ALERT

REGISTER Procedure
REMOVE Procedure
REMOVEALL Procedure
SET_DEFAULTS Procedure
SIGNAL Procedure
WAITANY Procedure
WAITONE Procedure
Security
DBMS_ALERT Example

DBMS_PIPE

CREATE_PIPE Function
NEXT_ITEM_TYPE Function
PACK_MESSAGE Procedure
PURGE Procedure
RECEIVE_MESSAGE Function
RESET_BUFFER Procedure
REMOVE_PIPE Function
SEND_MESSAGE Function
UNIQUE_SESSION_NAME Function
UNPACK_MESSAGE Procedure
DBMS_PIPE Example

UTL_SMTP

UTL_HTTP

UTL_TCP

UTL_MAIL

DBMS_NETWORK_ACL_ADMIN

LAB 14: COMMUNICATIONS

LAB 14 SOLUTIONS: COMMUNICATIONS

CHAPTER 15 SECURITY

DBMS_RLS

Implementation Tasks
Our Example Scenario
The Policy Package
The Dynamic Predicate Package
Creating The Context And The Trigger
Using DBMS_RLS
Exempting Policies

DBMS_FGA

CONTROLLING COLUMN ACCESS WITH VIRTUAL COLUMNS

LAB 15: SECURITY

LAB 15 SOLUTIONS: SECURITY

CHAPTER 16 SCHEDULING

ORACLE SCHEDULING

DBMS_JOB

PROCEDURE BROKEN
PROCEDURE CHANGE
PROCEDURE INSTANCE
PROCEDURE INTERVAL
PROCEDURE ISUBMIT
FUNCTION IS_JOBQ
PROCEDURE NEXT_DATE
PROCEDURE REMOVE
PROCEDURE RUN
PROCEDURE SUBMIT
PROCEDURE USER_EXPORT
PROCEDURE USER_EXPORT
PROCEDURE WHAT
The NEXT_DATE Parameter
The INTERVAL Parameter
The WHAT Parameter

DBMS_SCHEDULER

Evaluating Calendaring Expressions
DBMS_SCHEDULER Capabilities

LAB 16: SCHEDULING

LAB 16 SOLUTIONS: SCHEDULING

CHAPTER 17 MISCELLANEOUS PACKAGES

DBMS_OUTPUT

DBMS_RANDOM

STRING Function
VALUE Function

DBMS_STATS

GATHER_SCHEMA_STATS
GATHER_TABLE_STATS

DBMS_WM

DBMS_METADATA

DBMS_REDEFINITION

OTHER NOTABLE PACKAGES / PROCEDURES

MISCELLANEOUS CHANGES IN 12CR2

LAB 17: MISCELLANEOUS PACKAGES

LAB 17 SOLUTIONS: MISCELLANEOUS PACKAGES

CHAPTER 18 DATABASE TRIGGERS

PERMISSIONS NEEDED

DDL TRIGGERS

SERVERERROR TRIGGERS

LOGON / LOGOFF TRIGGERS

STARTUP / SHUTDOWN TRIGGERS

SUSPEND TRIGGERS

LAB 18: DATABASE TRIGGERS

LAB 18 SOLUTIONS: DATABASE TRIGGERS

CHAPTER 19 COLLECTIONS

DEFINING RECORDS

COLLECTIONS

Associative Arrays
Nested Tables
VARRAYs / VARYING ARRAYs
Assignments
Comparing Collections

COLLECTION METHODS

EXISTS
FIRST
LAST
COUNT
LIMIT
PRIOR
NEXT
DELETE
TRIM

SET THEORY AND NESTED TABLES

LAB 19: COLLECTIONS

LAB 19 SOLUTIONS: COLLECTIONS

CHAPTER 20 BULK OPERATIONS

BULK BINDING

FORALL

SQL%BULK_ROWCOUNT

SAVE EXCEPTIONS / SQL%BULK_EXCEPTIONS

BULK COLLECT

The LIMIT Clause
FORALL And The INDICES OF Clause
FORALL And VALUES OF

PIPELINED TABLE FUNCTIONS

MULTIDIMENSIONAL COLLECTIONS

LAB 20: BULK OPERATIONS

LAB 20 SOLUTIONS: BULK OPERATIONS

CHAPTER 21 CURSOR VARIABLES

WHAT IS A CURSOR VARIABLE?

CURSOR VARIABLE CONTROL

OPEN FOR

FETCH

CLOSE

CURSOR VARIABLE EXAMPLES

ROWTYPE_MISMATCH

LAB 21: CURSOR VARIABLES

LAB 21 SOLUTIONS: CURSOR VARIABLES

CHAPTER 22 DYNAMIC SQL

STANDARD DYNAMIC SQL

NEW DBMS_SQL FUNCTIONALITY

NATIVE DYNAMIC SQL (NDS)

EXECUTE IMMEDIATE

OPEN FOR, FETCH and CLOSE
Additional Rules For Native Dynamic SQL

BULK EXECUTE IMMEDIATE

SQL INJECTION ATTACKS

SQL Statement Manipulation
Additional Statement Insertion
The USING Clause Vs. Concatenation
DBMS_ASSERT

SWITCHING BETWEEN NDS AND DBMS_SQL

LAB 22: DYNAMIC SQL

LAB 22 SOLUTIONS: DYNAMIC SQL

CHAPTER 23 LARGE OBJECTS

EXTERNAL VS. INTERNAL LARGE OBJECTS

INITIALIZING INTERNAL LOB LOCATORS

THE DBMS_LOB PACKAGE

INSERTING INTERNAL LARGE OBJECTS

LOADING ERRORS

THE SECUREFILE OPTION

LAB 23: LARGE OBJECTS

LAB 23 SOLUTIONS: LARGE OBJECTS

CHAPTER 24 OBJECT-ORIENTED PROGRAMMING

ORACLE OBJECTS

ORDERING OBJECT TYPES

OBJECT TABLES

SQL FOR OBJECT TABLES

ADVANCED OBJECT CONCEPTS

LAB 24: OBJECT-ORIENTED PROGRAMMING

LAB 24 SOLUTIONS: OBJECT-ORIENTED PROGRAMMING

CHAPTER 25 JAVA AND OTHER LANGUAGES

ALTERNATIVE LANGUAGES

LOADING CODE

PUBLISHING CODE

EXECUTING THE CODE

INTERFACING WITH C

JSON SUPPORT

SODA

Simple Oracle Document Access For PL/SQL
Simple Oracle Document Access For C And C+

ENHANCED LANGUAGE INTERFACE FEATUR

Who Should Take This Course

This course is appropriate for anyone needing to understand Oracle?s proprietary programming language. That would include end users, business analysts, application developers and database administrators.

Recommended Prerequisites

Oracle12c SQL or higher or higher or equivalent experience is required.

Training Style

Lecture and Hands On Labs

« Hide The Details

Related Courses

Code	Course Title	Duration	Level
OR18SQL	Oracle18c SQL	5 Days	I	Details
OR18DBA3	Oracle 18c DBA I (Three Days)	3 Days	I	Details
OR18SQL3	Oracle18c SQL (Three Days)	3 Days	I	Details
OR12E	Oracle12c Release 1 SQL And PL/SQL (Five Days)	5 Days	I	Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.

Notify me the next time this course is confirmed!

Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]