Oracle Database 10g: SQL and PL/SQL New Features

What you will learn

In this course, students learn about the new binary-float data types enhancements in Oracle Database 10g. Students also identify the new Large Objects (LOB) enhancements. Students learn the new enhancements to nested collections, table functions, versioning of rows, the flashback feature, and many other SQL enhancements. Participants learn how to use regular expressions to search for and manipulate simple and complex patterns in string data.

Increase productivity, increase return of investment and extend the functionality of your already feature-rich database. Students use the new PL/SQL compiler initialization parameters, use conditional compilation, hide PL/SQL source code using dynamic obfuscation, and identify the new enhancements in bulk binding. Students also use the SQL Model clause to perform spreadsheet like calculations. Finally, some aspects of SQL for data warehousing are covered. Students learn the enhancements made to the MERGE statement and they learn how to use the time series enhancements in SQL retrieval statements. Demonstrations and hands-on practices reinforce the concepts and new features presented.
Audience
 Application Developers
Business Intelligence Developer
Data Warehouse Developer
Developer
Functional Implementer
PL/SQL Developer
Support Engineer
System Analysts

Prerequisites
 Oracle 9i or earlier RDBMS SQL and PL/SQL Knowledge
SQL*Plus or iSQL*Plus tools

Course Objectives
 Use the Oracle data types and data enhanced data types
Identify the Large Objects (LOB) enhancements
Write code that uses the nested table and VARRAY enhancements
Identify and use the SQL enhancements
Use Regular Expressions to search for and manipulate complex patterns in string data
Perform a case-insensitive or accent-insensitive linguistic sort using NLS_SORT
Perform a linguistic comparison using NLS_COMP
Identifying the New PL/SQL Compilation Initialization Parameters
Setting and using the New PL/SQL Compile Time Warnings for Subprograms
Use conditional compilation
Obfuscate PL/SQL source code
Use the SQL model clause
Use Analytic Functions in the SQL model clause
Use SQL enhancements for Data Warehousing
Cleanse data using the DELETE clause
Densify data with partitioned outer joins

Course Topics
Using Oracle Database 10g Data Types
 Working with the New BINARY_FLOAT and BINARY_DOUBLE Numeric Data Types
The Floating-Point Special Values
Using Comparison Operations on Binary-Floats
Using the Floating-Point Literals
Using the Floating-Point Functions
Using Conversion Operations on Binary-Floats
Examining Binary-Float Performance
Supporting NCHAR String Literals

Using Large Objects (LOB) Enhancements
 Identifying the Large Objects Enhancements
Migrating from LONG to LOB
Using the DBMS_LOB Package
Initializing, Populating, and Removing LOB Columns
Selecting CLOB Values by Using SQL and DBMS_LOB
Conversion Between CLOB and NCLOB
Data Interface for LOBs in Abstract Data Types and Remote LOBs
Support for LOB Array Read and Write

Using Nested Table and VARRAY Enhancements
 Collections: Overview
Adjusting the Size of an Element Type
Using the VARRAY LIMIT Size
Using VARRAY Columns in Temporary Tables
Changing a Nested Table’s Storage Tablespace
ANSI Support for Nested Tables
Introducing the Multiset Operators

Using General SQL Enhancements
 Introducing the VERSIONS Clause
Using the Row Versions Feature
Using Flashback Query
Introducing the FLASHBACK TABLE and FLASHBACK DATABASE Statements
Overview of Table Functions
Understanding the Enhanced ODCI Functions
Using Alternative Quotes
Using the DROP TABLE … PURGE statement

Using Regular Expressions and Linguistic Sort and Comparison
 Using Regular Expressions Functions, Conditions, and Meta Characters in SQL and PL/SQL
Performing a Basic Search Using the REGEXP_LIKE Condition
Finding Patterns Using the REGEXP_INSTR Function
Extracting Sub-strings Using the REGEXP_SUBSTR Function
Replacing Patterns Using the REGEXP_REPLACE Function
Performing a Case-Insensitive or Accent-Insensitive Linguistic Sort Using NLS_SORT
Performing a Linguistic Comparison Using the NLS_COMP Initialization Parameter
The NLSSORT Function: Case Insensitive and Accent Insensitive Support

Using the New PL/SQL Compiler
 Identifying the New Initialization Parameters for PL/SQL Compilation
Using the New PL/SQL Compile Time Warnings for Subprograms
Setting the Warning Messages Levels Using the PLSQL_WARNINGS initialization parameter
Setting the Warning Messages Levels Using the DBMS_WARNING Package Subprograms
Viewing the Current Setting of PLSQL_WARNINGS Using SQL*Plus
Viewing the Current Setting of PLSQL_WARNINGS Using the Data Dictionary Views
Guidelines for Using PLSQL_WARNINGS
Viewing the Current Setting of PLSQL_WARNINGS Using the Data Dictionary Views

Programming with PL/SQL Enhancements
 Using Conditional Compilation
Using the Selection, Inquiry, and Error Directives
Displaying the PLSQL_CCFLAGS Setting
Identifying the Database Version and Release Using the DBMS_DB_VERSION Package Boolean Constants
Using the DBMS_PREPROCESSOR Procedures to Print or Retrieve the PL/SQL Post-Processed Source Text
Obfuscating (hiding) PL/SQL Source Code
Bulk Binding: FORALL Support for Sparse Collections and Index Array
Maintaining Valid PL/SQL Program Units and Views

Using the SQL Model Clause
 Learning the Concepts and Reviewing the Sample Data
Using Cell and Range References
Using the CV() Function
Using the FOR Construct
Using Analytic Functions in the SQL Model Clause
Using the UPDATE, UPSERT, and UPSERT ALL Options
Nested Cell References and Reference Models
Cyclic Rules in Models, Cycles, and Simultaneous Equations

Using SQL Enhancements for Data Warehousing
 MERGE Improvements and Extensions
Using Conditional Updates
Cleansing Data Using the DELETE Clause
Densifying Data with Partitioned Outer Joins
Repeating Data Values to Fill Gaps
Computing Data Values to Fill Gaps
Time Series Calculations on Densified Data
Period-to-Period Comparison of One Time Level

Suggested Next Courses
 Oracle Database 10g: Advanced PL/SQL
Oracle Database 10g: SQL Tuning Workshop