What's new in Oracle 11G SQL and PL-SQL?

--> In 11G, FOLLOWS clause is introduced which is useful in defining thefiring order of triggers when more than one triggers defined for same point of timings...

For Example :

CREATE OR REPLACE TRIGGER trg_ai_trans_g
AFTER INSERT ON trans_tab FOR EACH ROW
FOLLOWS trg_ai_trans_l
BEGIN
/* Some logic */
END;
/

-->Intra-unit inlining of PL/SQL code is another new feature of Oracle 11G. which allows for dynamically including subroutines whenever they are invoked. This means a subroutine call will be substituted by the actual code of the subroutine when the object code is generated.This is also true for locally declared subprograms. Oracle determines whether a particular subprogram can be inlined or not based on performance criteria. There is no coding change involved, but the PLSQL_OPTMIZE_LEVEL has to be set to 3 for automatic inlining. Inlining also works with PL/SQL_OPTIMIZE_LEVEL set to 2, But in this case a pragma has to be invoked programmatically just before the subprogram call to initiate the inlining process. Here’s an example:

CREATE OR REPLACE PROCEDURE p_child
IS
BEGIN
/* Some logic here */
END p_child;
/
CREATE OR REPLACE PROCEDURE p_parent
IS
BEGIN
/* Some logic here */
/* Conditionally call p_child here */
IF () THEN
PRAGMA INLINE(p_child, 'yes');
p_child;
END IF;
END p_parent;
/
when both automatic inlining and the pragma are specified, the pragma takes precedence. Thus, when the second argument of the pragma has a value of  'no', the inlining is not done.

Using inlining the code become much faster. ****Very IMP

-->ENHANCED NATIVE COMPILATION
Pre-Oracle 11g, PL/SQL stored subprograms and anonymous PL/SQL blocks could be natively compiled to a DLL format ( residing in shared libraries) and executed faster but this required a C compiler. Now the Native compilation is possible without C compiler. Compalition mode can be changed by setting PLSQL_CODE_TYPE parameter to INTERPRETED or NATIVE.

-->result_cache is a hint that is new with Oracle 11g. It caches the result set of the select statement.If now another session does the same select statement, Oracle can return the result from the cache rather than executing the query, which might be a performance improvement.

-->Are the shared dynamically linkable libraries(of native compilation) portable?
No, absolutely not! The shared libraries contain platform specific object code. The shared libraries generated by native compilation are stored in the database as BLOBs in the ncomp_dll$ dictionary table.

-->Introduced new Pl\Sql data type SIMPLE_INTEGER Subtype of PLS_INTEGER It has the same range as PLS_INTEGER BUT One basic difference between the two is that SIMPLE_INTEGER is always NOT NULL. Another major difference is  that it never gives numeric overflow error like its parent data type instead it wraps around without giving any error. SIMPLE_INTEGER data type gives major performance boost over PLS_INTEGER when code is compiled in ‘NATIVE’ mode, because arithmetic operations on
SIMPLE_INTEGER type are performed directly at the hardware level. When code is compiled in  INTERPRETED’ mode which is default, SIMPLE_INTEGER is still better than the PLS_INTEGER but is not that significant.

-->Now (IN 11G) to get nextval of a sequence we don't need select seq.nextval from dual.
Ability to perform PL/SQL assignments on sequence values, as in
v_new_val := seq_test.NEXTVAL;

--> 11 G Introduced keyword "continue" :
This is similar to C language to continue in loop. See the example below
begin
for kk in 1..3
loop
dbms_output.put_line('Loop count kk = ' || to_char(kk));
if(kk = 2)
then
continue;
end if;
dbms_output.put_line('only if kk is not equal to 2');
end loop;
end;

-->In oracle 11g calling of stored program from sql is possible with named notattions which was not possible up to 10g.

--> Oracle 11g introduced "pivot" clause in sql : it's similar to Excel.
For example below is the simple sql query and out put-
SQL> SELECT job
2 , deptno
3 , SUM(sal) AS sum_sal
4 FROM emp
5 GROUP BY
6 job
7 , deptno
8 ORDER BY
9 job
10 , deptno;

JOB DEPTNO SUM_SAL
--------- ---------- ----------
ANALYST 20 6600
CLERK 10 1430
CLERK 20 2090
CLERK 30 1045
MANAGER 10 2695
MANAGER 20 3272.5
MANAGER 30 3135
PRESIDENT 10 5500
SALESMAN 30 6160
9 rows selected.

But using povot in 11g it can be like

SQL> WITH pivot_data AS (
2 SELECT deptno, job, sal
3 FROM emp
4 )
5 SELECT *
6 FROM pivot_data
7 PIVOT (
8 SUM(sal) -- pivot_clause
9 FOR deptno -- pivot_for_clause
10 IN (10,20,30,40) -- pivot_in_clause
11 );

JOB 10 20 30 40
--------- ------ ------ ------- ------
CLERK 1430 2090 1045
SALESMAN 6160
PRESIDENT 5500
MANAGER 2695 3272.5 3135
ANALYST 6600

5 rows selected.

-->Oracle 11g audit vault - Oracle Audit Vault is a new feature that will provide a solution to help customers address the most difficult security  problems remaining today, protecting against insider threat and meeting regulatory compliance requirements.

--> PL/SQL hierarchical profiler (introduced in Oracle 11g)