Intro to Oracle for Sybase & SQL Server Developers

Introduction to Oracle for SQL Server &

Sybase Developers

Length: 5 Days
Prerequisites: Programming experience in either a Sybase or SQL Server environment. Relational
Database and SQL experience should be carried over from Sybase or SQL Server.

Overview

This class is the starting point for all developers with either Sybase or SQL Server experience entering the world of Oracle application development. You will leverage your existing relational database skills to rapidly learn the critical aspects of Oracle development including Oracle architecture as well as working with SQL and SQL*PLUS. Differences between Oracle and Sybase or SQL Server are noted throughout.

The course had been designed specifically for developers who have a good background in SQL Server or Sybase, but little or no experience in Oracle. For example, we do not spend time teaching the ANSI standard SQL commands or other concepts and commands with which the students are already familiar. Instead we focus on Oracle’s extensions to SQL and highlight the differences between Oracle and SQL Server or Sybase. We even review the differences in architecture between the databases and how that affects softwaredevelopment. Hands-on workshops constitute approximately 50% of the class.

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

Understand Oracle's architecture and differences between Oracle and Sybase SQL Server
Navigate the Oracle data dictionary
Understand the difference between Oracle's SQL capabilities and those of Sybase and SQL Server
Utilize a wide range of Oracle's enhancements to SQL
Learn how to optimize SQL code
Understand the differences between PL/SQL in Oracle and Transact SQL in Sybase and SQL Server
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, bulk processing, array processing and error handling
Use bulk processing to write efficient PL/SQL programs
Use UTL_FILE to read and write to operating systems files
Create efficient server-side stored procedures, functions and triggers
Track and manage object dependencies
Create and maintain Oracle packages
Describe the new features of Oracle that address the Internet and very large databases

Audience: Application/software developers, database programmers

Outline

Chapter 1. Introduction to Oracle

What is the Oracle Database?
Principal Features
DDL Statements
DML Statements
Oracle Editions
New Features

Chapter 2. Intro to Oracle Workshop

Introduction to SQL *Plus
Development Environment
Connect to SQL *Plus
SQL *Plus Describe Command
SQL *Plus Connect Command
SQL SELECT Statement
Dual: The Oracle Work Table
Editing in SQL *Plus
Listing the Buffer Contents
Editing the Buffer Contents
Editor Commands
SQL *Plus Edit Command
Related SQL *Plus Scripts
Exit from SQL *Plus
۞ Sybase/SQL Server isql to SQL *Plus Command Equivalency

Chapter 3. Architecture

Database and Instance
Database Files
Segments and Extents
DBA_DATA_FILES
DBA_SEGMENTS
What is a Tablespace?
Why Tablespaces?
Redo Log Files
Control Files
Temp Files
Parameter Files
Password Files
V$BGPROCESS
Database Block Writer
Log Writer - LGWR
System and Process Monitor - SMON & PMON
Checkpoint - CKPT
Archiver - ARCn
PGA and UGA
The Oracle SGA
Library Cache
SQL Statement Processing
Data Dictionary
Buffer Cache
Database Block Size
Non-Standard DB Size
Keep and Recycle Caches
Redo Log Buffer
Database Block Size
Non-Standard DB Size
Keep and Recycle Caches
Large Pool/ Java Pool
Library Cache

Chapter 4. SQL *Plus Reporting & Scripting

What is the Data Dictionary?
Static Views
Common DBA Views
DICTIONARY View
Oracle/Sybase/SQL Server Data Dictionary Equivalents
۞ Oracle/Sybase/SQL Server Architectural Comparisons
SQL *Plus Reporting and 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
Controlling Column Format
COLUMN Example
Displaying Current Column Settings
Numeric Format Patterns
SQL*Plus BREAK Command
SQL*Plus COMPUTE Command
Create a Report Workshops

Chapter 5. Built-in Functions

Intro to Oracle Functions
Analytic Functions
Single-Row Functions
Character Functions
LOWER Function
CONCAT Function
TRIM Function
SUBSTR/INSTR Functions
SOUNDEX Function
TRANSLATE Function
String Manipulation Function Comparisons
Numeric Functions
ROUND Function
TRUNC Function
WIDTH_BUCKET Function
Numeric Functions Comparisons
Datetime Functions
CURRENT_x Functions
TRUNC Function
Date Arithmetic
DATE +/- NUMBER
DATE - DATE
ADD_MONTHS Function
Conversion Functions
Auto-Datatype Conversion
TO_CHAR Function
Datetime Conversion
TO_CHAR Function
TO_DATE Function
TO_TIMESTAMP Function
USER and SYSDATE
NULLIF Function
NVL Function
DECODE Function
Pseudo Columns
DUAL: The Oracle Work Table
Other Functions Comparisons

