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
Introduction To PL/SQL 12c
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 both users of Oracle12c and Oracle11g.


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

Oracle12c SQL or equivalent experience is required.


Course duration

5 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
  • 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
  • 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
  • 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
  • ENHANCED LANGUAGE INTERFACE FEATURES

 
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