Oracle 12c New PL/SQL Features

Now let's see what Oracle 12c has in store for PL/SQL and database changes. The RESULT_CACHE has been improved. You can now use it with functions that specify invoker's rights. The WITH clause can define a PL/SQL function that you can call directly from SQL without a PL/SQL context switch. It can also be used to get access to a constant from a package.

ACCESSIBLE BY will allow you to specify packages that can access your newly defined package. You cannot call this newly defined package directly. But the packages on the "white list" can. This allows you to define helper functions that have restricted usage.

You can now grant roles to program units. That is, grant them to packages, procedures, and functions. Then you can use invoker's rights on them. They can get the privileges they need through the roles. You can bind PL/SQL types such as BOOLEAN to SQL. FETCH FIRST let's you limit the number of rows that return from a query.

BEQUEATH_CURRENT_USER let's you specify a view to have invoker's rights. Invisible columns can be created when you specify INVISIBLE during the DDL. They can only be seen if you explicitly name them. DESCRIBE does not see them. Neither does SELECT *. However %ROWTYPE knows about them. They have no column order. Strangely enough, that can be mandatory columns.

The UTL_CALLSTACK package contains routines previously found in DBMS_UTILITY. Then there is the EXPAND_SQL_TEXT in the DMBS_UTILITY package that expands view queries to their base tables. You can ENABLE_DDL_LOGGING. This is an init param. All DDL will now be logged in an XML file. It can be activated at the database or session level.

Temporary undo can now be stored in a temporary table instead of the undo tablespace. You can now issue a TRUNCATE TABLE CASCADE to get rid of tables plus those that depend on the table you are truncating.