Chapter 6. Datatypes and Constraints

Object Names
Naming Rules
Creating Tables: Syntax
Creating Tables: Example
Creating Tables: Dictionary
Creating Tables: DESCRIBE
Built-In Datatypes
۞ Sybase and SQL Server Datatype Comparisons
CREATE TABLE AS SELECT
CTAS Options
Introduction to Constraints
Constraint Names
Constraints Example
Disabling Constraints
Enabling Constraints
Constraints and the Data Dictionary
Altering Table Structure
Adding Columns
Adding Constraints
DROP COLUMN
Dropping Tables
Tables and Constraints Workshop
Temporary Table Concepts
Sybase / MS SQL Server vs. Oracle Temporary Table Comparisons
Creating Temporary Tables
Temporary Table Limitations
CTAS and Temporary Tables Workshop
Metadata Access
Metadata Access Workshop

Chapter 7. Sequences, Views & Synonyms

CREATE SEQUENCE Syntax
CREATE SEQUENCE Example
ALTER SEQUENCE Example
Referencing Sequences
Using a Sequence to Generate a Primary Key
Caching of Sequences
Sequences Summary
Sequences Workshop
Views
What is a Synonym?
Private Synonyms
Public Synonyms
CREATE SYNONYM Examples
Synonym Search Sequence
The Data Dictionary

Chapter 8. Data Manipulation & Transaction Control

Inserting Rows
Rounding on Insert
Returning Values from DML
Inserting Multiple Rows
Multi-Table INSERT
Deleting Rows
TRUNCATE Command
Updating a Single Row
Updating Multiple Rows
MERGE statement
Transaction Control
COMMIT & ROLLBACK
Transaction Control Who Sees What?
Transaction Control Locking Mechanisms
Savepoints
Commits in SQL*Plus
Setting AUTOCOMMIT
۞ Compare Oracle / Sybase / SQL Server Oracle Transactions
۞ Sybase vs. Oracle Locking, SQL Server vs. Oracle Locking

Chapter 9. Indexes

Introduction to Indexes
B-Tree Indexes
B-Tree Structure
ROWID Structure
Index Creation
Function-Based Indexes
Descending Indexes
Cost Based Optimization
۞ Contrast Indexes in Oracle / Sybase / SQL Server
Indexes Workshop
DECODE and CASE
Introduction to DECODE
Simple Example
Flip Table on Side
DECODE and Range Comparisons
The CASE Expression
CASE Syntax
CASE Nonsense
Histograms with CASE
DECODE and CASE Workshop
Flashback Query
Concepts
Session Level Flashback
Statement Level Flashback
Flashback Tips
Flashback Limitations

Chapter 9. Subqueries

Subquery Concepts
Restrictions
Rules
Subquery IN Operator
Subquery Gotcha
The need for NOT EXISTS
EXISTS Operator
Top-N Queries
Subquery Support
Subquery Factoring Claus

Chapter 10. ANSI Joins

Review of Join Concepts
OuterJoins
ANSI Compliant Joins
USING Clause
ON Clause
ANSI Outer Joins
Full Outer Joins
ANSI Cross Join

Chapter 11. External Tables

Concepts
Directory
Creating the Table
Query the Table
Tips
Limitations

Chapter 12. Introduction to SQL Tuning

Cost based Optimizer (CBO)
Explain Plan
PLAN_TABLE
DBMS_XPLAN
Reading Execution Plans
Autotrace

Chapter 13. Appendix A: Concurrency Control

Introduction to Locks
Row Lock Architecture
Basic Locking Rules
DML & DDL Locks
Locking Issues: Lost Update
Locking Issues: Blocking
Locking Issues: Deadlock
Deadlocks: Cause and Fix

Chapter 14. Appendix B: Transaction Control

Transaction Review
Supported Statements
Statement Level Read Consistency
Transaction Level Read Consistency
Oracle Isolation Levels
Phantom and Non-Repeatable Reads
Read Committed
Serializable
Read Only
Savepoints
Implicit Commits

Chapter 15. Appendix C Introduction to XMLDB

