Oracle Database 12c New Features

The following are some of the interesting new features of Oracle Database 12c.

Invisible Column

Oracle 12c allows a column to be invisible. Invisible columns are considered only when they are explicitly referred; otherwise they are ignored for queries and DML operations.

You can make a column visible or invisible whenever you want using ALTER TABLE command.


CREATE TABLE T2 ( C1  NUMBER(5),  C2 NUMBER(5) INVISIBLE)

INSERT INTO T2 (C1,C2) VALUES (10, 20);
INSERT INTO T2 VALUES(30);

SELECT * FROM T2;

SQL> select * from t2;

        C1
----------
        10
        30

SELECT C1,C2 FROM T2;


SQL> select c1,c2 from t2;

        C1         C2
---------- ----------
        10         20
        30


ALTER TABLE T2 MODIFY (C2  VISIBLE)

SELECT  * FROM T2

SQL> select c1,c2 from t2;

        C1         C2
---------- ----------
        10         20
        30

Default Value from CURRVAL AND NEXTVAL

In Oracle 12c, it is now possible to specify the CURRVAL and NEXTVAL sequence pseudocolumns as the default values for a column. In previous versions, it was either done explicitly in INSERT command or a trigger was used to do it automatically during insertion.

CREATE SEQUENCE t1_seq;

CREATE TABLE t1 (
  id          NUMBER DEFAULT t1_seq.NEXTVAL,
  description VARCHAR2(30)
);

INSERT INTO t1 (description) VALUES ('DESCRIPTION');
INSERT INTO t1 (id, description) VALUES (999, 'DESCRIPTION');

select * from  t1

SQL> select * from t1;

        ID DESCRIPTION
---------- ------------------------------
         1 DESCRIPTION
       999 DESCRIPTION

The following example shows how to use NEXTVAL for master table and CURRVAL for details or child table.


CREATE SEQUENCE order_master_seq;
CREATE SEQUENCE order_details_seq;

CREATE TABLE order_master (
  Order_id     NUMBER DEFAULT order_master_seq.NEXTVAL,
  Description  VARCHAR2(20)
);

CREATE TABLE order_details (
  id           NUMBER DEFAULT order_details_seq.NEXTVAL,
  order_id     NUMBER DEFAULT order_master_seq.CURRVAL,
  description  VARCHAR2(50)
);

insert into order_master (description) values ('First Order');
insert into order_details (description) values ('First Item');
insert into order_details (description) values ('Second Item');

select * from order_master;

SQL> select * from order_master;

  ORDER_ID DESCRIPTION
---------- --------------------
         1 First Order

select * from order_details;

SQL> select * from order_details;

        ID   ORDER_ID DESCRIPTION
---------- ---------- --------------------------------------------------
         1          1 First Item
         2          1 Second Item

DEFAULT ON NULL

If the column is referenced in INSERT, even when supplying the value NULL, the default value is not used. Oracle 12c allows you to modify this behaviour using the ON NULL clause in the default definition. When ON NULL is used, default value is applied when NULL value is provided explicitly.


create sequence t1_seq;

CREATE TABLE t1 (
  id          NUMBER DEFAULT ON NULL t1_seq.NEXTVAL,
  description VARCHAR2(30)
);

insert into t1(description) values('First Item');   // default value is used as value for ID is missing
insert into t1 values(null,'Second Item');          // default value is used even when NULL is explicitly provided
insert into t1 values(333,'Third Item');

SQL> select * from t1;

        ID DESCRIPTION
---------- ------------------------------
         1 First Item
         2 Second Item
       333 Third Item

Identity Columns

The 12c database introduces the ability to define an identity clause for a table column defined using a numeric type.


GENERATED
[ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ (identity_options ) ]

Using ALWAYS forces the use of the identity. If an insert statement references the identity column, even to specify a NULL value, an error is produced.


CREATE TABLE order_master (
  Id NUMBER GENERATED ALWAYS AS IDENTITY,
  Description Varchar2(20)
);

Insert into order_master (description) values('First Order');
Insert into order_master (description) values('Second Order');

SQL> select * from order_master;

        ID DESCRIPTION
---------- --------------------
         1 First Order
         2 Second Order


SQL> Insert into order_master (id,description) values(100,'Third Order');
Insert into order_master (id,description) values(100,'Third Order')
                          *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

