New Features for Developers in Oracle 11g

Oracle 11g has introduced many new features in SQL & PL/SQL, which are very useful for a developer. I havecompiled some of the most important features, which a Oracle developer should know.

Reference: http://www.oracle.com/technetwork/articles/grid/index-099021.html

1. Compound Triggers: A compound trigger is actually four different triggers defined as one. it not only it saves lot of code writing, but also resolves the issue where same set of session variables share common data

2. Trigger Follows/Precedes Clause: The Oracle 11g trigger syntax now includes the Follows/Precedes clause to guarantee execution order for triggers defined with the same timing point. Follows clause in a trigger specifies that the current trigger would follow the execution of specified triggers.

3. Creating Trigger in DISABLED mode: Oracle 11g provides flexibility to create a Trigger in DISABLED mode also. They remain deactivated until they are enabled. Prior to Oracle 11g, a trigger can be created in ENABLED mode only.

4. Read Only Tables: In Oracle 11g, a table can be set READ ONLY mode to restrict write operations on the table. A table can be altered to toggle over READ ONLY and READ WRITE modes.

5. Invisible Indexes: An invisible index is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE or using INDEX hint. Making an index invisible is an alternative to making it unusable. It can be converted to VISIBLE mode for auto consideration by the optimizer

6. Virtual Columns: Oracle 11g allows you to create a "virtual column", an empty column that contains a function upon other table columns (the function itself is stored in the data dictionary). They show same behavior as other columns in the table in terms of indexing and statistics.
- Oracle 11g - Virtual Columns

7. Continue Statement: The continue statement passes control to the next iteration. The CONTINUE statement exits the current iteration of a loop, either conditionally or unconditionally, and transfer control to the next iteration.

8. LISTAGG Analytic Functions: Oracle 11g has introduced. LISTAGG analytic function to aggregate the result set in multiple rows into one single column.
- Oracle 11g New String Aggregation Techniques

9. REGEXP_COUNT: Oracle 11g introduced REGEXP_COUNT in regular expressions, used to count the occurrence of a character or string expression in another string.

10. Pivot and Unpivot: The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. The UNPIVOT operator converts column-based data into separate rows.
- Oracle 11g Pivot data Rows to Columns

11. Result Cache: Oracle 11g has introduced a new component in SGA - Shared Pool as Result Cache to retain result-sets of SQL queries and PL/SQL functions results. By caching the results of queries, Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the resultset.

12. Pragma Inline: A new pragma PRAGMA INLINE has been introduced to specify whether a subprogram call has to be inlines or not. Inlining replaces a subprogram call with a copy of the called subprogram

13. Direct Sequence Assignment: Prior to Oracle 11g, sequence assignment to a number variable could be done through a SELECT statement only. From oracle 11g we can directly assign a sequence value to a pl/sql variable providing greater simplicity.

14. SIMPLE_INTEGER Data Type: Oracle 11g has designed a new data type SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE. They are compatible with the native compilation feature of Oracle 11g, which makes supports their faster implementation.

15. Recursive Subquery Factoring: Version 11g release 2 introduced recursive subquery factoring or the recursive with clause. This is an extension to the SQL syntax with which you can do recursive/hierarchical queries.
- Oracle 11g - Recursive subquery factoring

16. Calling Functions: In Oracle 11g, functions can now be called using Named, Positional and Mixed notation while calling from SQL SELECT statement.

17. DDL Wait Option: Oracle Database 11g, using ddl_lock_timeout parameter, when a DDL statement in the session does not get the exclusive lock, it will not error out. Instead, it will wait for sepcified seconds in ddl_lock_timeout parameter. In those seconds, Oracle continually re-tries the DDL operation until it's successful or the time expires.

18. Deprecation of SQL Plus: Oracle has deprecated the use of SQL Plus (isqlplus and sqlplusw) since Oracle 11g. Oracle 11g supports SQL plus through command line and recommends the use of SQL Developer.

19. SIMPLE_INTEGER: Oracle 11g has designed a new data type SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE keeping in view the hardware requirements and expectations with an Integer value. They are compatible with the native compilation feature of Oracle 11g, which makes supports their faster implementation.

20. New PL/SQL Compiler Warning: Oracle Database 11g has a new warning called PLW-06009 to warn you "dangerous practice of leaving the WHEN OTHERS THEN NULL" during compile time. enable this warning you have to set this session parameter plsql_warnings = 'enable:all'

21. Behaviour: Oracle 11g no longer keeps PL/SQL as a pure procedural language. Now PL/SQL is efficient to implement Object Oriented Concepts, thus making it comparable to other programming languages like C++ and JAVA.

22. New Partitions: By using Oracle 11g new feature called Reference Partitioning we can create partitions identical to parent table in child table. Oracle 11g has also provided us a way to define partitions in an intelligent manner by System Partitioning.
- Oracle 11g - Reference Partitions
- Oracle 11g - System Partitions


Related Posts
- Oracle Database 12c New Features for Developers
- Top 15 new features of Oracle Database 12.2 for developers
- SQL Interview Question Answers
- How to configure Case Sensitive Password in Oracle