Oracle Database is a database management system that the company works very hard on to improve and enhance.  Oracle Database 12c comes with a bevy of features that allow you to have the database of the future today.  One of its noteworthy features and enhancements is related to the PL/SQL.

Oracle Database 12c gives you an assortment of improvements that pertain to how you execute and define PL/SQL program units, including:

–          Optimizing invoker rights for RESULT_CACHE clause

–          Defining and running PL/SQL functions within SQL statements

–          Limit access to program units by using whitelists via ACCESSIBLE BY

–          Fine-tuning program unit privileges

Invoker rights and RESULT_CACHE

The RESULT_CACHE was introduced in Database 11g as a very efficient and easy to use caching system.  It helps you use a cache for a row of data that has not been changed from the last time it was retrieved.

In Oracle Database 12c, you could now combine invoker rights with the RESULT_CACHE function without resulting in any compilation error.

Defining PL/SQL Subprograms Found in an SQL statement

Developers have always been able to call PL/SQL functions within an SQL statement, enabling them to do more with SQL.  The good news is that they are now able to define PL/SQL procedures and functions using the WITH clause in any subquery.  This enables them to use these in a similar manner as any user-defined or built-in function.  This allows you to simplify functions and queries instead of writing long lines of codes.

It also improves performance.  By being able to move the code inside your SQL statement, you will be able to bypass having to use a context switch, which slows down the performance.

Whitelisting and the ACCESSIBLE BY clause

PL/SQL applications typically have many packages within them, including top level application programming interfaces and helper packages.

The problem before was that any session that has been granted an EXECUTE authority could easily access all of these subprograms within the application.  With Oracle Database 12c, you can add an ACCESSIBLE BY clause so that you could specify just what can be accessed.

Granting roles to different program units

In previous releases of Oracle Database, the definer’s rights are always executed with the definer’s privileges for a particular unit.  Now with Oracle Database 12c, you can just grant specific release to different PL/SQL packages, as well as functions and procedures on a schema level.  These privileges are role-based, which can modify the rights that are given to the invoker of that particular program unit.

Four Cornerstone can help you get Oracle Database 12c up and running in no time at all.  We can get this complex procedure off your hands.  In addition, we have a group of highly trained professionals who could teach your IT team everything it needs to know about Oracle Database 12c.  Oracle’s latest Database iteration (PL/SQL) comes with marked improvements in terms of functionality and flexibility. Your developers would love these new enhancements.  You simply have to stop waiting and start working better, and with our help, you can do it today! Contact us now!

Photo courtesy of Oracle_Photos_Screenshots.