To Register

For more information or to Register,
Click Here.

Follow Me

OR651 Oracle 11g Introduction to PL/SQL

OVERVIEW

This course is a 2-3 day class that provides you with the skills necessary to code Oracle Version 11g PL/SQL programs with a focus on developing these  PL/SQL programs for procedures, functions and triggers.  We offer this class in Oracle versions 9i and 10g as well.

DURATION:  2-3 Days (see content below)

OBJECTIVES 

Upon successfully completing the course, participants will be able to:

  • Understand the syntax of a  PL/SQL block
  • Code and test anonymous PL/SQL programs
  • Code PL/SQL programs that include common programming constructs such as datatyping, variable assignment, flow control, cursor handling, iterations.
  • Describe the Oracle  objects procedure, functions and trigger and their implementation in a client/server environment.
  • Understand how to compile and test procedures, functions and packages.
  • Create server-side stored procedures, functions and packages using PL/SQL Programming.
  • Code a wide range of SQL statements including:
    CREATE PROCEDURE, CREATE FUNCTION, CREATE PACKAGE.
  • Understand and code error handling and exception routines in PL/SQL Programs.
  • Use the data dictionary to reference source code for procedures, functions and packages
  • Understand Triggers and how to compile and test triggers
  • Use the UTL_FILE package to read and write to operating system files.
  • Use the UTL_MAIL package to send email from an Oracle database

AUDIENCE

Application developers or Database Administrators who will develop applications in an Oracle environment.

PREREQUISITES

Before beginning this course, students should have completed SQL Fundamentals course or have working knowledge of SQL. Understanding the basic SQL commands (INSERT, UPDATE, DELETE and SELECT) is required.    Programming experience in a procedural language such as C, C++, PL/I or COBOL is beneficial, but not required.

FORMAT/MATERIALS

Lecture and discussion followed by hands-on labs to reinforce the material.  Each participant receives an extensive manual of lecture notes, examples and workshop information.

CONTENT: Day 1

Programming with PL/SQL

  • What is PL/SQL?
  • Why Use PL /SQL?
  • PL/SQL Program Structure
  • Anonymous Blocks
  • Compile Errors
  • Editing and executing programs
  • Sending Output to SQL*Plus
  • Introduction to Procedures
  • Procedure Compile Errors
  • Procedure Compile Warnings
  • Introduction to Functions
  • Introduction to Packages
  • Querying the Data Dictionary
  • Tools for PL/SQL Development
  • Working in SQL*Plus

Language Fundamentals

  • Types of PL/SQL Programs
  • PL/SQL Program construct
  • Example PL/SQL program
  • SELECT INTO statement
  • IF-THEN-ELSE statement
  • CASE Statement and Expression
  • Simple CASE
  • Searched CASE
  • PL/SQL Arrays
  • Simple Array Example
  • Array Methods
  • Quoting Mechanism
  • PL/SQL delimiters
  • PL/SQL datatypes
  • Creating Variables and Constants
  • Assigning Values to Variables
  • Unconditional branching-labels and GO TO
  • LOOP control
  • Nested loops
  • FOR loop
  • WHILE loop
  • Computer Labs

PL/SQL Cursors

  • Cursor concepts and attributes
  • Explicit and implicit cursors
  • CURSOR parameters
  • Declaring the cursor: CURSOR
  • Opening the cursor: OPEN
  • Fetching from the cursor: FETCH
  • Cursor attributes:
  • Cursor FOR LOOP control
  • Updating/deleting current row
  • Returning Sets: REF CURSOR
  • Computer Labs

Collections

  • Nested Table
  • Associative Arrays (INDEXED BY tables
  • Bulk binds
  • BULK COLLECT INTO
  • FORALL statement

CONTENT: Day 2

PL/SQL Error Handling

  • Coding Exception Blocks
  • Recovering from errors
  • Predefined and user defined exceptions
  • Computer Labs

Database Procedures

  • Why use procedures and functions
  • Contents of procedures
  • Syntax and examples: CREATE PROCEDURE
  • Calling procedures
  • DESCRIBEing procedures
  • Viewing source code and any errors
  • Compilation
  • Compilation errors
  • Dependencies
  • Reporting errors from a database procedure: RAISE_APPLICATION_ERROR
  • Execution privileges and synonyms for procedures
  • Computer Labs

Database Functions

  • Function creation
  • Syntax
  • Calling functions
  • Computer Labs
  • Using functions with Developer/2000 and other client programs

Debugging Procedural Objects

  • Older debugging facilities
  • Oracle debugging facilities
  • Limitations of debugging facilities
  • Using FORMAT_ERROR_BACKTRACE
  • Computer Labs

Database Packages

  • Description and components
  • Why use packages
  • Syntax and examples
  • Creating package specification and body: CREATE PACKAGE
  • Manually re-compiling
  • Package initialization code
  • Package variables, cursors, types
  • Calling Package procedures/functions
  • Privileges and Packages
  • Dropping Packages
  • Computer Labs

CONTENT: Day 3

Database Triggers

  • Description
  • Syntax and examples: CREATE TRIGGER
  • Creating and maintaining triggers
  • The 12 trigger types
  • Trigger notes
  • Failures in triggers
  • OLD and NEW in triggers
  • Additional trigger concepts
  • What cannot go in triggers
  • Computer Labs

Bulk Processing

  • Description
  • Bulk Collect
  • Bulk DML - FORALL
  • Bulk DELETE
  • Bulk INSERT
  • Bulk UPDATE
  • Returning into Arrays

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
  •