Description
This course provides participants with the skills necessary to make full use of the procedural extensions to SQL that are supplied with Oracle. It is a practical hands-on course with many exercises.

Audience
Application designers, developers and programmers who will be moving to an Oracle environment.

Prerequisites
Familiarity with Oracle’s implementation of SQL to the level provided by Arkoa’s Using Oracle8 SQL*Plus course is required.

Hardware and Software
Oracle8 or 8i properly installed on any platform, with sufficient logins for the number of students and instructor.

Format

• Presentation
  
• Hands-on
  

Objectives
After completing this course, participants should be able to:

• Describe the rationale behind PL/SQL
• Perform PL/SQL programming
• Define composite datatypes
• Use exception handling
• Create stored procedures and functions
• Implement packages
• Attach triggers to tables

Introduction

• Introduction to PL/SQL
  • The purpose of PL/SQL
  • Advantages of using PL/SQL
  • Oracle8 object relational features

Fundamentals

• Block structures and flow control
  • IF, Loop, and GOTO statements
• Variables and constants
  • Declaring variables
  • Declaring constants
  • Using variables
  • Variable scope

Composite Data Types

• Standard PL/SQL methods: RECORD and TABLE
• Object-relational methods: reference types and collection types

Cursors and Exception Handling

• Cursors
  • Declaring and using cursors
  • Implicit cursors
  • Cursor variables
• Exception handling
  • Predefined exceptions
  • User defined exceptions
• SQLCODE and SQLERRM

Stored Procedures and Functions

• Stored procedures
• Stored functions
• Creating SQL accessible functions
• Using functions and procedures
• External procedures

Triggers

• Components of a trigger
  • The trigger statement
  • The restriction
  • The trigger action
• Using triggers
  • Creating, enabling, and disabling triggers
  • Compiling triggers

Packages

• Using packages
  • EXECUTE permission
  • Dependencies
  • Wrapped PL/SQL
• Supplied packages
  • Oracle packages
  • DBMS_PIPE package
  • DBMS_OUTPUT package
  • UTL_FILE package
• DBMS_JOB package