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.

Audience: This course is appropriate for anyone needing to understand Oracle’s proprietary programming language. That would include business analysts, application developers and database administrators.
Course Duration: 5 days
Prerequisites:

Oracle12c SQL or equivalent experience is required.

Course Outline:
  • 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 One – PL/SQL Program Structure
    • Lab One Solutions – PL/SQL Program Structure
  • 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 Two – PL/SQL Flow Control
    • Lab Two Solutions – PL/SQL Flow Control
  • SQL Developer and PL/SQL
    • SQL developer and PL/SQL
    • Creating and Executing Scripts
    • Lab Three – SQL Developer and PL/SQL
    • Lab Three Solutions – SQL Developer and PL/SQL
  • 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 Four – Select Into
    • Lab Four Solutions – Select Into
  • 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 Five – The PL/SQL Cursor
    • Lab Five Solutions – The PL/SQL Cursor
  • Optimization
    • Timing PL/SQL
    • FOR UPDATE / WHERE CURRENT OF
    • Lab Six – Optimization
    • Lab Six Solutions – Optimization
  • PL/SQL Exception Handling
    • The Exception Section
    • Oracle Named Exceptions
    • Pragma EXCEPTION_INIT
    • User Defined Exceptions
      • The Scope 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 Seven – PL/SQL Exception Handling
    • Lab Seven Solutions – PL/SQL Exception Handling
  • 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 Eight – Stored Procedures
    • Lab Eight Solutions – Stored Procedures
  • 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 Nine Functions
    • Lab Nine Solutions – Functions
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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

 

  • 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
  • Cursor Variable
    • 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
  • 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
  • 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
  • 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
  • Java and Other Languages
    • Alternative Languages
    • Loading Code
    • Publishing Code
    • Executing the Code
    • Interfacing With C
    • Enhanced Language Interface Features