Oracle 12c Release 1 PL/SQL Development Training

 

Oracle 18c PL/SQL Development – 5 Days

Course Description – Oracle 18c PL/SQL Development

This Oracle 18c PL/SQL training 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 Oracle 11g and higher.

Intended Audience

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.

Prerequisites

Oracle 12c SQL or equivalent experience is required.

Outline

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 FEATURES

Other courses to explore:

Data Modeling: Logical Database Design – Onsite, Tailored

Oracle 12c Release 2 DBA I Training – Onsite, Tailored, Low Cost

Oracle 12c -18c New Features For Administrators Training – Onsite

Don’t forget to check out our monthly specials to save – Monthly Specials

MindIQ.com

Print Friendly, PDF & Email