Oracle Training : Introduction to Oracle8i for Sybase Developers

This class is the starting point for all developers with Sybase experience entering the world of Oracle application development. You will leverage your existing Sybase skills to rapidly learn the critical aspects of Oracle development including working with SQL*Plus, writing PL/SQL programs, creating stored procedures, calling Oracle Supplied Packages, creating Java procedures and creating triggers. Differences between Oracle and Sybase are noted throughout. Hands-on workshops constitute approximately 50% of the class. An overview of Oracle architecture is also provided.

Oracle Training Prerequisites

Programming experience in a Sybase environment. Relational database and SQL experience should be carried over from Sybase.

Oracle Training Course duration

5 Days

Oracle Training Objectives

After successfully completing this course, you will be able to:

Understand Oracle's architecture and differences between Oracle and Sybase
Utilize SQL*Plus scripting and report generation features
Navigate the Oracle data dictionary
Create database objects such as sequences, object types and collections
Code PL/SQL programs that include common programming constructs such as data-typing, variable assignment, flow control, cursor handling, and error handling
Create server-side stored procedures, functions and triggers
Publish Java programs in the database and call them from Oracle
Create and maintain Oracle packages
Understand and use SQL Tuning techniques
Create and manipulate large objects and BFILE data types
Implement job scheduling using the DBMS_JOB package
Create and use materialized views
Use data warehousing summary functions such as ROLLUP and CUBE
Successfully master the material for exams 1 and 2 of the Oracle Certified Application Developer Program.

Oracle Training Course outline

Introduction to Oracle8i

Oracle8i - The "Internet Database"
Oracle Editions
8i Releases
And Now, Oracle9i
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

Hardware/Software Requirements

Hardware Requirements

Minimal Processor: Pentium 166 or Pentium 200
Recommended Processor: Pentium 233 or Pentium 266
RAM: 128 MB (256 MB recommended)

Oracle Corporation also recommends increasing virtual memory to 200 MB. (Modify it in the Performance tab of System Properties in the Control Panel.)

Available Disk Space (FAT or HTFS): 3 GB
Video: 256 color
A connection to the Internet is desirable (but not absolutely necessary) to show students various Oracle-related sites.

Software Requirements

Windows NT 4.0 or Windows 2000
Oracle8i 8.1.6 or above
Java JDK 1.1.5 or above*
Microsoft Internet Explorer 5 or Netscape Navigator 4.5
Adobe Acrobat Reader or Acrobat 4.0 or later (for the course presentation)*
Student Files
Text Editor*

Please contact your training representative for more details on having this course delivered onsite or online

Training Outlines - the one stop shopping center for IT training.
© Training Outlines All rights reserved
Copyright © SB