Oracle Training : Oracle8i PL/SQL Programming

Oracle8i PL/SQL Programming

Oracle Training Overview

This class is for those entering the world of Oracle application development either having previous Oracle or other relational database experience. You will learn the critical aspects of Oracle development including cursors, stored procedures and functions, triggers, error handling and tuning basics. Hands-on workshops constitute approximately 50% of the class.

Prerequisites

Programming experience in SQL and a procedural language such as C or COBOL. Relational database experience is helpful but not required.

Oracle Training Course duration

3 Days

Oracle Training Objectives

After successfully completing this course, you will be able to:

Code PL/SQL programs that include common programming constructs such as data-typing, variable assignment, flow control, cursor handling, and error handling
Create server-side stored procedures, functions and triggers
Publish Java programs in the database and call them from Oracle
Create and maintain Oracle packages
Successfully master the material for exam 2 of the Oracle Certified Application Developer Program

Oracle Training Course outline

What is PL/SQL?

PL/SQL concepts
Server side execution

Types of PL/SQL Programs
Anonymous blocks
Triggers
Stored procedures
Stored functions

Benefits
Block structure
Anonymous blocks
Displaying output
Calling PL/SQL programs
Debugging and testing in SQL*Plus
Procedure Builder and related Tools

PL/SQL Programming

Delimiters
Datatypes
Variables and constants
Naming variables
Variable scope
Calling SQL functions from PL/SQL
%type and %rowtype
Select into
If-then-else
Loops

Cursors

Cursor concepts
Declaring a cursor
Cursor attributes
Fetch loop
Parameters
Cursor for loops
Current row reference and data maintenance

PL/SQL Stored Procedures

Creating procedures
Executing procedures
Parameters
Dropping and recreating procedures
Reporting errors
Source code
Dependencies
Privileges
Synonyms

PL/SQL Stored Functions

Function concepts
Creating functions
Executing functions

Error Handling

Error handling concepts
The exception block
Predefined exceptions
User defined exceptions
Passing exceptions to callers
Packaged exceptions
Application errors
Error propagation

Triggers

Trigger concepts
Creating a trigger
Row versus statement triggers
Before versus after triggers
Getting before and after values
Trigger attributes
"Instead of" triggers
Enabling and disabling triggers
DDL Triggers
System Event Triggers

Packages

Package concepts
Advantages
Creating a package
Global variables
Oracle supplied packages

Introduction to SQL Tuning

SQL tuning basics
The plan table
Explain the plan
SQL tuning
Automating explains

Java

The Java language
Oracle's Java support
Java stored procedures
Java installation
Calling Java from Oracle

Hardware/Software Requirements

Hardware Requirements

Minimal Processor: Pentium 166 or Pentium 200
Recommended Processor: Pentium 233 or Pentium 266
RAM: 128 MB (256 MB recommended)

Oracle Corporation also recommends increasing virtual memory to 200 MB. (Modify it in the Performance tab of System Properties in the Control Panel.)
Available Disk Space (FAT or HTFS): 3 GB

Video: 256 color
A connection to the Internet is desirable (but not absolutely necessary) to show students various Oracle-related sites.

Software Requirements

Windows NT 4.0 or Windows 2000
Oracle8i 8.1.6 or above
Java JDK 1.1.5 or above*
Microsoft Internet Explorer 5 or Netscape Navigator 4.5
Adobe Acrobat Reader or Acrobat 4.0 or later (for the course presentation)*
Student Files
Text Editor*