Srikanth Technologies

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 { 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

Post Your Comment

Image

Enter the code given in the above image :
Enter Your Name :
Enter Your Email Address :
Enter Your Comment :

Comments


Posted By Teja On 26-Oct-13 12:17:05 AM

Awesome sir.. We don't need to worry about the technology changes in oracle,java,.net if we follow you on twitter we get all the updates.
Thank you very much for sharing the info sir. You rock always



Posted By Sengar Abhishek On 20-Nov-15 04:03:43 PM

Great, Awesome work by updating our knowledge pool in easy to use manner.



Posted By Ashraya S N On 18-Mar-16 03:43:38 PM

books are reaaly good , I found it very interesting
I downloaded it now, please update me when you update the content of the book
thank you very much, this books are going to be helpfull for all beginners like me and for those who want to revise



Posted By Hemantraj On 15-Nov-16 08:05:10 PM

Your notes is very best for beginners.