Oracle 12c New Features

Oracle 12c New Features

  1. When you truncate a parent table with child tables, you get:ORA-02266: unique/primary keys in table referenced by enabled foreign keys. In Oracle 12c, you can use: truncate table <Parent> cascade; Must have defined the FK as ON DELETE CASCADE. Otherwise ORA-14705: unique or primary keys referenced by enabled foreign keys in table will result.

 

  1. Session Sequences – select seq1.nextval from dual;

– Session Seq: values visible only in the session

– Not persistent

SQL> create sequence sessseq session

;

SQL> create sequence globseq global

;

SQL> select globseq.nextval from dual;

3

SQL> select sessseq.nextval from dual;

1

 

  1. You can create different types of indexes(B-tree, bitmap) on same column. But Only one is visible at a time.

 

  1. Default Values

Starting with Oracle Database 12c, a column can be assigned a default non-null value whenever the user tries to insert NULL into the column. The default value will be specified in the DEFAULT clause of the column with a new ON NULL extension.

 

SQL> create table t5 (col1 number, col2 number default on null 0);

Table created.

SQL> desc t5

Name Null? Type

—————– ——– ——

COL1 NUMBER

COL2 NOT NULL NUMBER

SQL> insert into t5 values (1, null);

SQL> insert into t5 values (2,2);

SQL> select * from t5;

COL1 COL2

———- ———-

1    0

2    2

 

Oracle Database 12c allows developers to default a column directly to a sequence generated value. The DEFAULT clause of a table column can be assigned to SEQUENCE.CURRVAL or SEQUENCE.NEXTVAL.

 

/*Create a sequence*/

CREATE SEQUENCE seq START WITH 100 INCREMENT BY 10

/

/*Create a table with a column defaulted to the sequence value*/

CREATE TABLE t_def_cols

( id number default seq.nextval primary key,

name varchar2(30),

doj date default on null ’01-Jan-2000′

)

/

 

  1. Longer Varchar2 (Support for 32K VARCHAR2)

Oracle Database 12c supports the VARCHAR2, NVARCHAR2, and RAW datatypes up to 32,767 bytes in size. The previous maximum limit for the VARCHAR2 (and NVARCHAR2) and RAW datatypes was 4,000 bytes and 2,000 bytes respectively.

– CLOB behind the scenes

 

  1. IDENTITY columns

A table column, marked as IDENTITY, automatically generate an incremental numeric value at the time of record creation. Before the release of Oracle 12c, developers had to create an additional sequence object in the schema and assign its value to the column.

/*Create a table for demonstration purpose*/

CREATE TABLE t_id_col

(id   NUMBER GENERATED AS IDENTITY,

name VARCHAR2(20))

/

 

The identity column metadata can be queried from the dictionary views USER_TAB_COLS and USER_TAB_IDENTITY_COLS.Note that Oracle implicitly creates a sequence to generate the number values for the column

 

While inserting data in the table T_ID_COL, do not include the identity column as its value is automatically generated:

 

/*Insert test data in the table*/

BEGIN

INSERT INTO t_id_col (name) VALUES (‘Allen’);

INSERT INTO t_id_col (name) VALUES (‘Matthew’);

INSERT INTO t_id_col (name) VALUES (‘Peter’);

COMMIT;

END;

/

Let us check the data in the table. Note the identity column values:

 

/*Query the table*/

SELECT id, name FROM t_id_col

/

 

ID NAME

—– ——————–

1 Allen

2 Matthew

3 Peter

The sequence created under the covers for identity columns is tightly coupled with the column. If a user tries to insert a user-defined input for the identity column, the operation throws an exception ORA-32795:

 

INSERT INTO t_id_col VALUES (7,’Steyn’);

insert into t_id_col values (7,’Steyn’)

*

ERROR at line 1:

ORA-32795: cannot insert into a generated always identity column

 

  1. Row limiting using FETCH FIRST

For Top-N queries, Oracle Database 12c introduces a new clause, FETCH FIRST, to simplify the code and comply with ANSI SQL standard guidelines. The clause is used to limit the number of rows returned by a query. The new clause can be used in conjunction with ORDER BY to retrieve Top-N results. Another new clause, OFFSET, can be used to skip the records from the top or middle, before limiting the number of rows.

 

/*Query to list top-5 employees by salary*/

SELECT *

FROM t_fetch_first

ORDER BY sal DESC

FETCH FIRST 5 ROWS ONLY

/

 

/*Query to list top-25% employees by hiredate*/

SELECT *

FROM t_fetch_first

ORDER BY hiredate FETCH FIRST 25 PERCENT ROW ONLY

/

 

/*Query to list 2 employees after skipping first 5 employees*/

SELECT *

FROM t_fetch_first

ORDER BY SAL DESC

OFFSET 5 ROWS FETCH NEXT 2 ROWS ONLY

/

 

  1. Invisible columns

 

Oracle Database 12c supports invisible columns, which implies that the visibility of a column. A column marked invisible does not appear in the following operations:

 

SELECT * FROM queries on the table

SQL* Plus DESCRIBE command

Local records of %ROWTYPE

Oracle Call Interface (OCI) description

A column can be made invisible by specifying the INVISIBLE clause against the column. Columns of all types (except user-defined types), including virtual columns, can be marked invisible, provided the tables are not temporary tables, external tables, or clustered ones. An invisible column can be explicitly selected by the SELECT statement. Similarly, the INSERT statement will not insert values in an invisible column unless explicitly specified.

 

furthermore, a table can be partitioned based on an invisible column. A column retains its nullity feature even after it is made invisible. An invisible column can be made visible, but the ordering of the column in the table may change.

 

In the following script, the column NICKNAME is set as invisible in the table t_inv_col:

 

/*Create a table to demonstrate invisible columns*/

CREATE TABLE t_inv_col

(id NUMBER,

name VARCHAR2(30),

nickname VARCHAR2 (10) INVISIBLE,

dob DATE

)

/

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s