Oracle Training : Oracle9i Advanced PL/SQL Programming

This hands-on class is designed for PL/SQL developers writing or supporting complex enterprise systems and provides a critical continuation of learning for the PL/SQL developer. This course is the next step for persons who have knowledge of PL/SQL basics. You will learn how to build systems that incorporate many new 9i features such as ANSI CASE statement, extracting DDL with DBMS_METADATA and using datetime data to support time zone data. We will also discuss Object-Relational features, VARRAYs and Nested Tables, dynamic SQL, pipelined table functions, packages, DBMS_LOB, UTL_FILE for sequential file handling outside the database, intersession communication with DBMS_PIPE or DBMS_ALERT and sophisticated transaction-based messaging with Advanced Queuing (DBMS_AQ). You'll also be introduced to PL/SQL program tracing and tuning with DBMS_TRACE and DBMS_PROFILER. This course has been completely updated for Oracle9i. Working examples are included throughout the course book and students have access to electronic versions of all examples and lab solutions. Approximately 50% of class time is spent on hands-on exercises.

Oracle Training Prerequisites

Students must already have a working knowledge of Oracle 8 or 8i.

Oracle Training Course duration

3 Days

Oracle Training Objectives

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

Code the CASE statement
Create DDL from existing database objects with DBMS_METADATA
Incorporate datetime data into your database and applications
Build autonomous transactions
Incorporate object-relational features into your applications
Code pipelined table functions in Oracle9i databases
Package your PL/SQL objects
Use DBMS_LOB to process LOB data
Use UTL_FILE to read and write to sequential files
Schedule batch jobs with DBMS_JOB
Communicate across Oracle sessions
Use database-centric transaction-based messaging
Understand the process of tuning and tracing PL/SQL programs

Oracle Training Course outline

PL/SQL Review
- PL/SQL Program Structure
- PL/SQL Program Types
- IF/THEN/ELSE and IF/THEN/ELSIF
- LOOPing
- SELECT INTO
- Cursors
- Exceptions
- Privileges
PL/SQL New Features Overview

9i CASE Statement and CASE Expression
9i Datetime Datatypes and Functions
9i DBMS_METADATA
9i XMLType
9i Record-Based DML
8i Invoker Rights
8i Bulk Binds
8i Autonomous Transactions
8i Database Even Triggers
Workshop

Oracle Object Support

The Object-Relational Model
Introducing Object Types
Creating Object Types
Referencing Objects
Constructor Methods
Insert Data into Objects
Object Type Indexes
Object Tables
Dropping Types
Related Data Dictionary Views
Introducing Methods
Building Member Methods
Map Methods
Order Methods
9i Inheritance Support
Workshop

PL/SQL Records, Tables and Collections

Declaring PL/SQL Records
Declaring and Using PL/SQL Tables
Introducing Collections
Introducing VARRAYs
Creating a VARRAY
Inserting into a VARRAY
Querying a VARRAY
Introducing Nested Tables
Creating a Nested Table
Inserting into a Nested Table
Querying a Nested Table
The THE Operator
The CAST Operator
The CURSOR Operator
Initializing Collections with PL/SQL
Referencing Collections in PL/SQL
Comparing Collections
Built-In Methods
PL/SQL Exceptions
Related Data Dictionary Views
9i Multi-Level Collections
Workshop

Table Functions

Table Function Concepts
Introducing Pipelined Functions
Pipelined Function Examples
Parallelizing the Function
Workshop

Advanced PL/SQL Package Practices

Package Review
Package Variable Scoping
Overloading
Oracle Supplied Packages Review
Workshop

Dynamic SQL

Concepts
Comparing NDS vs. DBMS_SQL
Single and Multi-row NDS Queries
DBMS_SQL Examples
DBMS_SQL Privileges
Workshop

LOB Handling Using DBMS_LOB

LOB Concepts
LOB Types
LOB vs. LONG
Creating/Updating/Reading/Comparing LOBs
Writing/Appending/Copying/Erasing and Loading LOBs
Temporary LOBs
LONG to LOB Conversions
BFILE Concepts
Creating a Directory
Opening/Initializing/Inserting Into/Closing BFILEs
Workshop

File Input/Output Using UTL_FILE

Introducing the UTL_FILE Supplied Package
Init.ora parameters
Dealing with Unix File Permissions
Open/Close/Read/Write files
Workshop

Schedule Batch Jobs Using DBMS_JOB

Introducing the DBMS_JOB Supplied Package
Submitting a Job
Running a Job
Changing Job Parameters
Cleaning up broken jobs
Removing a Job
Using the Data Dictionary to View Job Parameters
Workshop

Intersession Communication Using DBMS_PIPE and DBMS_ALERT

Understanding Intersession Communication
Introducing the DBMS_ALERT Supplied Package
Sending/Receiving/Waiting For Alerts using DBMS_ALERT
Removing Alerts
Setting Polling Intervals
Querying SYS.DBMS_ALERT_INFO
Workshop
Introducing the DBMS_PIPE Supplied Package
Packing and Sending Messages
Receiving and Unpacking Messages
Creating and Removing Pipes
Purging Messages
Workshop

Advanced Queuing using DBMS_AQ and DBMS_AQADM

Understanding Advanced Queuing
Introducing the DBMS_AQ and DBMS_AQADM Supplied Packages
Advanced Queuing Logic Flow
Advanced Queuing Example
Creating a Message Object with DBMS_AQADM
Creating a Queue Table and Queue with DBMS_AQADM
Creating/Starting/Stopping/Dropping Queues with DBMS_AQADM
Enqueuing and Dequeuing Messages with DBMS_AQ
Advanced Queuing Data Dictionary Views
Advanced Queuing Supporting Structures
Workshop

Tune and Trace PL/SQL Programs

Introduction to Query Tuning (Basics)
Tracing PL/SQL Programs with DBMS_TRACE
Collecting Statistics with DBMS_PROFILER
Workshop

Hardware/Software Requirements

Hardware Requirements

Minimal Processor: Pentium 166 or Pentium 200
Recommended Processor: Pentium 233 or Pentium 266
RAM: Minimum 128 MB (256 MB recommended)
Oracle Corporation also recommends increasing virtual memory to:
- Minimum 200 MB, Maximum 400 MB
- Modify it in the Performance tab of System Properties in the Control Panel
Available Disk Space:
- FAT 4.5 GB for HOME Directory, 140MB for System Drive
- NTFS 2.75 GB for HOME Directory, 140MB for System Drive
- Temp Space - 400 MB
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 (SP5 or Higher) or Windows 2000 (SP 1 or higher)
Oracle9i (Release 2 recommended)
- A new install is strongly recommended if the existing Oracle install is on a freely accessible machine, such as classroom machine.
Microsoft Internet Explorer 5 or Netscape Navigator 4.5 (or later)
Adobe Acrobat Reader or Acrobat 4.0 or later (for the course presentation)*
Student Files
Text Editor*

	About us Contact us Careers at Wintrac Our Clients Why Wintrac	Register for a free training CD-ROM drawing Refer a client or instructor and earn $$$	Wintrac Inc. 16523 SW McGwire Ct. Beaverton OR 97007
© Wintrac, Inc. All rights reserved. Site Map \| Terms of Use \| Privacy Policy