hrs Specialised Training to meet your needs

nfsql

New features in Oracle 8i/9i - SQL and PL/SQL

Assumptions and pre-requisites

Delegates are programmers with some experience of programming in Oracle 7 and/or using basic PL/SQL and SQL in Oracle 8.

Course Outline

Overview of the Oracle 9i Product Set and Documentation

General SQL changes

ANSI Joins
NULLs in ORDER BY
TIMESTAMP/TIMEZONE
New ROWID
INSERT/UPDATE with default values
Merging Data
Removing columns from tables
External Tables
Setting foreign key to NULL when parent deleted
Deferred constraints
Enforced constraints
COALESCE
CASE
SYSTIMESTAMP
NULLIF
Subqueries in the FROM clause
Subqueries as expressions
TRIM

General PL/SQL Changes

CASE
INSTEAD OF triggers
Autonomous PL/SQL block
Invokers Rights
Dynamic SQL in PL/SQL
NOCOPY
SQL Returning

"Data Warehousing"-style changes to SQL

(to cater for large amounts of data, and/or heavy use of aggregated data)

PERCENTILES
New aggregate functions - CUBE, ROLLUP
Analytic Functions - RANK
Partitioned Tables
Partitioned Indexes
Materialized Views
Query Rewrite

New Types on Indexes

Reverse Key
IOT
Function based

Overview on Working with Objects

Creating object types
Creating object columns, REF columns, and object tables
Varrays and Nested Tables
Performing SELECT, INSERT, UPDATE, and DELETE on objects
Object methods

Overview on Working with Large Objects

Creating LOBs
LOBs and PL/SQL

Overview of working with XML in PL/SQL

New datatype XMLType
Using the XML SQL Utility (XSU)
- DBMS_XMLQuery
- DBMS_XMLGEN
- DBMS_XMLSave
Using DOM and XPath
- XMLDOM
- XMLPARSER
- XSLPROCESSOR
Introduction to InterMedia Oracle Text
- using cyxsys
- CONTAINS

[RETURN TO LIST OF COURSES]

Send mail to pleasering@oracle-training.co.uk with questions or comments about this web site.
Copyright © 2003 hrs
Last modified: February 11, 2003