What's New in SQL Reference?

This section describes new features of Oracle9i release 2 and provides pointers to additional information. New features information from previous releases is also retained to help those users upgrading to the current release.

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

Oracle9i Release 2 (9.2) New Features in the SQL Reference

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

The following built-in expression is enhanced in this release:

The syntax for type constructor expressions now allows creation of new user-defined constructors (see "Type Constructor Expressions").

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

The following privileges are new or enhanced in this release:

DEBUG CONNECT SESSION system privilege (new)
DEBUG ANY PROCEDURE system privilege (new)
DEBUG object privilege (new)
EXECUTE object privilege on procedures, packages, libraries, and object types (enhanced)
GRANT ANY OBJECT PRIVILEGE system privilege

The following top-level SQL statements are new or enhanced in this release:

ALTER DATABASE has new syntax for managing standby databases, for managed standby recovery, and for logical standby.
ALTER OPERATOR lets you recompile an existing user-defined operator.
ALTER TABLE contains new clauses that let you rename a column or a constraint.
CREATE DATABASE:
- Contains two new clauses for assigning passwords to the SYS and SYSTEM users
- Lets you create a locally managed SYSTEM tablespace
CREATE SYNONYM now allows creation of synonyms for object types.
CREATE TABLE:
- Allows creation of a default list partition to capture rows that do not fall within any of the other list partitions
- Allows creation of range-list composite-partitioned tables
- Contains syntax for creating a table of type XMLType and for creating range-list composite-partitioned tables
- Allows data compression of data in table and partition segments
CREATE TYPE allows creation of object types with of NCHAR, NVARCHAR2, and NCLOB attributes.
CREATE VIEW now contains syntax for creating an XML view by transforming a table of type XMLType.
SELECT provides syntax for "flashback queries", which let you query data at a specified system change number or time in the past.

Oracle9i Release 1 (9.0.1) New Features in the SQL Reference

The following built-in datatypes were 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 were 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 was new in this release:

IS OF type Conditions

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

The following functions were enhanced for this release:

The following privileges were 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 were new to this release:

The following SQL statements had 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 and new RESET 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.
constraints 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.
file_specification 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 were new to this version:

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

In addition, the following features were 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".