Top 5 New Features in Oracle 12c for Developers

Oracle 12c is the latest version of Oracle database, and it has been out for a few years. In this tip, I'll share five of my favourite features for developers in Oracle 12c.

Row Limiting with the TOP N Clause

One of the most useful features of Oracle 12c is the ability to limit rows easily.

This is great for pagination and other similar requirements.

In older versions of Oracle, you needed to have one or two nested subqueries with the ROWNUM function. Now, you can just add the new clause to the query and you'll get your results.

An example of this query is shown here:

SELECT first_name, last_name, date_of_birth
FROM student
ORDER BY date_of_birth
FETCH FIRST 10 ROWS ONLY;

This will get the first 10 rows after the ordering has been applied.

If you wanted to get the "second page", or rows 11 to 20 (or any pair of numbers), you can use OFFSET.

SELECT first_name, last_name, date_of_birth
FROM student
ORDER BY date_of_birth
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

This will give you rows 11 to 20.

Identity Columns

Another great feature is the ability to have identity columns in Oracle 12c.

This feature allows you to have column values generated automatically.

It's like having an easy way to apply a sequence value to a column.

Before this, you'd have to use an INSERT trigger with a sequence. Now, you can do it on the column definition.

CREATE TABLE idtest (
new_id NUMBER GENERATED AS IDENTITY,
first_name VARCHAR2(100)
last_name VARCHAR2(100)
);

In this table, whenever you omit the value for new_id, a value is inserted. It works just like a sequence.

Grant Roles to PL/SQ Programs Only

In Oracle 11g and earlier versions, whenever you wanted to run a PL/SQL program that accessed a table, you needed to grant access to the user running the program both to the program and the underlying table.

This felt a bit strange, because the package was the one accessing the table. And whenever you adjusted the package, you needed to adjust the access to the users.

In Oracle 12c, you can just give access to the user to the PL/SQL package, and not the underlying tables. This makes it easier to administrate and better for security.

Period Definition

Oracle 12c allows you to easily define when a record is effective from. Before having this feature, it was often done using start and end dates, or an effective date, in the database table.

Now, when you create a table, you add a PERIOD clause:

CREATE TABLE student (
student_id NUMBER
first_name VARCHAR2(50),
last_name VARCHAR2(50),
start_date DATE,
end_date DATE,
PERIOD FOR VALID (start_date, end_date);

This PERIOD clause refers to two columns, which are the start and end date. Now, you'll still need these columns, but it makes your queries a lot easier.

SELECT student_id, first_name, last_name, start_date, end_date
FROM student
AS OF PERIOD FOR VALID sysdate;

You add in the AS OF PERIOD FOR VALID, and then a date. The date can be any value you like, and the records returned will be the ones where the date is between the start and end date.

It's a bit of an improvement to a process that I've seen quite often.

APPROX_COUNT_DISTINCT

Have you ever been investigating a table and wanted to find out how many records it contains with a simple SELECT COUNT(*) query, but the query took a long time to run? Or, if you wanted to find the number of distinct values in a column, but the query was slow?

Well, we now have an easier way to run this query.

A new function, called APPROX_COUNT_DISTINCT. This lets you find the approximate number of distinct values in a column.

It's called an approximate because it doesn't give you the exact number. But, the number is pretty close.

SELECT COUNT(DISTINCT first_name)
FROM student;

SELECT APPROX_COUNT_DISTINCT(first_name)
FROM student;

The first query uses the actual COUNT function, and the second uses the new function. The second query should run a lot faster and get a number that is pretty close to the correct one.

So, there are my five favourite features for Oracle 12c developers.