Introduction to Oracle 10g
What is the Oracle Database?
Enterprise Edition
Standard Edition
Express Edition (10g)
Options
Oracle Architecture Overview
Common Schema Objects
Workshop
|
Introduction to SQL*Plus
SQL*Plus: The Development Environment
Connect to the Database
SQL*Plus Describe Command
Customizing Your Environment (LOGIN.SQL and Predefined Variables) (10g)
Executing Queries in SQL*Plus
Spooling Output
Creating and Running Scripts
Querying and Purging the Recycle Bin (10g)
Workshop
|
Datatypes
General Oracle Datatypes
Datetime Datatypes
XML Data Type
Miscellaneous Data Types
Workshop
|
Creating Tables and Indexes
Naming Rules
Listing Oracle Objects using the Data Dictionary
Creating a Table
Creating Tables from Other Tables (CTAS)
Introducing Constraints
Naming Constraints
Disabling and Enabling Constraints
Listing Constraints using the Data Dictionary
Altering Table Structure
Dropping Tables
Privileges Required to Create a Table
Creating a Table in Another Schema
Creating a Synonym on a Table
Introduction to Indexes
Creating Indexes
Index Use - Who Decides?
Workshop
|
Querying the Database, Part I - Select
Simple SELECT Statement
Eliminating Duplicate Rows with DISTINCT
Calculated Columns and Column Aliases
Sorting Results Sets
Case (and Accent) Insensitive Sort (10g)
Conditions and Comparison Operators
Quoting Text Strings (10g)
Logical Operators
Search Lists: IN
Search Range: BETWEEN
Search Patterns: LIKE
IS NULL Operator
Using the ANSI Compliant CASE Expression
Introduction to Regular Expressions in SQL (10g)
Pattern Matching with REGEXP_LIKE (10g)
Using REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR (10g)
Workshop
|
Oracle 10g Row Functions
Introduction to Functions
String Manipulation Functions
Case Conversion Functions
Concatenation Function
Character Conversion Functions
Date Conversion Functions
Datetime Conversions
Time Zone Functions
Date Arithmetic & Functions
Oracle Pseudo Columns (ROWNUM)
Workshop
|
Querying the Database, Part II - Joins
Inner Joins
Using Table Aliases in Joins
Outer Joins
ANSI Compliant Joins
ANSI Natural Join
ANSI Inner Join
ANSI Outer Joins
Full outer joins
ANSI cross join
|
Querying the Database, Part III - Aggregate Functions
Aggregate (Group) Functions
GROUP BY Clause
Grouping Multiple Columns
Golden GROUP BY Rule
HAVING Clause
ROLLUP
CUBE
GROUPING Function
Workshop
|
Querying the Database, Part IV - Subqueries
Subquery Concepts
Subquery Restrictions
Subquery Rules
Subquery IN Operator
Subquery EXISTS Operator
NOT EXISTS Operator
Subquery in FROM Clause
Extended Subquery Support
Named Subqueries
Workshop
|
Querying the Database, Part V - Set Operators
UNION Operator
INTERSECT Operator
MINUS Operator
Workshop
|
Data Manipulation
Inserting Rows
Returning Values from DML Statements
Returning Aggregates (10g)
DML Error Logging (10g)
Inserting Multiple Rows
9i Multi-Table INSERT
Introduction to Oracle Sequences
Using a Sequence to Generate a Primary Key
Deleting Rows
TRUNCATE Command
Updating a Single Row
Updating Multiple Rows
MERGE statement
10g MERGE Enhancements (10g)
Workshop
|
Transaction Control
Transactions
Oracle Transaction Statements
Batch Commits (10g)
Statement Level Read Consistency
Transaction Level Read Consistency
Oracle Isolation Levels
Phantom and Non-Repeatable Reads
Savepoints
Implicit Commits
Workshop
|
Concurrency Control
Introduction to Locks
Row Lock Architecture
Basic Locking Rules
DML Locks
DDL Locks
Locking Issues: Lost Update, Blocking and Deadlocks
Deadlocks: Causes and Fixes
Workshop
|
Oracle Views
What is a View?
Creating Views
Updateable Views
Read Only Views
Views & The Data Dictionary
Workshop
|
Introduction to Oracle Security
Object Privileges
System Privileges
Oracle Roles
Determining Privileges Held
Workshop
|
Introduction to PL/SQL
What is PL/SQL?
Why Use PL/SQL?
PL/SQL Program Structure
Anonymous Blocks
Sending Output to SQL*Plus
Introduction to Procedures
Procedure Compiler Errors
Procedure Compiler Warnings (10g)
Introduction to Functions
Introduction to Packages
Querying the Data Dictionary
Introduction to Triggers
Tools for PL/SQL Development
Working in SQL*Plus
Workshop
|
Language Fundamentals
PL/SQL Statements
PL/SQL Symbols
Quoting Mechanism (10g)
Common PL/SQL Datatypes (10g Enhancements)
Declaring Variables
%TYPE Attribute
PL/SQL Records
%ROWTYPE Attribute
Programmer Defined Records
Variable Scope
Nested Blocks
Functions
Regular Expressions (10g)
IF Statement
CASE Statement and Expression
Simple CASE
Searched CASE
Simple Loops
Nested Loops
Numeric FOR Loop
PL/SQL Arrays
Simple Array Example
Array Methods
Workshop
|
PL/SQL and SQL, Part I Basics
SELECT INTO Statement
Implicit Cursor Loops
DML in PL/SQL
Cursor Attributes
Embedding DDL
Workshop
|
PL/SQL and SQL, Part II Cursors
What is a Cursor?
Explicit Cursor Processing
Cursor Attributes
Dynamic Cursors
Dynamic Cursor Example
Variable Scope & Cursors
Cursor Parameters
Cursor Records
Cursor FOR LOOP
Updating the Current Row
Workshop
|
PL/SQL and SQL, Part III Bulk Processing
Bulk Processing
Bulk Collect
Bulk DML - FORALL (10g Enhancements)
Bulk DELETE, INSERT and UPDATE
Returning into Arrays (10g Enhancements)
Workshop
|
Stored Procedures
What is a Stored Procedure?
CREATE PROCEDURE Syntax
Simple Example
Review: Compile Errors
Procedure Signatures
Calling Procedures
Procedure Synonyms
Referencing Parameters by Name
Returning Sets: REF CURSOR
Dropping Stored Procedures
Procedure Dependencies
ALTER COMPILE Statement
PL/SQL Procedure Privileges
Granting Execute Privilege
The Data Dictionary
Workshop
|
PL/SQL Functions
What is a Function?
CREATE FUNCTION Syntax
Simple Example
Calling Functions
The Data Dictionary
Table Functions
Building a Table Function
Using a Table Function
Pipelined Functions
Workshop
|
Error Handling
Types of Errors
Runtime Errors
Predefined Named Exceptions
EXCEPTION Block Syntax
Handling Exceptions
Using FORMAT_ERROR_BACKTRACE (10g)
Recovering from Errors
Logging Exceptions
RAISE_APPLICATION_ERROR Statement
Raising Exceptions
Raising User Exceptions
EXCEPTION_INIT Pragma
Workshop
|
PL/SQL Packages
Package Concepts
Package Privileges
Calling Packaged Objects
Initialization Code
Session Variables
Package Body Variables
Package Overloading
Compiling Packages
DROP PACKAGE Statement
Summary of Oracle Supplied Packages
Workshop
|
DML Triggers
DML Trigger Concepts
Trigger Execution
Row Triggers
:OLD and :NEW Reference Variables
Trigger Attributes
Trigger Restrictions
Table Triggers
Autonomous Transactions
Autonomous Triggers
INSTEAD OF Triggers
Workshop
|
Trace and Tune PL/SQL
Introduction to Tuning PL/SQL
DBMS_PROFILER (10g Enhancements)
Profiler Setup
Using Profiler
Reporting with PROFSUM
Effective Use of Bind Variables
Benchmarking with GET_TIME and GET_CPU_TIME (10g)
Workshop
|
File I/O Using UTL_FILE
UTL_FILE Concepts
Setup for UTL_FILE
Unix File Permissions
Opening Files
Reading and Writing Files
Closing Files
Workshop
|
Sending Email with UTL_MAIL (10g)
Introduction to UTL_MAIL
Setup for Emailing from the Database
UTL_MAIL Example
Workshop
|
Appendices
Table Descriptions
Oracle Data Dictionary
|