Oracle 11g – PL SQL – New features

Feature List:(Oracle Database 11g)

Adaptive Cursor Sharing ADDM Enhancements (for RAC) Automated Maintenance Tasks Automatic Memory Management Automatic SQL Tuning Automatic Storage Manager (ASM) Enhancements AWR Baseline Enhancements Case Sensitive Passwords Data Recovery Advisor Database Resident Connection Pool Database Replay DDL With the WAIT Option (DDL_LOCK_TIMEOUT) Diagnostics Repository Direct NFS

Fine-Grained Access to Network Services Finer Grained Dependency Management Flashback Data Archive Flashback Transaction Function Result Cache Health Monitor Install Oracle Database 11g Invisible Indexes Logminer Online Table Redefinition Partitioning Query Result Cache Read-Only Tables Resource Manager Enhancements

RMAN Scheduler Enhancements SecureFiles SQL Access Advisor SQL Performance Analyzer SQL Plan Management Statistics Collection Enhancements Table Compression Enhancements Temporary Tablespace Enhancements Tablespace Encryption Upgrading to Oracle Database 11g

Feature List:(Oracle Database 11g Release 1)

Automatic Subprogram Inlining Compiler Warning CONTINUE DBMS_XA Data Pump Enhancements Dynamic SQL Generalized Invocation

Hierarchical Profiler Native Compilation Native XML DB Web Services Notation PIVOT PL/Scope Real-Time SQL Monitoring

Regular Expressions Sequences SIMPLE_INTEGER Snapshot Standby Triggers UNPIVOT Virtual Columns

I found a nice article, contain reference DocID from Metalink to know latest and new features of Oracle Database 11g PL/SQL

All the new features available with 11.1 release of oracle are listed together in
Note 869084.1 : New PL/SQL Features for 11g Release 1 (11.1).
The contents of the article is pasted below.

New PL/SQL Features for 11g Release 1 (11.1)

• PL/SQL Native Compilation 
  In Oracle 11g, PL/SQL native compilation requires no C compiler, no DBA intervention and is fully supported in a RAC environment. By setting thePLSQL_CODE_TYPE to a value of NATIVE , rather than the default value of INTERPRETED , code is compiled directly to machine code . When the code is called, it is loaded into shared memory, making it accessible for all sessions in that instance. The %_PLSQL_OBJECT_SETTINGS views include the current PLSQL_CODE_TYPE setting for each PL/SQL object.
  Note 462449.1 : PL/SQL Native Compilation on Oracle 11G 

• PL/SQL Function Result Cache 
  The cross-session PL/SQL function result cache provides a simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA. These results can be reused by any session calling the same function with the same parameters. This can result in a significant performance boost when functions are called for each row in an SQL query, or within a loop in PL/SQL. This article describes the usage and administration of the function result cache.
  Note 430887.1  : 11g New Feature PL/SQL Function Result Cache 

• PL/SQL Fine-Grained Dependencies 
  Prior to Oracle11g, modifying the structure of a table would make dependent views invalid even thought the change to table has nothing to do with view.That is why starting from Oracle11g a change to table that does not effect view logically, will NOT make view invalid. So, if you add a column to table T1 then because of fine grained dependency tracking system applied by Oracle 11g, it doesn’t make view invalid.
  Note 430725.1 : 11g New Feature Fine-Grained Dependencies 

• PL/SQL Continue Statement 
  In Oracle Database 11g PL/SQL has a new construct called CONTINUE , which is used in a loop. The statement moves the logic to the end of the loop and then to the beginning of the loop.The CONTINUE statement jumps out of the current loop iteration and starts the next one. It can be used on its own, or as part of a CONTINUE WHEN statement. This type of processing has always been possible using IF statements either on their own or with exceptions or GOTO statements, but the CONTINUE statement is neater and brings PL/SQL in line with other languages.
  Note 469584.1 : 11g New Feature PLSQL CONTINUE Statement 