Using BY DEFAULT allows you to use the identity if the column isn't referenced in the insert statement, but if the column is referenced, the specified value will be used in place of the identity.

Attempting to specify the value NULL in this case results in an error, since identity columns are always NOT NULL.


CREATE TABLE order_master(
  Id NUMBER GENERATED BY DEFAULT AS IDENTITY,
  Description  Varchar2(20)
);

Insert into order_master (description) values('First Order');
Insert into order_master (id,description) values(20,'Second Order');

select * from order_master;

SQL> select * from order_master;

        ID DESCRIPTION
---------- --------------------
         1 First Order
        20 Second Order

Using BY DEFAULT ON NULL allows the identity to be used even when the identity column is referenced and NULL value is specified.


CREATE TABLE order_master(
  Id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY ,
  Description  Varchar2(20)
);

Insert into order_master (id,description) values(null,'First Order');
Insert into order_master (id,description) values(20,'Second Order');
Insert into order_master (description) values('Third Order');


SQL> select * from order_master;

        ID DESCRIPTION
---------- --------------------
         1 First Order
        20 Second Order
         2 Third Order

Note: Oracle uses a sequence internally to generate value to populate the identity column.

Using RETURN_RESULT

Oracle 12c allows a procedure to return a list of rows by using RETURN_RESULT procedure of DBMS_SQL package.

Rather than defining explicit ref cursor out parameters, the RETURN_RESULT procedure in the DBMS_SQL package allows you to pass them out implicitly.

The following procedure returns list of rows from JOBS table as return value from a procedure.


CREATE OR REPLACE PROCEDURE get_jobs
AS
  job_cursor SYS_REFCURSOR;

BEGIN
   OPEN job_cursor FOR
   SELECT * from jobs;
   DBMS_SQL.RETURN_RESULT(job_cursor);
END;

You can call the procedure and get the list of rows from the procedure as follows:


EXECUTE get_jobs

Functions in the WITH Clause

The declaration section of the WITH clause can be used to define PL/SQL functions, as shown below.


WITH
  FUNCTION  Experience(hd date) RETURN NUMBER IS
  BEGIN
    RETURN  floor ((sysdate - hd)  / 365);
  END;
SELECT First_name, Experience(hire_date)
FROM   Employees
/

From a name resolution perspective, functions defined in the PL/SQL declaration section of the WITH clause take precedence over objects with the same name defined at the schema level.

OFFSET and FETCH clauses

Oracle 12c provides enhanced support for top-n analysis.

OFFSET provides a way to skip the N first rows in a result set before starting to return any rows
The FETCH clause limits the number of rows returned in the result set
For the result offset clause, the value of the integer literal must be equal to 0 (default if the clause is not given), or positive. If it is larger than the number of rows in the underlying result set, no rows are returned.
For the fetch first clause, the value of the literal must be 1 or higher. The literal can be omitted, in which case it defaults to 1. If the clause is omitted entirely, all rows (or those rows remaining if a result offset clause is also given) will be returned.


OFFSET { integer-literal | ? } {ROW | ROWS}
FETCH { FIRST | NEXT } [integer-literal | ? ] {ROW | ROWS} ONLY

ROW is synonymous with ROWS and FIRST is synonymous with NEXT.

The following examples show how to use these clauses.


Select * from employees fetch first row only;                         // retrieves only first row

select * from employees fetch next 5 rows only;                       // retrieves first 5 rows from the beginning

select * from employees offset 5 rows fetch next 5 rows only;         // retrieves 5 rows from 5th row

select * from employees order by salary desc fetch next 5 rows only;  // retrieves first 5 highest salaried employees

Other Miscellaneous Features

The maximum size of VARCHAR2, NVARCHAR2, and RAW data types has been increased from 4,000 to 32,767 bytes. Increasing the allotted size for these data types allows users to store more information in character data types before switching to large objects (LOBs). This is especially useful for brief textual data types and the capabilities to build indexes on these types of columns.
Through Oracle Database 11g Release 2 (11.2), only definer's rights PL/SQL functions could be result cached. Now, invoker's rights PL/SQL functions can also be result cached.
Prior to Oracle 12c R1, undo records generated by the temporary tables used to be stored in undo tablespace. However, with the temporary undo feature in 12c, the temporary undo records can now be stored in a temporary table instead of undo tablespace.