Oracle Training : Oracle8i Advanced PL/SQL Programming

This class will complete a developers training curriculum concerning Oracle PL/SQL. Advanced PL/SQL constructs such as records, tables, LOBs, dynamic PL/SQL and File input/output are discussed. The Oracle facilities to tune, trace, and debug your PL/SQL programs are provided. The concept of packages is further explored and explained with using suggested methods and techniques. Intersession communication and queuing of Oracle jobs is also explained.

Oracle Training Prerequisites

Programming experience in PL/SQL

Oracle Training Course duration

3 Days

Oracle Training Objectives

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

Define PL/SQL records and tables
Create PL/SQL programs to read and write operating system files
Process Large Objects (LOBs)
Code PL/SQL packages using advanced techniques
Tune, trace and debug PL/SQL programs
Create dynamic PL/SQL programs
Schedule PL/SQL jobs
Communicate between database sessions
Provide transaction processing capabilities

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 Composite Datatypes

PL/SQL Records and Tables
Objects and Collections
Nested Tables and VARRAYs
CAST/THE/CURSOR Operators

Advanced PL/SQL Package Practices

Package Variable Scoping
Overloading
Oracle Supplied Packages Review

Dynamic PL/SQL Coding Using NDS and DBMS_SQL

NDS vs. DBMS_SQL
Single and Multi-row NDS Queries
Privileges

Tune and Trace PL/SQL Programs

DBMS_PROFILER
DBMS_TRACE

Oracle8i New Features

Invoker Rights
Bulk Binds
Autonomous Transactions
Database Event Triggers

LOB Handling Using DBMS_LOB

LOB Types
LOB vs. LONG
Creating/Updating/Reading/Comparing LOBs
Writing/Appending/Copying/Erasing LOBs
Temporary LOBs
Opening/Inserting into/Closing BFILEs

File Input/Output Using UTL_FILE

init.ora parameters
Open/Close/Read/Write files

Schedule Batch Jobs Using DBMS_JOB

Submitting a job
Running a job
Removing a job
Changing job parameters
Cleaning up broken jobs

Intersession Communication Using DBMS_PIPE and DBMS_ALERT

Sending/Receiving Alerts using DBMS_ALERT
Removing Alerts using DBMS_ALERT
Packing and Sending Messages using DBMS_PIPE
Creating and Removing Pipes using DBMS_PIPE

Queuing using DBMS_AQ and DBMS_AQADM

Creating a message object with DBMS_AQADM
Creating a queue table with DBMS_AQADM
Creating/Starting/Stopping/Dropping queues with DBMS_AQADM
Enqueuing and Dequeuing Messages with DBMS_AQ

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*

Contact Information
WINTRAC INC. - the one stop shopping center for IT training.
16523 S.W. McGwire Ct. Beaverton OR -97007
Phone: (503) 259-0312
Fax: 707-598-2268
Email: sales@wintrac.com

Send mail to webmaster@wintrac.com with questions or comments about this web site.
Copyright © 2012 Wintrac Inc.
Copyright © SB