My Favorite Oracle 12c Enhancements

The latest release of the Oracle database is labeled 12c. Larry Ellison describes this new kind of database as the “first multi-tenant database in the world.” The idea behind multi-tenancy is that one or many companies can share the same hardware, behind the cloud. Oracle is calling 12c a “container database”. This type of database is designed to house several databases, while keeping the data content of each database separated, but allowing them to share the same hardware resources. Like in all new releases of the database, Oracle has introduced some new enhancements for DBA’s and developers alike. This article focuses on 5 new enhancements that I believe are noteworthy for the Oracle developer community.

 1.     Truncate table with Cascade option

 Prior to 12c, the TRUNCATE TABLE command could only be used to truncate tables that were not referenced by records in child tables. This meant that all child tables needed to be truncated or deleted with a series of separate commands, before the master table could be truncated. With the new Truncate Table with Cascade option in 12c, when the master table is truncated a recursive truncate of all child tables is automatically performed.

 Note: The Foreign keys of the child tables must be defined with the ON DELETE CASCADE option.

2.     VARCHAR2 database type extended to 32,767 bytes

 With the release of Oracle 12c the VARCHAR2 data type has now been extended to match the size of its PL/SQL counterpart. In Oracle 11 the max size of a VARCHAR2 table column was 4,000 bytes and in PL/SQL the max declarable size of a VARCHAR2 variable was 32,767 bytes. It’s easy to see where I am going here; even though it was possible to declare and work with a VARCHAR2 variable that was larger than 4,000 bytes, in an 11c PL/SQL unit, it was not possible to store that same variable in the database as a VARCHAR2 column. Now, this is no longer a restriction.

3.     Declare PL/SQL functions in a SQL WITH clause

Oracle SQL has long since had the ability to call PL/SQL functions that are stored in the database. Now, Oracle 12c allows for the local definition of PL/SQL functions in a SQL statement. By declaring the PL/SQL function locally in the SQL statement, you have effectively eliminated the context switching that would occur between SQL and a PL/SQL function that’s stored in the database.

4.     Identity Column (surrogate key – sequential number – no business meaning)

 Identity columns are new to the Oracle Database with the release of Oracle 12c. The identity column feature is used to automatically assign a sequentially generated number to a column in a newly created table row. This is accomplished by using an Oracle Sequence under the covers to generate a sequential number. In previous versions of the Oracle database, developers often used a database trigger in conjunction with an Oracle Sequence to accomplish this task.

What are your favorite enhancements from Oracle 12c? Join the conversation below!