New Features in Oracle 10 g - Application development.

Relaxation of Line Length and Overall Limits for the DBMS_OUTPUT PL/SQL Package:

The declaration of the package type DBMS_OUTPUT.CHARARR is enhanced to support the increased maximum of 32,767 bytes. Previously, the VARCHAR2 constraint was 255 bytes. This determines the maximum line size that can be written by the DBMS_OUTPUT.PUT and DBMS_OUTPUT.PUT_LINE procedures.

PL/SQL programmers frequently use DBMS_OUTPUT and, in Oracle Database 10g Release 1 and earlier, were constrained by the 255 byte limit. In Release 2, the line length limit is increased to 32,767 bytes and the overall limit is removed altogether.

SQL Language Improvements

SQL Language improvements in Oracle Database 10g Release 2 include:

Rules Manager :

Rules Manager is a new feature of Oracle Database 10g Release 2. It enables developers to create applications that process and respond to events of any complexity using rules and policies defined in the database. It can evaluate events using data from the application and from database tables. It stores intermediate results to quickly evaluate the next event in a long running composite event (an event made up of two or more simple events).

Rules are defined using XML and SQL and can have complex conditions using conjunctions and disjunctions, and specify a set of events, time, and non-occurrence of events with or without a deadline.

The benefit of this feature is that rules that are managed in Oracle Database keep pace with changing business conditions and are always up-to-date; rules are easily changed with SQL and are not included in your application or loaded into a memory-based rules repository.

Rules can be evaluated efficiently with the complete business context stored in your Oracle Database and data provided by your application. Event response is flexible; rules can trigger actions in Oracle Database or your application.

check the below for further information.
Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for details
Collection Operator Performance Improvements :

Collection operators operate on collections of data objects. For Oracle Database 10g Release 2, the EQUALITY and MEMBER operators for collections have been improved.
These enhancements have improved the performance for collection operators.

Distributed Large Objects (LOB) Support :

Distributed LOBs support provides easy-to-use and efficient support for accessing unstructured data in a distributed environment. The data interface for LOBs can now INSERT, UPDATE, and SELECT LOBs across dblinks.

The benefit of this new support is the ability to access remote LOBs.

See also: Oracle Database Application Developer's Guide - Large Objects for details

New InsertXML(), AppendChildXML(), InsertXMLBefore(), and DeleteXML() Functions:

The current UpdateXML() function can only manipulate the content of existing nodes within an XML document. It cannot be used to add or remove nodes from a document. The only way to add or remove nodes from a document is to use the methods provided by the DOM API.
This feature resolves this issue by introducing a family of SQL functions similar to UpdateXML() that make it possible to add and remove nodes from a document.

It also enables XPath Rewrite in the cases where the target of the function is a schema-based XMLType with structured storage. This means that operations can be executed in a more efficient manner than would be possible with the DOM API.

This feature improves programmer productivity by providing functions that make it possible to perform complex operations on the content of XML documents directly from SQL. Typically, these functions reduce the number of statements required to perform a particular operation when compared to typical DOM-based operations.

This new set of functions also improves application performance by allowing more complex manipulation of XML content to be performed by XPath Rewrite. XPath Rewrite makes it possible to perform operations on XML documents faster than can be achieved with conventional DOM or SAX-based programming techniques. It is now possible to do in one statement what previously took 10 to manipulate XML eliminating the need to use the DOM API to manipulate XML documents. These new functions improve application performance by allowing complex operations on XML documents to be performed using XPath Rewrite.

See also:
Oracle XML DB Developer's Guide for details

Online Redefinition Supports Clustered Tables, ADT's, MV Logs, AQ Tables, and Preserves Database Statistics :

Online redefinition can now be performed on clustered tables, tables containing Abstract Data Types (ADT), Advanced Queuing (AQ) tables, Materialized View (MV) logs, and it retains all statistics on a table. It also clones statistics, checks and non-NULL constraints, and dependent objects on nested tables. PL/SQL packages do not have to be recompiled if the number and ordering of columns and data types in the table remain unchanged.

This feature means less downtime as more tables are now supported for online redefinition. Analysis of the table after reorganization is no longer required and PL/SQL packages remain available following an online redefinition, thus maintaining application availability. These enhancements are especially beneficial to customers using Oracle Applications.

Changes in DDL statements:

CREATE TABLE has the new limit on number of partitions and subpartitions as 1024K - 1.

CREATE TABLE and ALTER TABLE contains new syntax that lets you encrypt column data.
All of the DML statements (INSERT, UPDATE, DELETE, MERGE) now have an error logging clause.

FLASHBACK TABLE is a new statement that lets you revert one or more tables to an earlier system change number (SCN) or timestamp or retrieve a table that was dropped.

MERGE has new syntax that lets you:
Specify either the update operation or the insert operation, or both
Delete rows from the target table during the update operation

PURGE is a new SQL statement that lets you permanently remove previously dropped objects from the recycle bin and release the space that was associated with them.

SELECT has new syntax that lets you:
Issue a versions query, which returns all incarnations of the rows returned by the query within a specified SCN or time range.