Oracle PL/SQL Programming Workshop

3 Days

Description

This class will teach you how to write efficient and scalable PL/SQL programs to create database-intensive PL/SQL applications. You will learn the critical and fundamental aspects of PL/SQL language.

Learning how to create efficient, scalable PL/SQL programs is an important objective of this course. To meet that objective, the course includes instruction on such things as the proper use of bind variables, bulk processing, pipelining, benchmarking different formulations of a routine and profiling PL/SQL programs to identify the code that should be tuned.

Hands-on workshops constitute approximately 50% of the class.

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Oracle PL/SQL Programming Workshop class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date

Course Overview

  • Introduction to PL/SQL    
    • What is PL/SQL?    
    • Why Use PL/SQL?    
    • PL/SQL Program Structure    
    • Anonymous Blocks    
    • Compile Errors
    • Sending Output to SQL*Plus    
    • Introduction to Procedures    
    • Procedure Compile Errors
    • Procedure Compile Warnings
    • Introduction to Functions
    • Introduction to Packages    
    • Querying the Data Dictionary    
    • Introduction to Triggers
    • Tools for PL/SQL Development    
    • Working in SQL*Plus    
  • Language Fundamentals    
    • PL/SQL Statements    
    • PL/SQL Symbols
    • Quoting Mechanism
    • Common PL/SQL Datatypes
    • Declaring Variables
    • Assignment
    • Referencing Sequences (11g)
    • %TYPE Attribute
    • PL/SQL Records    
    • %ROWTYPE Attribute
    • Programmer Defined Records    
    • Variable Scope
    • Nested Blocks
    • Functions
    • Regular Expressions (11g Enhancements)
    • IF Statement
    • CASE Statement and Expression
    • Simple CASE
    • Searched CASE
    • Simple Loops
    • CONTINUE Statement (11g)
    • Nested Loops
    • Numeric FOR Loop
    • PL/SQL Arrays
    • Simple Array Example    
    • Array Methods    
  • PL/SQL and SQL, Part I Basics
    • SELECT INTO Statement    
    • Implicit Cursor Loops    
    • DML in PL/SQL    
    • Cursor Attributes    
    • Embedding DDL    
  • PL/SQL and SQL, Part II Cursors
    • What is a Cursor?    
    • Explicit Cursor Processing    
    • Cursor Attributes    
    • Dynamic Cursors    
    • Dynamic Cursor Example    
    • Variable Scope & Cursors    
    • Cursor Parameters    
    • Cursor Records
    • Cursor FOR LOOP    
    • Referencing the Current Row    
    • FOR UPDATE Example     
  • PL/SQL and SQL, Part III Bulk Processing    
    • Bulk Processing    
    • Bulk Collect    
    • Bulk DML – FORALL
    • Using Tables of Records (11g)
    • Bulk DELETE
    • Bulk INSERT
    • Bulk UPDATE    
    • Returning into Arrays
  • Stored Procedures    
    • What is a Stored Procedure?    
    • CREATE PROCEDURE Syntax    
    • Simple Example    
    • Review: Compile Errors    
    • Procedure Signatures    
    • Calling Procedures    
    • Procedure Synonyms    
    • Referencing Parameters by Name    
    • Returning Sets: REF CURSOR    
    • Dropping Stored Procedures    
    • Procedure Dependencies    
    • ALTER COMPILE Statement
    • PL/SQL Procedure Privileges
    • Granting Execute Privilege    
    • The Data Dictionary    
  • PL/SQL Functions    
    • What is a Function?    
    • CREATE FUNCTION Syntax    
    • Simple Example    
    • Calling Functions
    • Mixing Argument Types in Function Calls (11g)
    • The Data Dictionary    
    • Table Functions
    • Building a Table Function    
    • Using a Table Function    
    • Pipelined Functions    
    • Pipelined Example    
    • Test Data Generator Example
  • Error Handling
    • Types of Errors    
    • Runtime Errors    
    • Exception Concepts    
    • Predefined Named Exceptions    
    • EXCEPTION Block Syntax
    • Handling Exceptions    
    • Recovering from Errors    
    • Logging Exceptions
    • RAISE_APPLICATION_ERROR Statement
    • Raising Exceptions    
    • Raising User Exceptions    
    • EXCEPTION_INIT Pragma
    • Using FORMAT_ERROR_BACKTRACE
  • PL/SQL Packages
    • Concepts    
    • Package Benefits    
    • Package Benefits Diagram    
    • Package Contents    
    • Package Spécification    
    • Package Body    
    • Package Privileges
    • Calling Packaged Objects    
    • Initialization Code    
    • Session Variables    
    • Package Body Variables    
    • Package Overloading    
    • Compiling Packages    
    • DROP PACKAGE Statement
    • Sample of Oracle Supplied Packages
  • DML Triggers
    • Simple (11g) DML Trigger Concepts
    • Trigger Execution    
    • Trigger Ordering (11g)
    • CREATE TRIGGER Syntax    
    • Row Triggers
    • :OLD and :NEW Reference Variables
    • Trigger Attributes    
    • Audit Trigger Example
    • Derived Value Trigger Example
    • Trigger Restrictions    
    • Compound Triggers (11g)
    • Table Triggers
    • Autonomous Transactions    
    • Autonomous Trigger Example
    • INSTEAD OF Triggers    
  • Trace and Tune    
    • Introduction to Tuning PL/SQL    
    • DBMS_PROFILER    
    • Profiler Setup    
    • Using Profiler    
    • START_PROFILER and STOP_PROFILER
    • Reporting with PROFSUM
    • Effective Use of Bind Variables
    • Benchmarking with GET_TIME and GET_CPU_TIME
    • Subprogram Inlining (11g)
    • PL/SQL Result Cache (11g)
  • File I/O Using UTL_FILE    
    • UTL_FILE Concepts    
    • Setup for UTL_FILE
    • Unix File Permissions
    • Opening Files
    • Closing Files    
    • Reading Files    
    • Writing Files - PUT    
    • Writing Files - PUT_LINE    
    • Writing Files - NEW_LINE    
    • Writing Files - PUTF    
  • Sending Email with UTL_MAIL  
    • Introduction to UTL_MAIL
    • Setup for Emailing from the Database
    • UTL_MAIL Example  

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Oracle PL/SQL Programming Workshop class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date

Prerequisites

Some programming skills are required to benefit from this class. (We will not teach you the basics of programming such as logic flow and conditional logic.  We will, however, teach you how to do it efficiently in PL/SQL.)

Experience with SQL and SQL*Plus is required.

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Oracle PL/SQL Programming Workshop class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date