Hands-On Oracle9i New Features

Introduction 01 ("Must to know" Oracle9i: SQL New Features)

• The Oracle9i ANSI standard JOIN syntax vs the original join syntax.

• The ANSI standard NATURAL JOIN syntax

• The USING clause

• The ON clause

• The ANSI standard CROSS JOIN syntax

• The OUTER JOIN clause

• RIGHT OUTER JOIN vs original Oracle syntax

• LEFT OUTER JOIN vs original Oracle syntax, and

• FULL OUTER JOIN.

Introduction 02 ("Must to know" Oracle9i: SQL New Features)

• The (WITH SUMMARY AS) statement

• The use of multiple columns in the WHERE clause

• The (inline view)

• The MERGE statement

Hands-On 01 (Materialized Views – The view that stores data)

• Creating a materialized view log

• Listing the snapshot logs' view

• MLOG$_EMP

• Creating a materialized view

• Granting materialized view privilege

• Listing the user's materialized views

• Executing the REFRESH procedure

• Using DBMS_SNAPSHOT package

• Executing the REMOVE procedure

• Listing the materialized view in the job’s queue

• Deleting the materialized view’s job

• Dropping the created Materialized view

• Revoking the materialized view privilege

Hands-On 02 (The BREAK ON clause)

• REPHEADER

• REPFOOTER

• COLUMN <col> HEADING <hdr> FORMAT a20

• BREAK ON <col> SKIP n

• COMPUTE SUM OF <col> ON <col>

• SPOOL

• HOST

• CLEAR BUFFER

• CLEAR COLUMNS

• CLEAR COMPUTE

Hands-On 03 (Troubleshooting – UNION, INTERSET, and MINUS statements)

• UNION

• INTERSECT

• MINUS

Hands-On 04 (Troubleshooting Deleting duplicated records)

• Creating a table with lots of duplicated records

• Writing a simple delete (DML) statement to remove duplicated records.

• Verifying the removal of the duplicated records

• Dropping the duplicated table

Hands-On 05 (Data Partitioning)

• Creating the OS level folders

• Creating a tablespace

• Granting the CREATE TABLESPACE

• Granting the DROP TABLESPACE

• Creating a range-based partitioning table

• Inserting records into the partition table

• Analyzing the partition table

• Listing the statistics on the partition data table

• Compare a regular table with a partition table

• Listing the partition table

• Creating a local partition index.

• Analyzing the partition table index.

• Listing the statistics on the index partition table

• Dropping the partition table and all of the tablespaces

• Revoking the CREATE TABLESPACE privilege

• Removing the folders

Hands-On 06 (Data Warehousing – ROLLUP and CUBE function)

• ROLLUP

• CUBE

Hands-On 07 (Using Native Dynamic SQL)

• Native Dynamic SQL

Hands-On 08  (JAVA Stream)

• Checking the JAVA tool installation

• Checking the JAVA pool memory usage

• Creating a java class

• Publishing the JAVA class

• Running and testing the JAVA program

• Dropping the JAVA SOURCE

• Dropping the FUNCTION

Hands-On 09 (Defining Objects and Collection Types)

• Creating an object type

• Creating a table type using object type

• Listing the object types

• Creating a nested table using a table type

• INSERT INTO tablename VALUES (col, table_object_type(record_object_type()))

• Listing the nested table

• INSERT INTO THE (SELECT col… FROM tablename)

• SELECT nested_table_id, col1, col2 FROM THE (SELECT col1 FROM tablename)

• UPDATE THE(SELECT col1 FROM tablename)

• Adding a unique index to the nested table.

• Dropping the nested table and types

Hands-On 10 (Inserting employees’ picture into the EMP table using BLOB / CLOB)

• ALTER TABLE

• Assigning an empty blob to a column.

• Creating a directory object

• Granting the CREATE ANY DIRECTORY privilege

• Granting the DROP ANY DIRECTORY privilege

• Writing a stored procedure

• Finding where the picture's pointer is located using BFILENAME function

• Open the picture as read-only option using DBMS_LOB.FILEOPEN procedure

• Loading the picture into a column using DBMS_LOB.LOADFROMFILE procedure

• Closing the picture’s pointer using DBMS_LOB.FILECLOSE procedure

• Executing and test a stored procedure

• Dropping the directory object

• Revoking the CREATE ANY DIRECTORY privilege

• Revoking the DROP ANY DIRECTORY privilege

Hands-On 11 (Viewing a picture using BLOB datatype)

• Creating the "Data Blocks" item

• Using the Data Block wizard

• Using the Layout wizard

• Using and modifying the "Content" canvas

• Running the application

• Using the "Execute Query" icon

• Navigating through the application

• Saving the application

Hands-On 12 (What is a REF CURSOR)

• Creating a table

• Creating a package specification at the database level

• Creating the package body

• Creating the Form Block

• Creating following procedures:

o       query,

o       insert,

o       lock,

o       update,

o       delete, and

o       count procedures.

Hands-On 13 (FORM BLOCK and TRANSACTIONAL TRIGGERS)

• Creating the "Data Blocks" item

• Using the Data Block wizard

• Using the Data Block property palette

• Creating transactional triggers

1. ON-INSERT

2. ON-DELETE

3. ON-UPDATE

4. ON-COUNT

5. ON-LOCK

• Using the Layout wizard

• Using and modifying the "Content" canvas

• Running the application

• Using the "Execute Query" icon

• Navigating through the application

• Saving the application