• PL/SQL Automatic Subprogram Inlining 
  Every call to a procedure or function causes a slight, but measurable, performance overhead, which is especially noticeable when the subprogram is called within a loop. Avoiding procedures and functions is not an option, as it goes against the concept of modular programming, making programs bulky and difficult to manage. Automatic subprogram inlining can reduce the overheads associated with calling subprograms, whilst leaving your original source code in its normal modular state. This is done by replacing the subprogram calls with a copy of the code in the subprogram at compile time.
  The process of subprogram inlining is controlled by the PLSQL_OPTIMIZE_LEVEL parameter and the INLINE pragma. When PLSQL_OPTIMIZE_LEVEL=2 (the default), the INLINE pragma determines whether the following statement or declaration should be inlined or not. When PLSQL_OPTIMIZE_LEVEL=3, the optimizer may inline code automatically. In this case the INLINE pragma can turn it off inlining for a statement, or increase the likelihood that the optimizer will choose to inline a statement.
  http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/tuning.htm#i53930 

• PL/SQL SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Data Types 
  The SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE data types are predefined subtypes of PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively. Each subtype has the same range as its base type and has a NOT NULL constraint.
  SIMPLE_INTEGER differs significantly from PLS_INTEGER in its overflow semantics, but SIMPLE_FLOAT and SIMPLE_DOUBLE are identical to their base types, except for their NOT NULL constraint.
  You can use SIMPLE_INTEGER when the value will never be NULL and overflow checking is unnecessary. You can use SIMPLE_FLOAT and SIMPLE_DOUBLE when the value will never be NULL. Without the overhead of checking for nullness and overflow, these subtypes provide significantly better performance than their base types when PLSQL_CODE_TYPE=’NATIVE’, because arithmetic operations on SIMPLE_INTEGER values are done directly in the hardware. When PLSQL_CODE_TYPE=’INTERPRETED’, the performance improvement is smaller.
  Note 433091.1 : 11g New Features SIMPLE_INTEGER Datatype
  
  
• Named and Mixed Notation in PL/SQL Subprogram Invocations 
  Before Release 11.1, a SQL statement that invoked a PL/SQL subprogram had to specify the actual parameters in positional notation. As of Release 11.1, named and mixed notation are also allowed. This improves usability when a SQL statement invokes a PL/SQL subprogram that has many defaulted parameters, and few of the actual parameters must differ from their default values.
  Note 433070.1 : 11g New Feature Using Named and Mixed notation in PL/SQL subprograms 

• Enhancements to Regular Expression Built-in SQL Functions 
  The regular expression built-in functions REGEXP_INSTR and REGEXP_SUBSTR have increased functionality. A new regular expression built-in function, REGEXP_COUNT, returns the number of times a pattern appears in a string. These functions act the same in SQL and PL/SQL. 

• Sequences in PL/SQL Expressions 
  The pseudocolumns CURRVAL and NEXTVAL make writing PL/SQL source code easier for you and improve run-time performance and scalability. You can use sequence_name.CURRVAL and sequence_name.NEXTVAL wherever you can use a NUMBER expression.
  Note 433068.1 : Sequences in PL/SQL Expressions
  
  
• Dynamic SQL Enhancements 
  Both native dynamic SQL and the DBMS_SQL package have been enhanced.Native dynamic SQL now supports a dynamic SQL statement larger than 32 KB by allowing it to be a CLOB.
  In the DBMS_SQL package:
  • All data types that native dynamic SQL supports are supported.
  • The DBMS_SQL.PARSE function accepts a CLOB argument, allowing dynamic SQL statements larger than 32 KB.
  • The new DBMS_SQL.TO_REFCURSOR Function enables you to switch from the DBMS_SQL package to native dynamic SQL.
  • The new DBMS_SQL.TO_CURSOR_NUMBER Function enables you to switch from native dynamic SQL to the DBMS_SQL package.
  Note 435251.1   : How Native Dynamic Sql Supports Statements > 32K Characters
  Note 567856.1 : New Feature in 11g , DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER. 

• Compound Trigger 
  A compound trigger allows different blocks within a trigger to be executed at different timing points. It has a declaration section and a section for each of its timing points. All of these sections can access a common PL/SQL state.
  Note 430847.1 : 11g NEW PL/SQL COMPOUND Trigger
  
  

  
  

  • LISTAGG, NTH_VALUE, Ignore nulls
  • Recursive Subquery Factoring
  • The new hierarchical query
  • Parallel processing of statements

  Click on this link to expore the details about the above 4 bullet points.

  http://www.slideshare.net/lucasjellema/introduction-oracle-database-11g-release-2-for-developers?ref=http://knoworacle.wordpress.com/2010/04/20/introduction-oracle-database-11g-release-2-for-developers/