Package Includes:

* 3 DVD-ROMs featuring live instructor-led classroom sessions with full audio, video and demonstration components with over 14 hours of instruction by Jerry Richard
* Printable courseware
* Step-by-step hands-on-labs
* Focused on practical solutions to real-world development problems
* Free 1 Year Upgrade Policy

This video-based course will provide students with techniques to design PL/SQL applications, and to solve real-world business problems. The student will be working with stored functions, stored procedures, and packages along with some standard Oracle supplied packages and exception handling techniques in PL/SQL. The Oracle PL/SQL language provides for programming features not available within SQL.

PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, and information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database

Expert Trainer

Jerry Richard - Mr. Richard has been working with Oracle since 1984. At that time he served as a programmer/analyst for the current version of Oracle, which in 1984 was Version 6. He is experience and has utilized all Oracle Versions and iterations from Version 6 to Oracle 10g. Among, these assets he also has experience with SQL, SQL Writer and SQL Reports.

Mr. Richard is a OCA (Oracle Certified Administrator) DBA (Database Administrator). While at Texas Instruments he also used other types of databases, including but not exclusive to, DB2, SQL Server, and Sybase. Interestingly, he played an integral part in performing conversions between many of the the existing databases. In terms of Operating Systems he has served as an administrator in both a UNIX and a SUN environment, however his OS experience also extends to IBM Mainframe and Microsoft Windows

Course Features:

* Main Menu
Move through hours of in-depth content - quickly and easily due to the efficient and organized structure.
* PowerPoint
Utilizing PowerPoint presentations enhances the delivery by displaying a variety of visual information to the user. This type of representation allows the user to better interpret the material through charts, definitions, graphs, and more...
* Controls
Move forward, back, and repeat entire topics or just a section. A progress bar illuminates as you advance through exercises.
* Full Motion Video
All courses feature full-motion videos of instructors teaching the information as if they are speaking directly to you. Our unique delivery simulates a one-on-one classroom environment creating a more personal lesson and learning experience.
* Study Guides
Printable study guides for the entire course are available. This allows all material to be viewed, reviewed, and printed for viewing at a later date.
* Review Exercises
Each section has a review quiz to aid in the learning process by validating the comprehension of the material covered before moving on to a new section.
* Resume
All courses are resumed to where you left off last session allowing you to learn when it is convenient for you without the hassle of remembering where you where.
* Live Demonstrations
Demonstrations are a way for the instructor to show and tell the user how to perform a task by actually doing it on screen in front of them. In this format it gives the user the power to see things done by a professional in the intended environment as many times as they would like.
* Labs
This course features cutting-edge, interactive labs, which provide a hands-on learning experience. This environment allows you to interact with the software with out the need for purchasing it or having it installed on your computer.
* Certificate of Completion
Career Academy is recognized worldwide for its technology-based IT training curriculum's. Upon successful completion of our program, you will be receiving a Career Academy Distance Education Certificate of Completion.

Course Outline

