Oracle 11g Database New Features for Developers

• Table can be altered to Read Only or Read Write.

• A new XMLIndex_clause lets you create an XMLIndex index for XML data.

• In CREATE TABLE, A new virtual_column_definition create a virtual column.

• SELECT has new PIVOT syntax that rotates rows into columns. A new UNPIVOT operation to query data to rotate columns into rows.

• TRUNCATE statement was presented as a single statement with separate syntactic branches for TABLE and CLUSTER. That command has now been divided into TRUNCATE CLUSTER and TRUNCATE TABLE for consistency with other top-level SQL statements.

• Enhancements to Regular Expression (REGEXP_INSTR, REGEXP_COUNT and REGEXP_SUBSTR) Built-in SQL Functions.

• The CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration (in contrast with the EXIT statement, which exits a loop and transfers control to the end of the loop).

• Allow Sequences in PL/SQL Expressions. PL/SQL now recognizes the SQL pseudo columns CURRVAL, LEVEL, NEXTVAL, ROWID, and ROWNUM.

• Both native dynamic SQL and the DBMS_SQL package have been enhanced and support dynamic SQL statement larger than 32KB by allowing it to be a CLOB.

• Named and Mixed Notation in PL/SQL Subprogram Invocations are allowed.

• PL/SQL provides a function result cache. To use it, use the RESULT_CACHE clause in each PL/SQL function whose results you want cached. Because the function result cache is stored in a shared global area (SGA), it is available to any session that runs your application.

• PL/Scope is a compiler-driven tool that collects and organizes data about user-defined identifiers from PL/SQL source code.

• Subprogram Inlining replaces a subprogram call (to a subprogram in the same PL/SQL unit) with a copy of the called subprogram.

• Oracle Application Express provides an application development tool that is built into the database and is installed in the database by default.

• Create Rules and Actions for DML Events With Rules Manager. In 10g only INSERT is supported.

• Rules Manager now includes a PL/SQL API to store reusable and shareable rule conditions in SQL WHERE clause format.

• Unicode 5.0 Support

• New Compound Trigger Type: Compound trigger has a section for each of the BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, and AFTER STATEMENT timing points. All of these sections can access a common PL/SQL state.

• Client-Side Query Cache feature enables caching of query result sets in client memory. The cached result set data is transparently kept consistent with any changes done on the server side.

• Enable the XDB HTTP Server for SOA feature allows Oracle Database to be treated as simply another service provider in a service-oriented architecture (SOA) environment.

• Automatic SQL Tuning with Self-Learning Capabilities: Automatically detects high-load SQL statements and then tunes them automatically as needed in the maintenance window by creating appropriate SQL Profiles. It also issues proactive advice to create new access structures such as indexes that will significantly improve the performance of the high-load SQL statements.

• Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases.

• Database Resident Connection Pool (DRCP) reduces the resource requirements of applications that currently don't support connection pooling, either because it is not supported by the application infrastructure, or it has not been implemented.

• Online table redefinitions no longer invalidate dependent objects (PL/SQL, views, synonyms etc. expect triggers), provided the redefinition does not logically affect them.

• Oracle 11g has a new view called DBA_TEMP_FREE_SPACE that displays information about temporary tablespace usage.

Refer Oracle 11g Database Documents to learn more and to find examles