Oracle 9i PL/SQL (O020) Training Course

Training Course Objectives

This course introduces the fundamentals of Oracle's procedural language, PL/SQL, the engine behind the full range of Oracle technology.

The course introduces the syntactic transactional languages controls evolving to cover the full range of PL/SQL programming functionality found within the Oracle 9i RDBMS.

This practical course is an essential foundation study course needed for all Oracle 9i RDBMS professionals to maximise their utilisation of Oracle 9i and the productive benefits that can be achieved through the essential deployment of PL/SQL functionality across the business.

Who Should Attend

• Oracle Analyst/Programmers
• Oracle Technicians
• Oracle DBA's
• Oracle Developers

Training Course Prerequisites

• PC Fundamentals
• Oracle 9i SQL (O010)
• View Learning Path

Training Course Duration

5 Days

Training Course Contents

PL/SQL Basics

• Introduction
• PL/SQL Block Structure

Declaring

• Variable Types
• Datatypes
• Creating Variables and Constants
• %Type Identifiers

Basic Data Processing

• Select ... Into Command

Procedural Processing

• Simple or Infinite Loops
• While Loops
• For Loops
• IF Statements
• GOTO

Data Processing

• Creating Explicit Cursors
• Working with Cursors
• Using Cursors with Loops
• Parameterised Cursors
• Cursor Attributes
• Implicit Cursors
• Cursor for Loops

Error Handling

• Introduction to Exceptions
• Creating Programmer Defined Exceptions
• Exceptions
• Redefining Oracle Exceptions
• Raising Application Errors
• Using Error Variables

Modularization

• PL/SQL block structure
• Procedures
• Functions
• Parameters

Stored PL/SQL code

• User Schemas
• Hints
• Dependencies
• Invoker Rights
• Overloaded Modules
• Local Modules
• Shared Global Area
• Code Wrapping

Packages

• Creating Packages
• Dropping Packages
• Parameter passing by reference
• Serially reusable packages

Transactional Control

• Commit
• Rollback
• Savepoint
• Autonomous Transactions

Extending the Type System

• PL/SQL Collections
• Associated Arrays
• Nested Tables
• Bulk Binds
• Bulk Exceptions
• Caching Data

Database Triggers

• Writing Database Triggers
• Database Trigger Syntax
• Accessing Column Values in Triggers
• Trigger Restrictions

Trigger Firing Sequence

• Disabling Triggers
• Enabling Triggers
• Dropping Triggers
• New 9i Database Triggers

Data Dictionary Views

• Naming Conventions
• Package security

Oracle Supplied Packages

• DBMS_OUTPUT
• DBMS_JOB
• DBMS_PROFILER
• DBMS_RANDOM

File IO with UTL_FILE package

Dynamic SQL

• Methods
• Performing Dynamic SQL with DBMS_SQL Package
• Native Dynamic SQL

Large Object Support

• Large Object Principles
• Using DBMS_LOB

Web PL/SQL Development

• Output with htp.p
• Specific htp procedures

PL/SQL Toolkit Packages

• owa_cookie
• owa_image
• owa_pattern
• owa_util
• utl_http

Next Available Dates

iTrain delivers this course as company specific training. Please contact us for more information or course dates.