XML in the Database
XML Type in Tables
Inserting XML
Selecting XML
EXTRACT Functions
EXTRACTVALUE Functions
Updating XML Data
PL/SQL and XML
Other XML Features

Chapter 16 Appendix D: Overview of Data Warehouse

Introduction to Data Warehousing
The Star Schema
The Snowflake Schema
Snowflake vs. Star
Architecture Decisions
Required Database Features
Software Configurations
Hardware Configurations
Instance Configurations
Analytic Functions
PARTITION Clause
ORDERBY Clause
Row Windows
Range Windows
Ranking Functions
Top-N Queries
LAG and Lead
Lists of Functions
Executing SQL *Loader
The Control File
Variable Format Data
LOG File
Conditional Loads

Chapter 17 Appendix E: Java Support

Calling Java from Oracle
Loading Java Classes into Oracle
Writing PL/SQL Wrappers to Call Java Methods
Other Java Support

Chapter 18. Introduction to PL/SQL

What is PL/SQL?
Why Use PL/SQL
PL/SQL Program Structure
۞ Comparison of PL/SQL to Transact SQL
Anonymous Blocks
Compile Errors
Output to SQL *Plus
Procedures
Procedure Compile Errors
Functions
Packages
Triggers
Tools for Development
Working in SQL *Plus

Chapter 19. Language of Fundamentals

Statements
PL/SQL Symbol
Quoting Mechanism
Common PL/SQL Datatypes
Declaring Variables
%TYPE
PL/SQL Records
Records: %ROWTYPE
Programmer Defined Records
Variable Scope
Nested Blocks
Functions
Regular Expressions
IF Statement
CASE Statement
Simple Expression
Simple Loops
Nested Loops
Numeric FOR Loop
PL/SQL Arrays
Array Methods
Using Arrays for Processing Speed

Chapter 20. PL/SQL and SQL : Basics

SELECT INTO Statement
Implicit Cursor Loops
DML in PL/SQL
Cursor Attributes
Native Dynamic SQL (NDS)
Embedding DDL

Chapter 21. PL/SQL and SQL: Cursors

What is a Cursor?
Cursor Processing
Cursor Attributes
Dynamic Cursors
Variable Scope & Cursors
Cursor Parameters
Cursor Record
Cursor FOR LOOP
Referencing the Current Row

Chapter 22. PL/SQL and SQL: Bulk Processing

Bulk Processing
Bulk Collect
Bulk: SELECT INTO, DELETE, FETCH with LIMIT, INSERT
FORALL Improvements
Returning into Arrays

Chapter 23 Procedures

What is a Procedure?
Abbreviated Syntax
Compiler Warnings
Optimizing Complier
Native Compilation
Procedure Signatures
Calling Procedures
Procedure Synonyms
Referencing Parameters by Name
Returning Sets: REF CURSOR
Dropping Procedures
AFTER COMPILE
Privileges

Chapter 24. Functions

What is a Function?
Abbreviated Syntax
Calling Functions
Table Functions
Pipelined Functions
Test Data Generator

Chapter 25. Error Handling

Types of Errors
Runtime Errors
Exception Concepts
Predefined Named Exceptions
Handling Exceptions
Logging Exceptions
RAISE_APPLICATION_ERROR
Raising Exceptions
EXCEPTION_INIT

Chapter 26. Packages

Concepts
Package Benefits
Package Contents
Package Body Syntax
Privileges
Calling Packaged Objects
Initialization Code
Session Variables
package Body Variables
Package Overloading
Compiling Packages
DROP Package
Supplied Packages

Chapter 27. Triggers

Concepts
Trigger Execution
Create Trigger Syntax
Row Trigger
OLD Reference Variable
NEW Reference Variable
Trigger Attributes
Audit Trigger
Derived Value Trigger
Restrictions
Security Privileges
Statement Level Trigger
Autonomous Trigger
INSTEAD OF Triggers
Sybase/MS SQL Server vs. Oracle Triggers

Chapter 28. File I/O Using UTL_FILE

UTL_FILE Concepts
INSetup
Unix File Permissions
Opening, closing, reading and Writing Files
Writing Files - PUT, PUT_LINE, NEW_LINE, PUTF

Chapter 29. Overview of UTL_MAIL and other PL/SQL Built-in Packages

UTL_MAIL
UTL_MAIL Setup
Statement Level Read Consistency
Transaction Level Read Consistency