Topic Summary

• Introduction to Oracle9i
  • Oracle8i - The "Internet Database"
  • Oracle Editions
  • 8i Releases
  • And Now, Oracle9i
  • 9i Releases
  • SQL and PL/SQL
  • Compilers and API's
  • Additional Important Features and Options
  • Related Products
• Oracle Architecture
  • Instance and Database
  • Oracle and Sybase Terminology Comparisons
  • Understanding Tablespace and Schema
  • Oracle and Sybase Terminology Comparisons
  • Oracle Object-Oriented Support
  • Major Oracle Relational Objects
  • ORACLE PL/SQL Objects

• SQL*Plus Basics
  • Development Environment
  • Connect to SQL*Plus
  • SQL*Plus Connect Command
  • SQL*Plus Host Command
  • SQL SELECT Command
  • Editing in SQL*Plus
  • Listing the Buffer Contents
  • SQL*Plus Edit Command
  • Related SQL*Plus Commands
  • Editing: A Better Way
  • Running SQL*Plus Scripts
  • Exit from SQL*Plus
  • isql to SQL*Plus Equivalency Commands
• SQL*Plus Scripting
  • Creating SQL*Plus Reports
  • SQL*Plus Sample Report Script
  • Executing SQL*Plus Reports
  • Report Output
  • Spooling Output
  • Controlling the SQL*Plus Environment
  • Set Commands
  • Customizing Your SQL*Plus Environment
  • Page Header (ttitle)
  • Header and Footer System Variables
  • Multi-Line ttitle
  • Reformatting Columns
  • COLUMN Example
  • Displaying Current Column Settings
  • Numeric Format Patterns
  • SQL*Plus BREAK Command
  • SQL*Plus COMPUTE Command

• Datatypes & SQL Extensions
  • Datatypes.
  • Sybase Datatype Comparisons
  • Datatype Conversion
  • Dual: The Oracle Work Table
  • Pseudo Columns
  • Pseudo Columns: user, sysdate, uid, null, level
  • Pseudo Columns: rowid and rownum
  • Case Conversion Functions
  • TRIM Function
  • substr Function
  • DECODE Function
  • ROUND Function
  • TRUNC Function
  • Nulls and the NVL Function
  • Character Conversion Function
  • TO_CHAR number to character Example
  • Date Conversion Functions
  • TO_CHAR character date to Oracle date Example
  • TO_DATE Examples
  • String Manipulation Function Comparisons
  • Numeric Functions Comparisons
  • Other Functions Comparisons
• Tables, Sequences & Constraints
  • CREATE TABLE Syntax
  • CREATE TABLE Example
  • Creating Tables from Other Tables
  • CTAS Options
  • CTAS Additional Constraints
  • Temporary Table Concepts
  • Creating Temporary Tables
  • Temporary Table Limitations

• Query Tuning Basics
  • The Tuning Process
  • How Oracle Processes SQL
  • Query Optimization
  • The Cost Based Optimizer
  • Collecting Statistics
  • EXPLAIN PLAN
  • The Plan Table
  • PLAN_TABLE Output
  • Interpreting the Execution Plan
  • Automating Explain Plans
  • AUTOTRACE Output
  • The Shared Pool
  • Sizing the Shared Pool
  • Finding Problem Queries using V$SQLAREA
• What is PL/SQL?
  • What is PL/SQL?
  • PL/SQL Capabilities & Restrictions
  • Types of PL/SQL Programs
  • PL/SQL Subprograms
  • PL/SQL Storage & Execution
  • PL/SQL Program Structure
  • Anonymous Block Example
  • Anonymous Block Compile Errors
  • PL/SQL Output to SQL*Plus
  • Tools for PL/SQL Development

• PL/SQL Language Constructs
  • PL/SQL Delimiters/Operators
  • PL/SQL Datatypes
  • Declaring Variables
  • Naming Variables
  • Variable Scope
  • Nested PL/SQL Blocks & Variable Scope
  • Using SQL Functions
  • %type
  • %rowtype
  • SELECT INTO Statement
  • SELECT INTO Statement Example
  • RETURNING Clause
  • RETURNING Clause Example
  • IF Statement
  • Labels and goto Statement
  • Simple Loops
  • Nested Loops
  • Numeric For Loop
  • While Loop
• Cursors
  • What is a Cursor?
  • Explicit Cursor Processing
  • Step 1. Declare the Cursor
  • Step 2. Open the Cursor
  • Step 3. Fetch the Data
  • Step 4. Close the Cursor
  • Explicit Cursor Attributes
  • Implicit Cursor Attributes

• PL/SQL Stored Procedures & Functions
  • What is a Procedure?
  • CREATE PROCEDURE Syntax
  • CREATE PROCEDURE Example
  • Executing Procedures
  • Dropping And Re-Creating Procedures
  • Procedure Signatures
  • Calling Procedures
• Error Handling
  • Types of Errors
  • Runtime Errors
  • Exceptions
  • Exceptions - Concepts
  • Types of Error Handling
  • Predefined Exceptions
  • Exceptions - Syntax
  • Exceptions Example
  • Other Oracle Errors
• Triggers
  • Trigger Concepts
  • DML Trigger Execution
  • Database Event Trigger Execution
  • Trigger Syntax
  • ROW Trigger Example
  • :OLD Reference Variables
  • :NEW Reference Variables
  • Row Trigger Example Using :NEW
  • Trigger Attributes
  • Audit Trigger Example
  • Derived Value Trigger Example
  • Compile Errors
  • STATEMENT Trigger
  • INSTEAD OF Triggers
  • Autonomous Transactions

