Hi!

Oracle12c’s PL/SQL has many changes.  I’ll document some of the changes here.  I’m not comfortable posting examples from the Oracle documentation here so if you don’t have the Oracle documentation at your finger tips…go to Oracle.com, put ‘Documentation’ into the search window and download the .zip file from there.  Then look up these features for working examples.

The SQL WITH clause now allows for a PL/SQL function to be defined as part of the clause.  Oracle12 also calls this ‘Run Faster PL/SQL’…

I’ve blogged a few weeks ago about the new size limits of the Varchar2, Nvarchar2, and Raw columns.

Last week, I discussed the ‘Fetch First/Fetch Next’ syntax.  Guess what…this now works with Bulk Collect and Dynamic SQL (execute immediate syntax).

PL/SQL can also access invisible columns.  

There are many new procedures and functions to the supplied packages.  There are many enhancements to existing supplied packages.  Some of these features include:

  • UTL_CALL_STACK
    • Backtrace_dept_line_unit
    • Drror_dept_msg_number
    • Unit_Line
    • Subprogram
  • DBMS_SQL.Parse
    • Schema input parameter now

Lets talk about Oracle12 and AUTHID.  AUTHID allows for rights to be passed to the current user from the one that created the object (definer rights).  The default has been and is still ‘definer’ rights…this is why we DBA types like a single production user that owns everything.  This is an option at compile time.

Oracle12 has fixed several small issues in regards to AUTHID.  First…there was an issue if using AUTHID and the Oracle11 Result Cache…Oracle11 did not track the user as part of the Result Cache.  Oracle12 does track the user as part of the Result Cache so this restriction is now gone.  In Oracle12…you can use AUTHID set to Current User and Result Cache together.

Another area of concern with privileges is what the current logged in user has access to versus what the procedures and functions have access to.  In Oracle11 and before, if a user executing a function that needed access to SCOTT.EMP’s data, select privileges would need to be granted to the user…allowing the user to access the SCOTT.EMP table now from any tool.  Oracle12 fixes this issue by allowing roles to be granted to procedures and functions.  Oracle12 now allows for a role that has access to SCOTT.EMP to be granted to the function…NOT necessarily to the user!  Now, the function can select the information it is requesting without the executing user being able to have the same access privileges to the SCOTT.EMP table!

This technique will work on any object where you don’t want the logged in user to see data that is being requested by procedures or functions in an application.

Dan Hotka
Oracle ACE Director
Instructor/Author/CEO