THE HARTMANN SOFTWARE GROUP

303.377.6176

Introduction to Oracle 11g PL/SQL Training

Course Description

A Relational Database Management System (RDBMS) is a software system that allows you to create and manage a relational database. Minimum requirements for such a system are defined by both ANSI and ISO. PL/SQL is Oracle's Procedural Language for SQL. It is Oracle's database programming language for creating stored procedures, functions, database triggers, and object methods. PL/SQL can be used for implementing business rules, computing algorithms, manipulating data, and for stand-alone programs. The Oracle 11g release has greatly enhanced the features and functionality of PL/SQL. Students will write stored procedures, functions, packages, and triggers, and implement complex business rules in Oracle. Students will learn programming, management, and security issues of working with PL/SQL program units. Programming topics will include the built-in packages that come with Oracle, the creation of triggers, and stored procedure features.

Course Length: 3 Days
Course Tuition: $1190 (US)

Prerequisites
A good working knowledge of Oracle 11g SQL features is required. Programming experience in a high-level language, such as COBOL, Java, or Perl is also required.

Course Outline

Triggers
Beyond Declarative Integrity
Triggers
Types of Triggers
Trigger Sequencing
Row-Level Triggers
Trigger Predicates
Trigger Conditions
Using Sequences
Cascading Triggers and Mutating Tables
Generating an Error
Maintaining Triggers

PL/SQL Variables and Datatypes
Anonymous Blocks
Declaring Variables
Datatypes
Subtypes
Character Data
Dates and Timestamps
Date Intervals
Anchored Types
Assignment and Conversions
Selecting into a Variable
Returning into a Variable

PL/SQL Syntax and Logic
Conditional Statements – IF/THEN
Conditional Statements – CASE
Comments and Labels
Loops
WHILE and FOR Loops
SQL in PL/SQL
Local Procedures and Functions

Stored Procedures and Functions
Stored Subprograms
Creating a Stored Procedure
Procedure Calls and Parameters
Parameter Modes
Named Parameter Notation
Default Arguments
Creating a Stored Function
Stored Functions and SQL
Invoker’s Rights

Exception Handling
SQLCODE and SQLERRM
Exception Handlers
Nesting Blocks
Introduction to Oracle® 11g
PL/SQL Programming
Scope and Name Resolution Declaring and Raising Named Exceptions
User-Defined Exceptions

Records, Collections, and User-Defined Types
Record Variables
Using the %ROWTYPE Attribute
User-Defined Object Types
VARRAY and Nested TABLE Collections
Using Nested TABLEs
Using VARRAYs
Collections in Database Tables
Associative Array Collections
Collection Methods
Iterating Through Collections

Cursors
Multi-Row Queries
Declaring and Opening Cursors
Fetching Rows
Closing Cursors
The Cursor FOR Loop
FOR UPDATE Cursors
Cursor Parameters
The Implicit (SQL) Cursor

Bulk Operations
Bulk Binding
BULK COLLECT Clause
FORALL Statement
FORALL Variations
Bulk Returns
Bulk Fetching with Cursors

Using Packages
Packages
Oracle-Supplied Packages
The DBMS_OUTPUT Package
The DBMS_UTILITY Package
The UTL_FILE Package
Creating Pipes with DBMS_PIPE
Writing to and Reading from a Pipe
The DBMS_METADATA Package
XML Packages
Networking Packages
Other Supplied Packages

Creating Packages
Structure of a Package
The Package Interface and Implementation
Package Variables and Package State
Overloading Package Functions and Procedures
Forward Declarations
Strong REF CURSOR Variables
Weak REF CURSOR Variables

Working with LOBs
Large Object Types
Oracle Directories
LOB Locators
Internal LOBs
LOB Storage and SECUREFILEs
External LOBs
Temporary LOBs
The DBMS_LOB Package

Maintaining PL/SQL Code
Privileges for Stored Programs
Data Dictionary
PL/SQL Stored Program Compilation
Conditional Compilation
Compile-Time Warnings
The PL/SQL Execution Environment
Dependencies and Validation
Maintaining Stored Programs

Appendix A: Dynamic SQL
Generating SQL at Runtime
Native Dynamic SQL vs. DBMS_SQL Package
The EXECUTE IMMEDIATE Statement
Using Bind Variables
Multi-row Dynamic Queries
Bulk Operations with Dynamic SQL
Using DBMS_SQL
DBMS_SQL Subprograms

Appendix B: PL/SQL Versions, Datatypes, and Language Limits

Appendix C: Oracle 11g Supplied Packages

Training can be conducted in a number of different ways outside of a classroom setting. To that end, we offer some free on line tutorials which we will update extensively in the very near future. We also offer mentoring services to help design, integrate and develop large scale complicated systems.

Public Training Schedule

Java Training - June 11th - 15th

C/C++ Training - June 11th - 15th

Object Oriented C# Training - August 13th - 17th

See all public training classes & Register

Learn from anywhere!

Some of the places where we have taught >>

New York City
Los Angeles, California
Chicago, Illinois
Houston, Texas
Philadelphia, Pennsylvania
Pittsburgh, Pennsylvania
Phoenix, Arizona
San Antonio, Texas
San Diego, California
Dallas, Texas
San Jose California
Buffalo, New York
Toronto, Canada
Montreal, Canada
Detroit, Michigan
Indianapolis, Indiana
Jacksonville, Florida
San Francisco, California
Columbus, Ohio
Austin, Texas
Memphis, Tennessee
Baltimore, Maryland
Fort Worth, Texas
Charlotte, North Carolina
El Paso, Texas
Milwaukee, Wisconsin
Seattle, Washington
Boston, Massachusetts
Denver, Colorado
Louisville, Kentucky
Washington DC
Nashville, Tennessee
Las Vegas, Nevada
Portland, Oregon
Oklahoma City, Oklahoma
Tucson, Arizona
Albuquerque, New Mexico
Long Beach, California
Atlanta, Georgia
Fresno, California
Sacramento, California
New Orleans, Louisiana
Cleveland, Ohio
Kansas City, Kansas
Boulder, Colorado
Virginia Beach, Virginia
Omaha, Nebraska
Oakland, California
Miami, Florida
Tulsa, Oklahoma
Honolulu, Hawaii
Minneapolis, Minnesota
Colorado Springs, Colorado

Let's Have Fun

Public Training Schedule