Email Us   Phone : 503-259-0312   
  Home    |    Instructor-led Training    |    Self-Paced Learning    |    Online Training     


Contact Us   -   Why Choose Wintrac   -   Clients    

Courses
ADA
Adobe
Agile
AJAX
Android
Apache
AutoCAD
Big Data
Business Analysis
Business Intelligence
Business Objects
Business Skills
C++ programming
Cisco
Citrix
Cloud Computing
COBOL
Cognos
ColdFusion
COM/COM+
CompTIA
CORBA
CRM
Crystal Reports
Datawarehousing
DB2
Desktop Application Software
DNS
Embedded Systems
Google Web Toolkit (GWT)
IPhone
ITIL
Java
JBoss
LDAP
Leadership Development
Lotus
Machine learning/AI
Macintosh
Mainframe programming
Microsoft technologies
Mobile
MultiMedia and design
.NET
NetApp
Networking
New Manager Development
Object oriented analysis and design
OpenVMS
Oracle
Oracle VM
Perl
PHP
PowerBuilder
Professional Soft Skills Workshops
Project Management
Rational
Ruby
Sales Performance
SAP
SAS
Security
SharePoint
SOA
Software quality and tools
SQL Server
Sybase
Symantec
Telecommunications
Teradata
Tivoli
Tomcat
Unix/Linux/Solaris/AIX/
HP-UX
Visual Basic
Visual Foxpro
VMware
Web Development
WebLogic
WebSphere
Websphere MQ (MQSeries)
Windows programming
XML
XML Web Services
Other
Oracle 18c Release 2 PL/SQL
Oracle Training Overview

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 users of Oracle 11g - 18c.

Oracle Training 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.

Oracle Training Prerequisites

Oracle SQL 11g or higher or equivalent experience is required.

Course duration

3 Days

Oracle Training Course 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 – 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 14:  COLLECTIONS

LAB 14 SOLUTIONS:  COLLECTIONS

CHAPTER 15 – 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 15:  BULK OPERATIONS

LAB 15:  SOLUTIONS:  BULK OPERATIONS


 
About us
Contact us
Careers at Wintrac
Our Clients
Why Wintrac


Register for a free training CD-ROM drawing
Refer a client or instructor and earn $$$


Wintrac Inc.
16523 SW McGwire Ct.
Beaverton OR 97007
 
Wintrac, Inc. All rights reserved.                                                                               Site Map   |   Terms of Use   |   Privacy Policy