Introduction to Oracle9i

Course Length: 5 days, $250

This class will develop database-programming skills using SQL and PL/SQL. Students will learn the Oracle9i architecture, relational design concepts, simple and complex query skills, PL/SQL programming skills, proper software engineering techniques for database programming, Oracle problem solving, and performance tuning skills.

Audience: Analysts, developers, designers, administrators, and managers new to Oracle or database
programming.

Prerequisites: A background in data processing and programming is strongly recommended.

Introduction

What is an Oracle Server?
Oracle9i Architecture
Oracle Versioning
What is SQL?
SQL Statements
SQL Standards
Built-in Data Types

SQL Programming

What is SQL*PLUS?
Starting an Oracle Session
Windows and Browser Version of SQL*Plus
Logging into Oracle Through SQL*Plus
Using the SELECT Command
SELECT Syntax
Constants and Data Conversion
Pseudo Columns
Expressions and Aliases
DI
WHERE Clause
Arithmetic Operators
Logical Connectives
Predicates
ORDER BY Clause
GROUP BY Clause
HAVING Clause
Sales Order Entry System
Description: Sales Order Entry System
Relationships: Sales Order Entry System
Business Rules: Sales Order Entry System
Data Model

Using the Data Dictionary

Metadata and User Data
What is the Oracle Data Dictionary?
Base Tables and Static Views in the Data
Dictionary
Dynamic Views in the Data Dictionary
ALL_Views
DBA_Views
USER Views
Additional Views
V$ Vie

Using Oracle's SQL*PLUS

SQL*PLUS Commands
The DESCRIBE Command
Using the SHOW Command
Modifying Session Variables with SET
The SQL Buffer
Editing System Files with SQL*PLUS
Executing SQL*PLUS Commands
Tracing Statements in SQL*Plus
Setting Up a Site or User Profile
SQL*PLUS Reports
Modifying Column Formats
Using Compute Functions
Working with Report Titles
HELP Command
Creating Web Reports Using SQL*Plus

Learning the Data Manipulation Language

The Data Manipulation Language
Modifying Data
How to Insert Data
Multi-row Insert
The Insert Command
Modifying Data
Update Command
Deleting Data
Transaction Processing
The COMMIT Command
The ROLLBACK Command
Savep
Locks
Using Oracle's SQL Loader

Using SQL Functions

Functions
Using SQL Functions
Types of SQL Functions
NUMERIC Functions
Char Functions
Data Conversion Functions
DATE Functions
Miscellaneous Functions
DECODE Function
Group Functions
Additional Functions
New ROLLUP Operators
The GROUPING Function
Analytical Functions
Full List of SQL Functions

Advanced SQL Concepts

Working with Joins
Types of Joins
Basic Join
Equi-Joins
Outer Joins
Left and Right Outer Joins
What is a Subquery?
Subquery Rules
Comparison Tests for Subqueries
Using IN and EXISTS Operators in
Subqueries
Using the ANY and ALL Operators in
Subqueries
DML Commands Using Single-Row
Subqueries
Top-N Analysis
Using Set Operators
NULLS and Logic

Understanding Database Objects

Schemas
The Data Definition Language
Creating Tables
Defining DEFAULT Values
Using Explicit Defaults
The ALTER TABLE Command
Using Indexes
Query Optimization
Creating Indexes
Working with Views
Creating Views
Creating Sequences
Accessing Sequences
Creating Synonyms
Dropping Database Objects
Truncating tables
Constraints
Different Types of Constraints
Building Constraints
Primary Key Constraints
Foreign Key Constraints
Naming Constraints
Unique Constraints
Not Null Constraints
Check Constraints
Constraints can be Deferred
Enabling and Disabling Constraints
Index-Organized Table
Restrictions on Index-Organized Tables
Temporary Tables

Oracle Indexing

Index Types
B*Tree Index Attributes
B*Tree Indexes
Composite Indexes
Creating a Composite Index
Reverse Key Indexes
Bitmap Indexes
Cluster (B*Tree) Indexes
Index Organized Tables
Function-Based Indexes
Domain Indexes
Index Options
Index Maintenance
Analyzing the Index
Rebuilding the Index
Coalescing the Index
Indexes on Primary Keys
Export and Import of Indexes
SQL*Loader and Indexes

