Oracle Database 12c New Features for Developers

Tuesday, November 5, 2013

On July 1st, 2013 Oracle released the Oracle 12c Database, the first database designed for the cloud.
In this blog post we'll discuss some cool, new features for SQL and PL/SQL developers in the Oracle 12c Database


Some figures

Important new release:

  • 5 years in development
  • 500 new features
  • 2,500 person years development
  • 3,000 systems used in testing
  • 1 million feature tests run daily
  • 1.2 million hours of stress tests



The core aims of Oracle Database 12c are:

  • Reduce the cost of storing data
  • Provide continued access to data
  • Enable quicker access to data
  • Provide simpler management of data
  • Ensure secure access to data


One of the main new features Oracle offers to accomplish this is the “Multitenant architecture with pluggable dabases”.

As you can see in the picture it will be possible, for example, to combine the 3 separate databases (ERP, CRM and DW) into 1 container database (CDB) with 3 pluggable databases (PDB).

Oracle 12c Container DB




Memory, processes and metadata are maintained on 1 place (CDB) for all 3 PDB's.

The PDB's only contain the application data and the specific data dictionaries for their own system.

In consequence, the PDB's need less hardware resources and are better scalable. And DBA work like upgrading, patching and migrations should be much simpler and thus faster.

For example: One patch which is executed on the CDB is also immediately installed on all PDB's in the container.

This feature is especially important for large companies with a lot of databases who want to simplify their maintanance.


New features

The complete list of new features can be found in this list.

We'll discuss some features which will come in handy for SQL and PL/SQL developers:

  •   PL/SQL Functions Defined in the SQL WITH Clause

It's possible to use a PL/SQL function in a SQL WITH clause.

In the example below a function is set in the WITH clause to add a commission of 10% to the salary:


    FUNCTION commission(sal number)

    RETURN number



       return sal * 1.1;


    SELECT ename, sal, commission(sal)



  • Default Values for Columns Based on Oracle Sequences

Default values for columns can directly refer to Oracle sequences. Valid entries are sequence.CURRVAL and sequence.NEXTVAL.

Providing the functionality to directly refer to a sequence as a default value expression simplifies code development.


Create table my_table


id number default my_sequence.nextval primary key



  •  IDENTITY Columns

Instead of using a default sequence, the column can also be created with the keyword “IDENTITY”.

This provides a standards based approach to the declaration of automatically incrementing columns, which simplifies application development and makes the migration of DDL to Oracle simpler.


create table my_table


id number generated as identity



This standard ensures an auto-increment of the column value when adding new data to the table.


  • Increased Size Limit for VARCHAR2, NVARCHAR2 and RAW Data Types

The length of the VARCHAR2, NVARCHAR2 and RAW datatypes has been enlarged from 4000 to 32767 bytes.

So, a LOB type is less quickly necessarry to use in a table and this could have a significant impact on the memory and disk usage of the database.

  •   Adaptive Query Optimization

It is possible for the optimizer to miscalculate some estimations during initial plan generation. Adaptive query optimization allows these miscalculations to be corrected in one of the following two ways:

  • With adaptive plans, a plan can be stopped during execution and reoptimized based on information collected during the initial part of the execution. For example, if the initial plan choice was to do a NESTED LOOP with an estimated cardinality of 1, that plan is stopped after 1,000 records have been sent to the join and restarted using a HASH JOIN instead because the initial cardinality estimate was wrong.
  •   Automatic reoptimization does not affect the initial execution of a statement. Instead, the initial execution of a query is monitored and, if the actual execution statistics vary significantly from the original plan estimates, the execution statistics are recorded and used the next time the statement is executed to see if a new plan will be chosen for subsequent execution.

Adaptive query optimization is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics.

  • Concurrent Execution of UNION and UNION ALL Branches

The UNION and UNION ALL statements can run several branches concurrently instead of working on them one-by-one.

Executing multiple branches of UNION and UNION ALL statements in parallel speeds up processing time and leads to better resource utilization.

  •  In-Database Archiving

In-Database Archiving allows users and applications to set the archive state for individual rows. Rows that have been marked as archived will not be visible unless the session is enabled to see archived data.

With In-Database Archiving, more data can be stored in production databases for a longer period of time without compromising application performance. In addition, archived data can be aggressively compressed to help improve query and backup performance. Updates to archived data can be deferred during application upgrades, greatly improving the performance of upgrades.

  • SecureFiles is the Default for LOB Storage

In this release, SecureFiles is now the default for LOB storage when the compatible initialization parameter is set to 12.1 or higher.

The SecureFiles feature provides optimal performance for storing unstructured data in the database. Making SecureFiles the default for unstructured data helps ensure that the database is delivering the best performance possible when managing unstructured data.

  • Invisible Columns

The property of whether a column is visible can be controlled by the user. Invisible columns are not seen unless specified explicitly in the SELECT list. Any generic access of a table (such as a SELECT * FROMtable or a DESCRIBE) will not show invisible columns.

This can benefit the safety of the database.


create table users


id number,

name varchar2(100),

email varchar2(255),

password varchar2(100) INVISIBLE



A describe of this table would return the following:


SQL>desc users

Name                      Null?        Type

------------ ------------- -------------------------

ID                                           NUMBER

NAME                                     VARCHAR2(100)

EMAIL                                    VARCHAR2(255)


  • Multiple Indexes on Same Set of Columns

Multiple indexes can be created on the same set of columns as long as some characteristic is different. Qualifying characteristics are:

  • B-tree versus bitmap
  • Different partitioning strategies
  • Unique versus nonunique


Providing the capability to create multiple indexes on the same set of columns enables transparent and seamless application migrations without the need to drop an existing index and re-create it with different attributes.

  • CASCADE option for TRUNCATE and EXCHANGE commands

For example:

The command TRUNCATE TABLE my_table CASCADE will empty the complete table my_table, but also all optionally child-records.

  • Native SQL Support for Query Row Limits and Row Offsets

The FETCH FIRST and OFFSET clauses provide native SQL language support to limit the number of rows returned and to specify a starting row for the return set.

Many queries need to limit the number of rows returned or offset the starting row of the results. For example, top-N queries sort their result set and then return only the first n rows. FETCH FIRST and OFFSET simplify syntax and comply with the ANSI SQL standard.

For example: Query the top 5 highest earning employees:

SELECT ename, sal

FROM emp




Hope this helps!