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.
Join the DZone community and get the full member experience.Join For Free
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)
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.
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;
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, ... );
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;
Opinions expressed by DZone contributors are their own.