• Packages
  • PL/SQL Package Contents
  • What Are Packages?
  • Package Concepts
  • Why Use Packages?
  • CREATE PACKAGE Syntax
  • CREATE PACKAGE Example
  • CREATE PACKAGE BODY Syntax
  • CREATE PACKAGE BODY Example
  • Privileges And Packages
  • Calling Package Subprograms from a SQL*Plus session
  • Calling Package Subprograms from a PL/SQL Block
  • Package Initialization Code
  • Global Variables
  • Package Body Variables
  • Package Overloading
  • Package Overloading Example
  • Compiling Packages
  • DROP Package
  • Oracle Supplied Packages
• Dynamic SQL & PL/SQL
  • What is Dynamic SQL?
  • NDS vs. DBMS_SQL
  • NDS Statement Summary
  • NDS Single-Row Query Syntax
  • NDS Single-Row Insert Example
  • NDS Multi-Row Query Syntax
  • NDS Multi-Row Query Example
  • NDS PL/SQL Example
  • More NDS Examples

• Oracle Objects
  • The Object-Relational Model
  • Object Types
  • Object Type Components
  • Creating Object Types
  • Objects
  • Creating a Table with an Object Type
  • Referencing Objects
  • Constructor Method
  • Constructor Method Example
  • Inserting Data into Composite Objects
  • Object Type Indexes
  • Object Type Indexes Example
  • Object Tables
  • Dropping Types
  • DROP with FORCE
  • Object Data Dictionary Views
• Tables and Records
  • PL/SQL Records
  • PL/SQL Tables
  • Table Example
• PL/SQL Collection Types
  • What are Collections?
  • What is a VARRAY?
  • VARRAY Example
  • What is a Nested Table?
  • Nested Table Example
  • Inserting into Collections
  • Querying Collections
  • CURSOR Operator
  • DML with Nested Tables
  • Initializing Collections Within DML
  • Initializing Collections Within PL/SQL
  • Referencing Collections
  • Collection Assignment
  • Comparing Collections
  • Nested Tables and Varrays: Differences
  • Built-in Methods
  • Available PL/SQL Exceptions
  • Collections Dictionary Views

• LOB & BFILE Handling
  • What are LOBs?
  • LOB Types
  • LOB vs. LONG
  • Manipulating Internal LOBs
  • Creating LOBs
  • Updating LOB Columns
  • DBMS_LOB
  • Reading LOBs
  • DBMS_LOB.READ Example
  • Comparing LOBs
  • DBMS_LOB.COMPARE Example
  • Getting the Length of LOBs
  • Finding Positions of Patterns
  • DBMS_LOB.INSTR Example
  • Returning SubStrings
  • Writing LOBs
  • Appending LOBs
  • DBMS_LOB.APPEND Example
  • Copying LOBs
  • Erasing LOBs
  • LOB Loading from a File
  • Trimming LOBs
  • Temporary LOBs
  • 8i LONG to LOB Conversion
• Using DBMS_JOB
  • What is DBMS_JOB?
  • Submitting a Job
  • SUBMIT Example
  • Running a Job
  • Changing Job Parameters
  • Cleaning Up Broken Jobs
  • Removing a Job
  • Viewing Job Information

• Data Warehousing Support
  • What is a Data Warehouse?
  • The Star Schema
  • Star Schema Design
  • The Snowflake Schema
  • Snowflake Schema Design
  • New Data Warehousing Functionality
  • Using ROLLUP
  • Using CUBE
  • GROUPING Function
  • GROUPING Example
  • What is Summary Management?
  • What is a Materialized View?
  • M-view Concepts
  • Creating a Materialized View
  • Seeing the Effect
  • Indexing M-Views
  • Using DBMS_OLAP
  • Other Notable Things
• Java Support
  • The Java Language
  • Oracle's Java Support
  • Java Installation
  • Oracle8i Components and Commands for Java
  • Privileges for Java
  • JDK Installation
  • Calling Java from Oracle
  • Step 1. Build a Java class and appropriate methods
  • Step 2. Compile the class
  • Step 3. Load the class into the Oracle database
  • Step 4. Build PL/SQL programs to call the methods
  • Test the Hello_world Function
  • Oracle API's Providing Java Access to SQL Data
  • What is JDBC?
  • Oracle's JDBC Drivers
  • JDBC Code Example
  • What is SQLJ?
  • SQLJ Components
  • SQLJ Code Example

• Resumable Space Management
  • Introduction to Resumable Space Management
  • Errors Handled
  • Enabling RSM
  • Monitoring RSM
  • Storage Allocation Correction
  • After Suspend Trigger
  • Import and SQL* Loader
  • RSM Restrictions
  • Resources
• Misc Developer Features
  • SQL Case Statement Types
  • The Simple Case Statement
  • The Searched Case Statement
  • Flashback Queries
  • Flashback Tip
  • Flashback Limitations
  • New SQL features
  • Named Subqueries
  • MERGE statement
  • Multi-Table INSERT
  • ANSI Compliant Joins
  • ANSI Natural Join
  • ANSI inner join
  • ANSI outer joins
  • Full outer joins
  • ANSI cross join
  • Additional PL/SQL features
  • Metadata Access
  • External Tables - Concepts
  • External Tables - Use