Basic Insights into New Features of Oracle 11g for SQL and PL/SQL ( Basic 1 - 1)

1. PL/SQL "CONTINUE" Keyword: Oracle 11g introduces the "CONTINUE" keyword in PL/SQL, allowing you to implement a "C-Like" continue in a loop. This enables skipping an iteration to bypass any "else" Boolean conditions, eliminating the need for a cumbersome GOTO statement to exit a Boolean within a loop.

2. New "PIVOT" SQL Clause: Oracle 11g introduces the "PIVOT" SQL clause, which facilitates quick ROLLUP operations, similar to creating an MS-Excel pivot table. It allows you to display multiple rows in a single column using SQL. The PIVOT operator takes data in separate rows, aggregates it, and converts it into columns. Conversely, the UNPIVOT operator converts column-based data into separate rows.

3. Compound Triggers: Oracle 11g introduces the concept of compound triggers, which are single triggers on a table. These triggers allow you to specify actions for each of four timing points: before the firing statement, before each row affected by the firing statement, after each row affected by the firing statement, and after the firing statement. Compound triggers can consolidate both statement-level and row-level actions within a single trigger, offering the added advantage of sharing common state between all trigger points using variables. Common use cases for compound triggers include accumulating rows for bulk insertion and avoiding the ORA-04091 mutating-table error.

4. Change Trigger Execution Using "Follows" Clause: In Oracle 11g, the trigger syntax includes the "Follows/Precedes" clause to ensure the execution order for triggers defined with the same timing point. You can use "FOLLOWS" to specify that the trigger being created should fire after the specified triggers. This clause ensures that the specified triggers exist on the same table as the trigger being created and do not need to be enabled.

5. Creating Triggers in DISABLED Mode: Oracle 11g provides the flexibility to create triggers in DISABLED mode. These triggers remain inactive until explicitly enabled. In contrast, prior to Oracle 11g, triggers could only be created in ENABLED mode.

6. DDL Wait Option: Oracle Database 11g introduces the DDL_LOCK_TIMEOUT parameter, which allows a DDL statement in a session to wait for an exclusive lock rather than error out immediately. The DDL operation will continue to retry until it's successful or the specified timeout period expires.

7. Read-Only Tables: In Oracle 11g, you can set a table to READ ONLY mode, restricting write operations on the table. Tables can be altered to switch between READ ONLY and READ WRITE modes.

8. New Data Types and Invisible Indexes: Oracle 11g introduces new data types, including SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE, designed to align with hardware requirements for integer values. These data types are compatible with Oracle 11g's native compilation feature, supporting faster implementation. Additionally, Oracle 11g introduces invisible indexes that the optimizer ignores by default. To make an index visible to the optimizer, you can explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE or use the INDEX hint.

9. Using Virtual Columns: Oracle 11g allows you to create "virtual columns," which are empty columns containing a function based on other table columns. These columns behave like regular columns in terms of indexing and statistics.

10. LISTAGG Analytic Functions and REGEXP_COUNT: Oracle 11g introduces the LISTAGG analytic function to aggregate results from multiple rows into a single column. Additionally, it brings REGEXP_COUNT in regular expressions, allowing you to count occurrences of a character or string expression within another string.

11. SGA Result Cache: Oracle 11g introduces the SGA Result Cache, a new component in the Shared Pool that retains result sets of SQL queries and PL/SQL functions. Caching query results helps Oracle avoid redundant and time-consuming operations, ultimately improving query performance.

12. Pragma Inline and Direct Sequence Assignment: Oracle 11g introduces the PRAGMA INLINE pragma, which specifies whether a subprogram call should be inlined or not. Inlining replaces a subprogram call with a copy of the called subprogram, improving performance. Additionally, Oracle 11g allows direct assignment of sequence values to PL/SQL variables, simplifying the process.

13. Calling Functions and Recursive Subquery Factoring: In Oracle 11g, you can call functions using Named, Positional, and Mixed notation when calling them from SQL SELECT statements. Furthermore, Oracle 11g Release 2 introduces recursive subquery factoring, an extension to SQL syntax that allows you to perform recursive and hierarchical queries.