ANSI SQL Features

ANSI Join Syntax
The JOIN Statement
Cross Joins
Natural Join
The USING Clause
Outer Joins
CR_EMP_SALE_TABLES.SQL Script
Full Outer Join
CASE Expression
Simple CASE Statement
Searched CASE Statement
NULLIF CASE Statement
COALESCE CASE Statement
Scalar Subqueries
Restrictions of Scalar Subqueries
The MERGE Statement
New Analytical Functions
WIDTH_BUCKET
GROUPING SETS
Grouping with Composite Columns
Using Concatenated Groupings
WITH Clause
FIRST and LAST Aggregate Sets

Introduction to Security

System Privileges
Roles
Granting Privileges
Viewing System Privileges
Granting Object Privileges
Privileges and the User Group PUBLIC
Revoking System Privileges and Roles
Object Privileges
Database Authentication
Predefined Roles
Setting up a Role
Parent versus Child Roles
Working with Default Roles
Enabling and Disabling Roles
Guidelines and Standards for Roles

iSQL*Plus

Using iSQL*Plus
Logging in Using iSQL*Plus
Working with iSQL*Plus
The iSQL*Plus Work Screen
Configuring iSQL*Plus
Client Configuration
Proxy Server Configuration
Middle Tier Configuration
Setting up the iSQL*Plus Server
Cascading Style Sheets
Starting and Stopping the HTTP Server
Enabling or Disabling iSQL*Plus
iSQL*Plus Security
Web Browser Globalization Setup
Server and Middle Tier Globalization Setup
Running iSQL*Plus
Substitution Variables
SQL*Plus Command Summary
Unsupported SQL*Plus Commands

Introduction to PL/SQL Programming

Features of PL/SQL
The Block Structure
Language Rules
Executing PL/SQL from SQL*Plus
Variables and Constants
Identifiers
Customizing SQL*Plus for PL/SQL Scripts
SELECT INTO Statement
Using Bind Variables
Interactive PL/SQL Programs
Oracle Datatypes are supported in PL/SQL
Numeric Datatypes
Datatype Conversion
Implicit and Explicit Conversion
%TYPE and %ROWTYPE
Subtypes
Subtype Declarations
Labels
Variable Scope and Visibility Rules
SQL and PL/SQL Reserved Words
Direction of PL/SQL
External Routines and Java Stored Procedures

Control Flow

Operations
Built-In Functions
Handling Exceptions
Working with Exceptions
SQLCODE and SQLERRM Functions
Predefined PL/SQL Exceptions
User-Defined Exceptions
Working with Exceptions
The Procedure Raise_Application_Error
IF Statements
Generic LOOP
Working with NULL
LOOP Labels
FOR LOOP
WHILE LOOP
GOTO Statement

Working with Cursors

Working with Cursors
Implicit Cursors and Attributes
Explicit Cursors and Attributes
Using Subqueries with Cursors
Using Cursor FOR LOOPs
Using %ROWTYPE with Cursors
Using Aliases and Subqueries
Using Parameters with Explicit Cursors
Modifying Data using Explicit Cursors
Managing Transactions in PL/SQL Programs

Procedures and Functions

Working with Subprograms
Named Subprograms
Software Evolution
Stored Procedures
Stored Procedure Syntax
Parameters
NOCOPY Compiler Directive
Parameter Default Values
Positional and Named Notation
Executing a Stored Procedure
Compiling and Debugging Stored Procedures
Compiling a Stored Procedure
Stored Function Syntax
Executing a Function from SQL*Plus
The RETURN Statement

Packages

Advantages of Packages
Packages
What can a Package Contain?
Components of a Package
Package Specification
Package Body
Defining Package Components
Package Scope
Public Scope
Private Scope
Accessing Package Components
Privileges
Managing Side Effects
Pragma RESTRICT_REFERENCES
Determining the Purity of a Routine in
Oracle8i
Adding TRUST to
RESTRICT_REFERENCES
Defining Subprograms within another
Subprogram
Forward Declarations

Overloading

Restrictions on Overloading
Restrictions on Packaged Variables