Oracle 9i Database New Features for Developers

  • New SQL enhancements to bring Oracle in line with the ANSI/ISO SQL

  • Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. This functionality allows comparative reporting over time and recovery from logical corruptions.

  • Declarative primary key, unique key and foreign key constraints can now be defined against views

  • Multitable Inserts concept can be used single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables.

  • Allows the renaming of table columns and constraints

  • Insert into or update a SQL table by specifying a PL/SQL record variable, rather than specifying each record attribute separately. Also select entire rows into a PL/SQL table of records, rather than using a separate PL/SQL table for each SQL column.

  • Create collections that are indexed by VARCHAR2 values

  • UTL_FILE contains several new functions that useful to perform general file-management operations from PL/SQL

  • PL/SQL supports the complete range of syntax for SQL statements, such as INSERT, UPDATE, DELETE, and so on. If received errors for valid SQL syntax in PL/SQL programs before, those statements should now work.

  • The new data type TIMESTAMP records time values including fractional seconds. New data types TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE allow adjusting date and timing values to account for time zone differences.

  • Perform bulk SQL operations, such as bulk fetches, using native dynamic SQL (the EXECUTE IMMEDIATE statement)

  • MERGE Statement: This specialized statement combines insert and update into a single operation. It is intended for data warehousing applications that perform particular patterns of inserts and updates.

  • SELECT can have multiple groupings in the GROUP BY clause, for selective analysis across multiple dimensions.

  • Enhancements in Oracle Text formerly interMedia Text uses standard SQL to index, search, and analyze text and documents stored in the Oracle database.

  • Includes Oracle XML DB, which is a set of built-in high-performance storage and retrieval technologies geared toward XML

  • Supports JDK 1.3

  • Full support for ANSI-style CASE statements and expressions

  • Custom aggregate functions can be defined for working with complex data

  • Oracle Ultra Search is able both to search the contents of a database to find documents, newspaper articles, and other information stored inside a database and to search the contents of static HTML pages.

  • Oracle Spatial Enhancements: Location capabilities in Oracle9i and Oracle Spatial have been greatly enhanced. Content stored in Oracle9i can now be associated with related location criteria and services. New support for online mapping, yellow pages, driving directions, traffic, and geocoding services allow online content to be merged with database content.

Refer Oracle 9i Database Documents to learn more and to find examles.