Course Introduction
Course Introduction
Introduction to Oracle & SQL*PLUS
Introduction to Oracle & SQL*PLUS
Section Objectives
Basic Components of the SGA
Logging Into SQL*PLUS
A Simple Select
Demo - Correcting an Incorrect SQL
Oracle File Extensions
Saving A SQL Statement To A File
Demo - Running Scripts
CRT Files and Spool Files
Oracle SQL*PLUS Editing Commands
Demo - Using the Oracle Editor
SQL Scripts
Dictionary Tables For Users
Demo - Oracle Data Dictionary Views
Future Directions
Programmer Responsibilities
Benefits of Database Development
Introduction to Lab 1
Lab - Running Oracle & Building Tables
SQL*PLUS Commands
Programming Commands
Demo - Creating Variables in SQL*PLUS
Programming Commands Cont.
Reporting Commands
Demo - Report Commands in SQL*PLUS
More Programming Commands
Demo - Compute Statements with Break
Other Commands
SQL*Plus Enhancements in Oracle 10g
Append, Create and Replace Extensions
Show Recyclebin
Introduction to Lab 2
Lab - Commands Using SQL*Plus
SQL*Plus Commands
Chapter 1 Review
Structured Query Language
Structured Query Language
SQL Components
SQL Data Manipulation Language (DML)
SQL Basic Retrieval
Demo - Basic SQL Commands
Retrieval of Computed Values
Demo - SQL Retrieval
Order By Clause
Ordering By Descending
The Distinct Operand
Selecting On Conditions
Multiple Conditions
The Between Clause
The Like Clause
Demo - Showing Like Clause
Negative Conditions
Built In Functions
Group By Clause
The Having Clause
Demo - Review of Having Clause
The Analytical Commands
Cube
Retrieval Involving Null
Treatment of Nulls
Null Values Are Considered In:
Case Expression
Introduction to Lab 3
Lab - More About Selects
Demo - Review of Exercises
Chapter 2 Review
Advanced Structured Query Language
Advanced Structured Query Language
Advanced DML Functions
Qualified Names
Correlation Variables
Advanced DML Functions Cont.
Demo - Oracle's Natural and Outer Joins
ANSI/ISO Join Statements
Oracle Join Statements
Multiple Table Join
More about Aliases
Demo - Joins
Subqueries
Demo - Single & Multi-Value Subqueries
Multivalued Subqueries
The Any Parameter
In-Line Views
The Exists Parameter
Demo - How Exists Help Performance
Set Operators (Conjunctions)
Union
Demo - Unions
Set Operator Intersect
The Minus Conjunction
The Model Clause
Modify Functions
Update a Single Row
Complex Update
Merging Data In Oracle Tables
SQL Delete
Introduction to Lab 4
Lab - Select Rows from Multiple Tables
Chapter 3 Review
Non ANSI SQL Statements
Non ANSI SQL Statements
Oracle SQL Plus Commands
Number Formats
Numeric Functions
Character
Date Formats
Demo - Date Formats and Usage
Date Formats
The Date Functions
Demo - Date Functions
Syntax Operators & Parameter Subs.
Demo - Identify How Users Input Data
The Conversion Functions
The Sample Clause
Introduction to Lab 5
Lab - SQL*Plus Commands for Reporting
Chapter 4 Review
Data Definition Language Commands
Data Definition Language Commands
Base Tables
DDL Commands
Logical Definition - Tables
Create Table Mystaff
Demo - Create a Table
Logical Definition – System Datatypes
Column Data Types
Creating Table and
Creating Check Constraints
Demo - Creating Constraints
Creating Unique Constraints
Creating Referential Integrity Constraints
Demo - Create Primary and Foreign Keys
Non-Unique Indexes
Composite Indexes
Rebuilding Indexes
Bitmap Indexes
Creating Reverse Key Indexes
Demo - Creating Reverse Key Indexes
Index-Organized Tables
IOT Row-Overflow Strategy
Index Before or After Loading Data
Demo - Altering Tables after Creation
Synonyms
Deleting Catalog Items
Views
Column Names
Join Views
Views of Views
View Retrieval Queries
Introduction to Lab 6
Lab - Creating Tables and Indexes
Chapter 5 Review
PL/SQL Programming
PL/SQL Programming
Overview of PL/SQL
The PL/SQL Block Structure
PL/SQL In The Oracle RDBMS
PL/SQL Variables
Referencing Variables
Using Composite Variables
Demo - Creating Composite Variables
Using Composite Variables Cont.
PL/SQL Records Processing
Assigning Values To Variables
Demo - Local and Global Variables
Assigning Values To Variables Cont.
Defining Composite Variables - Tables
Datatype Conversions
Input and Output Values For Variables
Demo - Creating Global Variables
Using The Select Clause
PL/SQL Insert Statements
PL/SQL Update Processing
PL/SQL Delete Processing
Decision Statements!
Use CASE Statements in PL/SQL
CASE Statement and Expression
CASE_NOT_FOUND Exception
PL/SQL Iterative Processing
Developing A Continue Statement
PL/SQL Iterative Processing Cont.
PL/SQL Exceptions
Error Processing
PL/SQL Exceptions Cont.
Demo - Exception Handling
PL/SQL Error Processing
Demo - Exception Block Processing
Raising User-Defined Exceptions
Demo - User-Defined Exceptions
PL/SQL Error Processing Cont.
Propagating Exceptions
PL/SQL Cursor Processing
Declaring The Cursor
Cursor Attributes With Explicit Cursors
Demo - How CURSORS are Handled
Cursor Attributes With Explicit Cursors
Demo - Cursor Looping
Update The Current Row
Demo - Update Processing via Cursors
Storing Rows In a PL/SQL Table
Passing in the Declare Cursor Command
PL/SQL Subprograms
PL/SQL Subprograms Viewing Code
Demo - Creating Procedures
PL/SQL Subprograms Rebuilding
Procedures
PL/SQL Stored Procedures
Returning Values
Demo - Parameters
Returning Values
The In/Out Argument in a Procedure
Demo - OUTPUT Parameters
Displaying Parameters
Executing a Stored Procedure in SQL
Creating Functions
Create Functions in a Declarative Section
Database Packages
A Sample Package
Reading/Writing Operating System Files
Reading Operating System
Introduction to Lab 7
Lab - Creating Procedures & Functions
Introduction to Lab 8
Lab - Creating Packages
Chapter 6 Review
Course Closure
Course Closure