Oracle Database 12c New PL/SQL Features

Oracle Community

Oracle Database 12c New PL/SQL Features

Follow / 7.1.2013 at 2:29pm

Hooray! Oracle made 12.1 on Linux generally available last week, ending the beta program and freeing me from the constraints of the Non-Disclosure Agreement. I can now share with you the new PL/SQL features of Oracle Database 12c. You will find below a list of those features. I will be exploring them in much more detail over the coming months, both here on the Toad World blog and in Oracle Magazine.

Oracle Database 12c offers a number of new features that improve the performance and usability of PL/SQL. It also rounds out some “rough edges” of the language. Here is a summary of the most important changes for PL/SQL developers.

More PL/SQL-Only Data Types Cross PL/SQL-to-SQL Interface

Prior to 12.1, you could not bind PL/SQL-specific datatypes (for example, an associative array) in a dynamic SQL statement.. Now it is possible to bind values with PL/SQL-only data types in anonymous blocks, PL/SQL function calls in SQL queries, CALL statements, and the TABLE operator in SQL queries.

ACCESSIBLE_BY Clause

You can now include an ACCESSIBLE_BY clause to your package specification, specifying which program units may invoke subprograms in the package. This feature allows you to “expose” subprograms in helper packages that are intended to be consumed only by specific program units. This feature offers a kind of “whitelisting” for packages.

Implicit Statement Results

Before Oracle Database 12c, a PL/SQL stored subprogram returned result sets from SQL queries explicitly, through an OUT REF CURSOR parameter or RETURN clause. The client program would then have to bind to those parameters explicitly to receive the result sets. Now, a PL/SQL stored subprogram can return query results to its client implicitly, using the PL/SQL package DBMS_SQL instead of OUT REF CURSOR parameters. This functionality will make it easy to migrate applications that rely on the implicit return of query results from stored subprograms (supported by languages like Transact SQL) from third-party databases to Oracle Database.

BEQUEATH CURRENT_USER Views

Before Oracle Database 12c, a view always behaved like a definer rights unit (AUTHID DEFINER), even if it was referenced inside an invoker rights unit (AUTHID CURRENT_USER). Now, a view can be either BEQUEATH DEFINER (the default), which behaves like a definer rights unit, or BEQUEATH CURRENT_USER, which behaves somewhat like an invoker’s rights unit.

Grant Roles to Program Units, plus INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges

Prior to Oracle Database 12c, an invoker rights unit always ran with the privileges of its invoker. If its invoker had higher privileges than its owner, then the invoker rights unit might perform operations unintended by, or forbidden to, its owner.

As of 12.1, you can grant roles to individual PL/SQL packages and standalone subprograms. Instead of a definer rights unit, you can create an invoker rights unit and then grant roles to it. The invoker rights unit then runs with the privileges of both the invoker and the roles, but without any additional privileges possessed by the definer’s schema.

An invoker rights unit can now run with the privileges of its invoker only if its owner has either the INHERIT PRIVILEGES privilege on the invoker or the INHERIT ANY PRIVILEGES privilege. Note: the INHERIT PRIVILEGES is granted to all schemas on install/upgrade.

New Conditional Compilation Directives

In 12.1, Oracle has added two new predefined inquiry directives, $$PLSQL_UNIT_OWNER and $$PLSQL_UNIT_TYPE, which return the owner and type of the current PL/SQL program unit.

Optimizing Function Execution in SQL

Oracle now offers two ways of improving PL/SQL function performance in SQL statements: you can actually define the function itself inside the SQL statement using the WITH clause, and you can add the UDF pragma to the program unit, which tells the compiler that the function will be used primarily in SQL statements.

Using %ROWTYPE with Invisible Columns

Oracle Database 12c makes it possible to define invisible columns. From within PL/SQL, the %ROWTYPE attribute is aware of these types of columns and how to work with them.

FETCH FIRST Clause and BULK COLLECT

In 12.1, use the optional FETCH FIRST clause to limit the number of rows that a query returns, significantly reducing the SQL complexity of common “Top-N” queries. FETCH FIRST will be of most benefit in the simplification of migration from third-party databases to Oracle Database. This clause can also, however, improve the performance of some SELECT BULK COLLECT INTO statements.

The UTL_CALLSTACK Package

Prior to 12.1, the DBMS_UTILITY package offered three functions (FORMAT_CALL_STACK, FORMAT_ERROR_STACK, and FORMAT_ERROR_BACKTRACE) to provide information about the execution call stack, error stack and error backtrace, respectively. Now in 12.1, a single package, UTL_CALLSTACK, provides that same information, plus much more “fine-grained” access to the contents of these formatted strings.

You can, of course, check out these features yourself, by downloading the software from: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Or you can simply start with perusing the documentation set, available at: http://www.oracle.com/pls/db121/homepage

11562 3 /
Follow / 4 Jul 2013 at 9:15am

Finally PL/SQL objects in SQL without the need to create DB side objects!

What's the difference between accessible_by and "grant execute on ... to ..."?

Seems that we have a lot of changes in the inner privileges management... I wonder what is the implementation there :)

What's the implementation of invisible columns? Is it by adding a new column at the end still having the limit of 1000 cols?

Follow / 19 Jul 2013 at 8:28am

My understanding : By using 'ACCESSIBLE_BY' clause, we are only restricting the function from not being used by anyother function.

Question : Is it same as subfunction.??

If yes,then what is the improvement using ACCESSIBLE_BY clause..??

If No,Can u please explain the difference.???

Follow / 19 Jul 2013 at 4:22pm

ACCESSIBLE_BY gives you more freedom to distribute low-level APIs among multiple packages, but then control which program units can access those APIs. I see the main value of this feature in helping you craft more manageable chunks of code.

Yes, many changes in the privileges management architecture, more flexibility than ever before. Since most developers have never even felt a need for AUTHID CURRENT_USER, I am not sure how widely these features will be utilized, but they offer substantial new capabilities!