Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Oracle 12c's New Features For Developers

DZone's Guide to

Oracle 12c's New Features For Developers

An overview of Oracle 12c's new features, including invisible columns, identity columns, and having multiple indexes on the same column.

· Database Zone
Free Resource

Traditional relational databases weren’t designed for today’s customers. Learn about the world’s first NoSQL Engagement Database purpose-built for the new era of customer experience.

Oracle Database 12c with “multitenant” architecture database management gets very easy. It provides pluggable database environment. It can consists many database in one environment. All databases getting together share same backgroud proseses and memory. This architecture makes database management easier and at the same time it provides an environment for cloud architecture.

With Oracle Database 12c a lot of useful features came out for DBAs and developers.  In this article I mention some of new features for database developers.

Sequence as Default Value 

Before Oracle 12c we have to assign the variable or column seqeunce using sequence.nextval, but now we can use sequence as default value. when new row is added sequence automatically increments and writes the column. It can be shown an example below:

Create sequence seq_t start with 1 increment 1 nocycle;

Create table t_my_tab (id number default seq_t.nextval primary key)

Invisible Column

Another new feature is we can define a table column as invisible columm. when we define column as invisible it is not being fetched when we use generic query like "select * from" . It can be queried by only direct usage like "select invcolumn from ..". This feature is useful especially when we dont need that column instead of drop it or some security reasons for example password column etc.

ALTER TABLE t_my_tab  MODIFY (pwd invisible);

Multiple Indexes On the Same Column

Multiple indexes on the same column cannot be created in prior version. With oracle 12c this is possible but multiple indexes must be different types like BitMap,B-Tree etc.

Varchar2 Length to 32,767 Bytes

The maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has been increased from 4,000 to 32,767 bytes. 

Top-N Feature

With Oracle 12c new keywords came out for Top-N queries.  It can be shown an example below:

SELECT value FROM t_deneme

ORDER BY value DESC

FETCH FIRST 100 ROWS ONLY;

Identity Columns

With Oracle 12c we can define a column as identity column. When new row is added sequence automatically increments and writes the column. It can be shown an example below:

create table t_my_tab (

id number generated as identity,

...

);

With Clause

We can use pl/sql in select query using new with clause. It can be shown an example below:

WITH

FUNCTION f_incr(n IN NUMBER) RETURN NUMBER IS

BEGIN

RETURN n+1;

END;

SELECT f_incr(1)

FROM dual;


Learn how the world’s first NoSQL Engagement Database delivers unparalleled performance at any scale for customer experience innovation that never ends.

Topics:
database ,oracle ,sql ,pl/sql ,12c ,new features

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}