PL/SQL and SQL New features in Oracle 11 R2

===========================================
Index
===========================================
Top-N Rows
Identity column
Session specific sequence
Invisible Column
WITH and Local Function
TRUNCATE CASCADE
Temporary Undo
New Functions COLLECT and LISTAGG
EBR - Edition Based Redefinition

===========================================
Top-N Rows
===========================================
New syntax to select top-n rows
SELECT...
ORDER BY...
FETCH...

FETCH FIRST n ROWS
FETCH NEXT n ROWS

FETCH FIRST n PERCENT ROWS

OFFSET n ROWS

===========================================

VARCHAR2 size is Database

===========================================

Before Oracle 12c: in SQL and database: 4000. In PL/SQL: 32767

After Oracle 12c: 32767

If the size is more than 4K - the data is stored out of data blocks, as CLOB.

This NOT the default behaviour.

Enable by new parameter in init.ora

MAX_SQL_STRING_SIZE=EXTENDED

===========================================

Identity column

===========================================

A new "built in" column, to hold sequence values.

CREATE TABLE test_id_table

(id NUMBER GENERATED AS IDENTITY [START WITH N INCREMENT BY M],

 name VARCHAR2(30));

INSERT INTO test_id_table (name) VALUES ('A');

--no need to specify the identity column in values.

===========================================

Session specific sequence

===========================================

A new feature, session context sequence.

Useful when working with GLOBAL TEMPORARY TABLES.

CREATE SEQUENCE session_seq_a START WITH 1 INCREMENT BY 1 SESSION;

===========================================

Invisible Column

===========================================

Columns may be marked as INVISIBLE in ALTER/CREATE Table.

Invisible columns are not selected in SELECT *.

Invisible columns are not selected in DESCRIBE.

In INSERT, invisible columns may, or may not, be inserted.

When made visible, columns appear at the end of the table.

CREATE TABLE test_id_table

(id NUMBER ,

 test_name VARCHAR2(30),

 test_type VARCHAR2(1) INVISIBLE

);

===========================================

WITH and Local Function

===========================================

From Oracle 12c, Oracle can use PL/SQL function in WITH clause.

Example:

WITH my_with_func(some_number NNUMBER) RETURN NUMBER IS

BEGIN

  return some_number+100;

END;

SELECT col_a,

       col_b,

       my_with_func(10) AS plus_hundred

  FROM MY_TABLE;

When using WITH, the PL/SQL function is NOT stored in database!!!

It is referred to as "Local Function".

When using Local Function, there is no context switch between SQL and PL/SQL.

If there is a name conflict, the Local Function overrides the stored one.

===========================================

TRUNCATE CASCADE

===========================================

New to 12c

This clause would eliminate values in tables that are referentially connected.

For example, deleting department, would cause all employees of that department to be deleted, etc...

Very dangerous, not advisable to use!!

===========================================

Temporary Undo

===========================================

The problem with Global Temporary Table was that there was no Temporary Undo. 

DMLs on the Global Temporary Table still generated Redo Logs. 

The issue:

- A lot of redo data written to redo logs on disk.

- Retention period for real data was shortened.

The idea was to allow redo within the session.

From Oracle 12c, the Undo data for Global Temporary Tables is stored inside Temporary Tablespace.

To use this feature:

ALTER SYSTEM/SESSION SET TEMP_UNDO_ENABLED=true

===========================================

Result cache for PL/SQL function results

===========================================

Scenario - there is a complex PL/SQL function, that is processing relatively static data.

The idea is to store the PL/SQL function results in a cache.

The data is stored, until there is a DML operation on the underlying data.

Usage:

CREATE OR REPLACE FUNCTION my_cache_func(p_prod_id NUMER) 

       RETURN VARCHAR2 

       RESULT_CACHE RELIES_ON (REF_PRODUCTS) 

IS

  v_result VARCHAR2(30);

BEGIN

  SELECT product_info INTO v_result 

    FROM REF_PRODUCTS 

   WHERE REF_PRODUCTS.prod_id = p_prod_id;

  RETURN v_result;

END;

This is similar to using a hint /* RESULT CACHE / in SQL.

From Oracle 11R2 - Oracle detects function dependencies automatically. Therefore the RELIES_ON clause is no longer needed

Managing Result Cache
RESULT_CACHE_MAX_SIZE - Max memory in bytes allocated from SGA to Result Cache.
RESULT_CACHE_MAX_RESULT - Max percent out of RESULT_CACHE_MAX_SIZE that each query may use.

DMBS_RESULT_CACHE Package
- Interface to manage Result Cache

DMBS_RESULT_CACHE.flush 
DMBS_RESULT_CACHE.bypass(true) - disable usage of Result Cache
DMBS_RESULT_CACHE.bypass(false) - enable usage of Result Cache
===========================================
New Functions COLLECT and LISTAGG
===========================================
COLLECT and LISTAGG
These function do similar action, group data from several lines into one field in SELECT.

COLLECT
- A GROUP BY Aggregate function, grouping values from several lines into one field, comma separated. 
- The output is a Collection.
LISTAGG
- Similar to COLLECT, only the output is a String.
- LISTAGG is both Aggregate function, with GROUP BY, same as COLLECT, and an Analytical function with PARTITION clause.

Examples
COLLECT

CREATE OR REPLACE TYPE emp_name_tab_type AS TABLE OF VARCHAR2(30);

SELECT deptno, 
       AVG(sal) AS avg_sal,
       CAST(COLLECT(emp_name) AS emp_name_tab_type) AS emp_names
FROM EMP
GROUP BY deptno;

DEPTNO AVG_SAL EMP_NAMES
------ ------- ---------------------------------------------------
10        2916 EMP_NAME_TAB_TYPE(CLARK,MILLER,KING)
20        2175 EMP_NAME_TAB_TYPE(SMITH,FORD,ADAMS,SCOTT,JONES)
30        1566 EMP_NAME_TAB_TYPE(ALLEN,JAMES,TURNER,BLAKE,MARTIN)


SELECT deptno,
       AVG(sal) AS avg_sal,
       LISTAGG(emp_name,',') 
       WITHIN GROUP(ORDER BY sal) AS emp_names

DEPTNO AVG_SAL EMP_NAMES
------ ------- ---------------------------------------------------
10        2916 EMP_NAME_TAB_TYPE(MILLER,CLARK,KING)
20        2175 EMP_NAME_TAB_TYPE(SMITH,ADAMS,JONES,FORD,SCOTT,)
30        1566 EMP_NAME_TAB_TYPE(JAMES,MARTIN,TURNER,ALLEN,BLAKE)

===========================================

EBR - Edition Based Redefinition

===========================================

New from Oracle 11R2.

The idea is to eliminate downtime and locks during PL/SQL code changes.

DBA_EDITIONS - list all PL/SQL editions

The default edition is ORA$BASE.

Following editions are numbered as 2,3,4...

You can have same PL/SQL code in different editions.

ALTER SESSION/SYSTEM SET EDITION=9 changes the edition.

Currently sessions continue to run.

When there is a new session, it would load the PL/SQL code of the up to date edition.