Back to Search Results

Training Course Summary:

In this course participants will learn to write PL/SQL procedures, functions and packages. Working in both the Procedure Builder and the SQL*Plus environments, participants will learn how to create and manage PL/SQL program units and database triggers, including managing changes to objects and recompiling program units. Participants will also learn how to use some of the Oracle-supplied packages.

Pre-Requisites:

A good understanding of SQL and PL/SQL coding techniques gained by attending Introduction to Oracle: SPLI or INSPLI
Introduction to Oracle: SQL and PL/SQL (SPLI)
or Introduction to Oracle for Experienced SQL Users (INSPLI)

Training Course Overview/Content:

Overview of PL/SQL
Working with Procedure Builder
Components of Procedure Builder
Manage Program Units using the Object Navigator
Use the Program Unit Editor to create and compile Program Units
Invoke Program Units using the PL/SQL Interpreter

Creating Procedures
Create client-side and server-side procedures
Create and invoke procedures with arguments
Remove a procedure

Creating Functions
Create client-side and server-side functions
Invoke a function
Remove a function
Differentiate between procedures and functions
Benefits of stored procedures and functions

Creating Packages
Create a package to group together related PL/SQL objects
Make a package construct either public or private
Invoke a package construct
Remove a package construct

More Package Concepts
Overloading
Avoid errors with mutually referential subprograms
One-time only procedures
Control the extent to which functions permit database operations
Persistent states

Using Oracle-Supplied Packages
Overview of Oracle-supplied packages
Examples of some supplied packages
Writing Dynamic SQL

Creating Database Triggers
Create database triggers
Manage triggers
Drop database triggers

More Trigger Concepts
Create triggers on database actions
Trigger rules
Implementation of triggers
Other events when triggers will fire

Managing Subprograms and Triggers
Privilege requirements
Manage stored PL/SQL objects
Debug program units

Managing Dependencies
Overview of object dependencies
Manage PL/SQL objects for recompilation

Manipulating Large Objects
Compare and contrast LONG/RAW/LONG RAW with Large Objects (LOBs)
Understand Large Objects
Manage Binary Large File Objects (BFILEs)
Use PL/SQL with a LOB
Create a table with LOB columns
Manipulate LOBS
Use DBMS_LOB
Oracle-supplied package
Create a temporary Large Object



Course Objectives

At the end of the course the student will be able to:

Create and use stored procedures and functions in both Procedure Builder and SQL*Plus Create and use packages Describe Oracle-supplied packages Create and use DML triggers Understand and use Database triggers Manage procedures, functions, packages and triggers Understand and use Large Objects (LOBS)