Enhancements in Oracle 12c Database Part3

Extended data types

In 12c, the data type VARCHAR2, NAVARCHAR2, and RAW size will support up to 32,767 bytes in contrast to 4,000 and 2,000 in the earlier releases. The extended character size will reduce the use of going for LOB data types,  whenever possible. In order to enable the extended character size, you will have to set the MAX_STRING_SIZE initialization database parameter to EXTENDED.

Generated as Identity/Sequence Replacement

You can now create a column with 'generated as identity' clause. Thats it. Doing this is equivalent to creating a separate sequence and doing a sequence.nextval for each row. This is another handy and a neat feature which will help developer community. This is also called No Sequence Auto Increment Primary Key.
Identity Column creation:

SQL> create table t6 (col1 number generated always as identity);

SQL> create table t7 (col1 number generated always as identity (start with 1000 increment by 10));

SQL> insert into t6 values (1);

insert into t6 values (1)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

SQL> create table t9 (col1 number, col2 number generated by default as identity);

SQL> insert into t9 values (9,9);

SQL> insert into t9 values (10,default);

SQL> insert into t9 (col1) values (11);

SQL> select * from t9;

COL1 COL2
---------- ----------
9 9
10 2
11 3

WITH clause improvements

In 12c, you can have faster running PL/SQL function/procedure in SQL, that are defined and declared within the WITH clause of SQL statements. The following examples demonstrate how to define and declare a procedure or  function within the WITH clause:

WITH
PROCEDURE|FUNCTION test1 (…)
BEGIN
 <logic>
END;
SELECT <referece_your_function|procedure_here> FROM table_name;

Although you can’t use the WITH clause directly in the PL/SQL unit, it can be referred through a dynamic SQL within that PL/SQL unit.  The with clause inline PLSQL feature will make it possible to create a procedure or function inside your select statement instead of having to create this in a package or function. Oracle also says that this will optimize the performance against having to call a schema procedure/function

An Example:

WITH
FUNCTION FUNC_ADD_ONE(p_num IN NUMBER
IS
BEGIN
RETURN p_num+1;
SELECT FUNC_ADD_ONE(1) FROM Dual;

<<<Previous    Continue>>>