Introduction to Oracle 10g Programming Training in South Portland, Maine

1.  Relational Database and SQL

Overview

Review of Relational Database

Terminology

Relational Database Management

Systems

Introduction to SQL

Oracle Versioning and History

Logical and Physical Storage Structures

Connecting to a SQL Database

Datatypes

Sample Database

2.  Using Oracle SQL*Plus

SQL*Plus

The SQL Buffer

Buffer Manipulation Commands

Running SQL*Plus Scripts

Tailoring Your SQL*Plus Environment

Viewing Table Characteristics

SQL*Plus Substitution Variables

Interactive SQL*Plus Scripts

SQL*Plus LOB Support

Using iSQL*Plus

Graphical Clients

3.  SQL Queries - The SELECT Statement

The SELECT Statement

The CASE...WHEN Statement

Choosing Rows with the

WHERE Clause

NULL Values

Compound Expressions

IN and BETWEEN

Pattern Matching: LIKE and

REGEXP_LIKE

Creating Some Order

4.  Scalar Functions

SQL Functions

Using SQL Functions

String Functions

Numeric Functions

Date Functions

Date Formats

Conversion Functions

Literal Values

Intervals

Oracle Pseudocolumns

5.  SQL Queries - Joins

Selecting from Multiple Tables

Joining Tables

Self Joins

Outer Joins

6.  Aggregate Functions and Advanced

Techniques

Subqueries

Correlated Subqueries

The EXISTS Operator

The Aggregate Functions

Nulls and DISTINCT

Grouping Rows

Combining SELECT Statements

7.  Data Manipulation and Transactions

The INSERT Statement

The UPDATE Statement

The DELETE Statement

Transaction Management

Concurrency

Explicit Locking

Data Inconsistencies

Loading Tables from External Sources

8.  Data Definition and Control Statements

Datatypes

Defining Tables

Constraints

Inline Constraints

Modifying Table Definitions

Deleting a Table Definition

Controlling Access to Your Tables

9.  Other Database Objects

Views

Creating Views

Updatable Views

Sequences

Synonyms

10.  Triggers

Beyond Declarative Integrity

Triggers

Types of Triggers

Row-Level Triggers

Trigger Predicates

Trigger Conditions

Using SEQUENCEs

Cascading Triggers and Mutating Tables

Generating an Error

Maintaining Triggers

11.  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

12.  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

13.  Stored Procedures and Functions

Stored Subprograms

Creating a Stored Procedure

Procedure Calls and Parameters

Parameter Modes

Creating a Stored Function

Stored Functions and SQL

Invoker’s Rights

14.  Exception Handling

SQLCODE and SQLERRM

Exception Handlers

Nesting Blocks

Scope and Name Resolution

Declaring and Raising Named

Exceptions

User-Defined Exceptions

15.  Records, Collections, and User-Defined Types

Record Variables

Using the %ROWTYPE

Attribute

VARRAY and Nested TABLE

Collections

Using Nested TABLEs

Using VARRAYs

Collection in Database Tables

Associative Array Collections

Collection Methods

Iterating Through Collections

16.  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

17.  Bulk Operations

Bulk Binding

BULK COLLECT Clause

FORALL Statement

FORALL Variations

Bulk Returns

Bulk Fetching with Cursors

18.  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

19.  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

20.  Working with LOBs

Large Object Types

Oracle Directories

LOB Locators

Internal LOBs

External LOBs

Temporary LOBs

The DBMS_LOB Package

21.  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

22.  Appendix A - The Data Dictionary

Introducing the Data Dictionary

DBA, ALL, and USER Data

Dictionary Views

Some Useful Data Dictionary

Queries

23.  Appendix B - 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

24.  Appendix C - PL/SQL

Versions, Datatypes,

and Language Limits

25.  Appendix D – Oracle 10g

Supplied Packages