What's New in the SQL Reference?

This section describes new features of Oracle9i Release 1 (9.0.1) and provides pointers to additional information. Oracle8i new features information is also retained to help those users migrating from Oracle7 to Oracle9i Release 1 (9.0.1).

The following sections describe the new features in the Oracle9i SQL Reference:

Oracle9i Release 1 (9.0.1) New Features in SQL Reference

The following built-in datatypes are new or modified in this release:

Oracle provides SQL-based interfaces for defining new types when the built-in or ANSI-supported types are not sufficient. See "Oracle-Supplied Types".
"CHAR Datatype" can take the CHAR or BYTE parameter to indicate character or byte semantics, respectively.
"INTERVAL YEAR TO MONTH Datatype" and "INTERVAL DAY TO SECOND Datatype" are provided for additional datetime functionality
"TIMESTAMP Datatype" is provided for additional datetime functionality.
"VARCHAR2 Datatype" can take the CHAR or BYTE parameter to indicate character or byte semantics, respectively.

The following expression forms are new or enhanced in this release:

"CASE Expressions" (enhanced with searched case expression)
"CURSOR Expressions" (enhanced; they can be passed as REF CURSOR arguments to functions)
"Datetime Expressions" (new)
"INTERVAL Expressions" (new)
"Scalar Subquery Expressions" (new)

The following condition is new in this release:

IS OF type Conditions

The following built-in functions are new to this release:

The following functions are enhanced for this release:

The following privileges are new to this release:

EXEMPT ACCESS POLICY system privilege
RESUMABLE system privilege
SELECT ANY DICTIONARY system privilege
UNDER ANY TYPE system privilege
UNDER ANY VIEW system privilege
UNDER object privilege

The following top-level SQL statements are new to this release:

The following SQL statements have new syntax:

ALTER DATABASE has new syntax that lets you end a "hot backup" procedure while the database is mounted. It also has new syntax related to standby databases.
ALTER INDEX lets you gather statistics on index usage.
ALTER OUTLINE allows modification of both public and private outlines.
ALTER ROLE lets you identify a role using an application-specified package
ALTER SESSION lets you specify whether statements issued during the session can be suspended under some conditions.
ALTER SYSTEM has extended SET clause; lets you put the database in quiesed state.
ALTER TABLE allows partitioning by a list of specified values.
ALTER TYPE lets you modify the attribute or method definition of an object type.
ALTER VIEW lets you add constraints to views.
ANALYZE now has ONLINE and OFFLINE clauses as part of the VALIDATE STRUCTURE syntax. In addition, you can now choose whether to collect standard statistics, user-defined statistics, or both.
constraint_clause has been enhanced to facilitate index handling when dropping or disabling constraints.
CREATE CONTEXT has added syntax to let you initialize the context from the LDAP directory or from an OCI interface and to make the context accessible throughout an instance.
CREATE CONTROLFILE allows creation of Oracle-managed files.
CREATE DATABASE lets you create default temporary tablespaces when you create the database; lets you create undo tablespaces.
CREATE FUNCTION lets you create pipelined and parallel table functions and user-defined aggregate functions.
CREATE OUTLINE allows creation of both public and private outlines.
CREATE ROLE lets you identify a role using an application-specified package.
CREATE TABLE allows creation of external tables (tables whose data is outside the database); allows creation of Oracle-managed files; allows partitioning by a list of specified values.
CREATE TABLESPACE allows for segment space management by bitmaps as well as by free lists; allows creation of Oracle-managed files; lets you create undo tablespaces.
CREATE TEMPORARY TABLESPACE allows creation of Oracle-managed files.
CREATE TYPE lets you create subtypes.
CREATE VIEW lets you create subviews of object views; lets you define constraints on views.
DROP TABLESPACE has added syntax that lets you drop operating system files when you drop the contents from a dropped tablespace.
filespec allows creation of Oracle-managed files
INSERT has added syntax that lets you insert default column values.
SELECT lets you specify multiple groupings in the GROUP BY clause, for selective analysis across multiple dimensions; lets you assign names to subquery blocks; has new ANSI-compliant join syntax.
SET TRANSACTION lets you specify a name for a transaction.
UPDATE has added syntax that lets you update to default column values.

Oracle8i New Features in SQL Reference

The following SQL functions are new to this version:

The following top-level SQL statements are new to Release 8.1.5:

In addition, the following features have been enhanced:

The aggregate functions have expanded functionality. See "Aggregate Functions".
When specifying LOB storage parameters, you can now specify caching of LOBs for read-only purposes. See CREATE TABLE .
The section on Expressions now contains a new expression. See "CASE Expressions".
Subqueries can now be unnested. See "Unnesting of Nested Subqueries".