PL/SQL Enhancements

Oracle Database 12c enhances the PL/SQL function result cache, improves PL/SQL execution in SQL, adds a whitelist, and fine-tunes privileges.

By Steven Feuerstein

September/October 2013

Oracle Database 12c offers a variety of enhancements to the way you can define and execute PL/SQL program units. This article covers several new Oracle Database 12c features that enable you to do the following:

• Optimize invoker rights functions for the RESULT_CACHE clause

• Define and execute PL/SQL functions inside SQL statements

• Restrict access to program units through use of a “whitelist,” specified by the ACCESSIBLE BY clause

• Fine-tune privileges for a program unit by granting roles to that unit

Oracle Database 11g introduced the PL/SQL function result cache, which offers a very powerful, efficient, and easy-to-use caching mechanism. The main objective of this cache is to ensure that if a row of data hasn’t changed since it was last fetched from the database, no SQL statement needs to execute for it to be retrieved again.

This holds true across the entire database instance. In other words, suppose a user connected to schema USER_ONE executes a result-cached function to retrieve the row from the employees table for employee ID = 100. When a user connected to schema USER_TWO executes the same function call for the same employee ID, that row of information is retrieved directly from the cache and not by execution of a SELECT statement.

If you are not already using this feature (and you are using Oracle Database 11g), I strongly encourage you to investigate it and start applying it—in close collaboration with your DBA so that the result cache pool is sized properly.

Even in Oracle Database 11g Release 2, however, you could not combine invoker rights (AUTHID CURRENT_USER clause) with the function result cache (RESULT_CACHE keyword). An attempt to compile the following function:

CREATE OR REPLACE FUNCTION last_name (
  employee_id_in
  IN employees.employee_id%TYPE)
  RETURN employees.last_name%TYPE
  AUTHID CURRENT_USER
  RESULT_CACHE
IS
  l_return   employees.last_name%TYPE;
BEGIN
  SELECT last_name
    INTO l_return
    FROM employees
   WHERE employee_id = employee_id_in;
  RETURN l_return;
END;
/

results in this compilation error:

PLS-00999: implementation restriction
(may be temporary) RESULT_CACHE is
disallowed on subprograms in
Invoker-Rights modules

The reason for this restriction is the whole point of invoker rights. At runtime the PL/SQL engine uses the privileges of the current user to resolve references to database objects such as tables and views. But if such a function was compiled with RESULT_CACHE, then (using the example above) after USER_ONE executed the function, passing in 100, when USER_TWO makes the same function call, the body of the function would not be executed and the reference to the EMPLOYEES table would not be resolved according to USER_TWO’s privilege. This could have caused serious security issues.

Well, the good news is that this restriction was temporary. In Oracle Database 12c, you can now compile functions such as last_name (above) without error—and Oracle Database 12c does the right thing, of course.

Behind the scenes, Oracle Database 12c passes the name of the current user as a hidden parameter; this value is cached along with the values of all the arguments passed to the function. So each time the last_name function is called, Oracle Database 12c checks to see if that function has been previously called with both the same employee ID and the same current user.

This means that the result cache for an invoker rights function is (logically) partitioned by the name of the current user. Consequently, the result cache for an invoker rights function will improve performance only in situations in which the same user calls the function with the same argument values repeatedly. Another way of explaining this is to point out that in Oracle Database 11g Release 2, I could have achieved the same effect, but only if I had changed the implementation of the last_name function, as shown in Listing 1.

Code Listing 1: “Partitioned” Oracle Database 11g Release 2 invoker’s rights function

CREATE OR REPLACE PACKAGE employee_api
   AUTHID CURRENT_USER
IS
   FUNCTION last_name (
      employee_id_in IN employees.employee_id%TYPE)
      RETURN employees.last_name%TYPE;
END;
/
CREATE OR REPLACE PACKAGE BODY employee_api
IS
   FUNCTION i_last_name (
      employee_id_in   IN employees.employee_id%TYPE,
      user_in          IN VARCHAR2 DEFAULT USER)
      RETURN employees.last_name%TYPE
      RESULT_CACHE
   IS
      l_return   employees.last_name%TYPE;
   BEGIN
      SELECT last_name
        INTO l_return
        FROM employees
       WHERE employee_id = employee_id_in;
      RETURN l_return;
   END;
   FUNCTION last_name (
      employee_id_in IN employees.employee_id%TYPE)
      RETURN employees.last_name%TYPE
   IS
      l_return   employees.last_name%TYPE;
   BEGIN
      RETURN i_last_name (employee_id_in,
                          USER);
   END;
END;
/

Note that the last_name function is defined in the package specification and is not result-cached. Instead, that public function (declared in the package specification) merely calls the private/internal “version” of the function, which has a second parameter: the user.

So each time you call employee_api.last_name, Oracle Database 11g Release 2 adds the name of the user to the set of values used by the database to determine whether there is a match in the result cache.

This is no longer necessary; in Oracle Database 12c, you simply need to decide if you think it is worth adding RESULT_CACHE to invoker rights programs.

Developers have long been able to call their own PL/SQL functions from within a SQL statement. Suppose, for example, I have created a function named BETWNSTR that returns the substring between the specified start and end locations:

FUNCTION betwnstr (
   string_in      IN   VARCHAR2
 , start_in       IN   PLS_INTEGER
 , end_in         IN   PLS_INTEGER
)
   RETURN VARCHAR2
IS
BEGIN
   RETURN ( SUBSTR (
        string_in, start_in,
        end_in - start_in + 1 ));
END;

I can then use it in a query as follows:

SELECT betwnstr (last_name, 3, 5)
  FROM employees

This approach offers a way to both “extend” the SQL language with application-specific functionality and reuse (rather than copy) algorithms. A downside of user-defined function execution in SQL is that it involves a context switch between the SQL and PL/SQL execution engines.

With Oracle Database 12c, you can now define PL/SQL functions and procedures in the WITH clause of a subquery and then use them as you would any other built-in or user-defined function. This feature enables me to consolidate the BETWNSTR function and query shown above into a single statement:

WITH
 FUNCTION betwnstr (
     string_in   IN VARCHAR2,
     start_in    IN PLS_INTEGER,
     end_in      IN PLS_INTEGER)
 RETURN VARCHAR2
 IS
 BEGIN
   RETURN (SUBSTR (
       string_in,
       start_in,
       end_in - start_in + 1));
 END;
SELECT betwnstr (last_name, 3, 5)
  FROM employees

So why would a developer want to copy logic from a PL/SQL function into a SQL statement? To improve performance. When I call my own PL/SQL function in a SQL statement, the SQL engine must perform a performance-affecting context switch to the PL/SQL engine. Moving the code inside the SQL statement means that that context switch no longer occurs.

Although you can call a packaged function in SQL, you cannot reference a constant declared in a package (unless that SQL statement is executed inside a PL/SQL block). Here’s an example of the constant reference limitation:

SQL> CREATE OR REPLACE PACKAGE pkg
  2  IS
  3     year_number
        CONSTANT INTEGER := 2013;
  4  END;
  5  /
Package created.
SQL> SELECT pkg.year_number
FROM employees
  2   WHERE employee_id = 138
  3  /
SELECT pkg.year_number FROM employees
ERROR at line 1:
ORA-06553: PLS-221: 'YEAR_NUMBER' is not
a procedure or is undefined

The classic workaround to this limitation has been to define a function in the package and then call the function:

SQL> CREATE OR REPLACE PACKAGE pkg
  2  IS
  3     FUNCTION year_number
  4        RETURN INTEGER;
  5  END;
  6  /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY pkg
  2  IS
  3     c_year_number
        CONSTANT INTEGER := 2013;
  4
  5     FUNCTION year_number
  6        RETURN INTEGER
  7     IS
  8     BEGIN
  9        RETURN c_year_number;
 10     END;
 11  END;
 12  /
Package body created.
SQL> SELECT pkg.year_number
  2    FROM employees
  3   WHERE employee_id = 138
  4  /
YEAR_NUMBER
———————————
       2013

That’s a lot of code and effort simply to be able to reference the constant’s value in a SQL statement. And, with Oracle Database 12c, it is no longer necessary. I can revert to and recompile the original pkg, containing just a single constant named year_number in the package specification, and create a function in the WITH clause:

WITH
 FUNCTION year_number
 RETURN INTEGER
 IS
 BEGIN
   RETURN pkg.year_number;
 END;
SELECT year_number
  FROM employees
 WHERE employee_id = 138

You will also find in-SQL PL/SQL functions to be handy in standby read-only databases. Although you will not be able to create “helper” PL/SQL functions in such a database, you will be able to define that function directly inside the query.

This WITH FUNCTION feature is a very useful enhancement to the SQL language. You should, however, ask yourself this question each time you contemplate using it: “Do I need this same functionality in multiple places in my application?”

If you do need it, you should decide if the performance improvement of using WITH FUNCTION outweighs the potential downside of copying and pasting this logic into multiple SQL statements.

Most PL/SQL-based applications are made up of many packages, some of which are the “top level” API to be used by programmers to implement user requirements and others of which are “helper” packages that are to be used only by certain other packages.

Before Oracle Database 12c, PL/SQL could not prevent a session from using any and all subprograms in packages to which that session’s schema had been granted EXECUTE authority. As of Oracle Database 12c, in contrast, all PL/SQL program units have an optional ACCESSIBLE BY clause that enables you to specify a whitelist of other PL/SQL units that can access the PL/SQL unit you are creating or altering.

Let’s take a look at an example. First I create my “public” package specification, which is intended for use by other developers to build the application.

CREATE OR REPLACE PACKAGE public_pkg
IS
   PROCEDURE do_only_this;
END;
/

Next, I create the specification of my “private” package. The package is private in the sense that I want to make sure that it can be invoked only from within the public package (public_pkg). So I add the ACCESSIBLE_BY clause:

CREATE OR REPLACE PACKAGE private_pkg
   ACCESSIBLE BY (public_pkg)
IS
   PROCEDURE do_this;
   PROCEDURE do_that;
END;
/

Now it’s time to implement the package bodies. The public_pkg.do_only_this procedure calls the private_pkg subprograms:

CREATE OR REPLACE PACKAGE BODY public_pkg
IS
   PROCEDURE do_only_this
   IS
   BEGIN
      private_pkg.do_this;
      private_pkg.do_that;
   END;
END;
/
CREATE OR REPLACE PACKAGE BODY
private_pkg
IS
   PROCEDURE do_this
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('THIS');
   END;
   PROCEDURE do_that
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('THAT');
   END;
END;
/

I can now run the public package’s procedure without any problem:

BEGIN
   public_pkg.do_only_this;
END;
/
THIS
THAT

But if I try to call a subprogram in the private package in an anonymous block, I see this error:

BEGIN
   private_pkg.do_this;
END;
/
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00904: insufficient privilege to
access object PRIVATE_PKG
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

And the same error occurs if I try to compile a program unit that tries to call a subprogram in the private package:

SQL> CREATE OR REPLACE PROCEDURE
use_private
  2  IS
  3  BEGIN
  4     private_pkg.do_this;
  5  END;
  6  /
Warning: Procedure created with
compilation errors.
SQL> SHOW ERRORS
Errors for PROCEDURE USE_PRIVATE:
LINE/COL ERROR
———————— ——————————————————————————
4/4      PL/SQL: Statement ignored
4/4      PLS-00904: insufficient
         privilege to access object
         PRIVATE_PKG

As the “PLS” error indicates, this issue is caught at compilation time. There is no runtime performance hit for using this feature.

Before Oracle Database 12c, a definer’s rights program unit (defined with the AUTHID DEFINER or no AUTHID clause) always executed with the privileges of the definer of that unit. An invoker’s rights program unit (defined with the AUTHID CURRENT_USER clause) always executed with the privileges of the invoker of that unit.

A consequence of these two distinct AUTHID settings is that program units that need to be executed by all users would have to be created as definer’s rights units. The program units would then execute with all the privileges of the definer, which might not be optimal from a security standpoint.

As of Oracle Database 12c, you can grant roles to PL/SQL packages and schema-level procedures and functions. Role-based privileges for program units enable developers to fine-tune the privileges available to the invoker of a program unit.

You can now define a program unit as having invoker’s rights and then complement the invoker’s privileges with specific, limited privileges granted through the role.

Let’s walk through an example that shows how to grant roles to program units and the impact it has. Suppose that the HR schema contains the departments and employees tables, defined and populated with data as follows:

CREATE TABLE departments
(
   department_id     INTEGER,
   department_name   VARCHAR2 (100),
   staff_freeze      CHAR (1)
)
/
BEGIN
   INSERT INTO departments
        VALUES (10, 'IT', 'Y');
   INSERT INTO departments
        VALUES (20, 'HR', 'N');
   COMMIT;
END;
/
CREATE TABLE employees
(
   employee_id     INTEGER,
   department_id   INTEGER,
   last_name       VARCHAR2 (100)
)
/
BEGIN
   DELETE FROM employees;
   INSERT INTO employees
        VALUES (100, 10, 'Price');
   INSERT INTO employees
        VALUES (101, 20, 'Sam');
   INSERT INTO employees
        VALUES (102, 20, 'Joseph');
   INSERT INTO employees
        VALUES (103, 20, 'Smith');
   COMMIT;
END;
/

And suppose that the SCOTT schema contains only an employees table, defined and populated with data as follows:

CREATE TABLE employees
(
   employee_id     INTEGER,
   department_id   INTEGER,
   last_name       VARCHAR2 (100)
)
/
BEGIN
   DELETE FROM employees;
   INSERT INTO employees
        VALUES (100, 10, 'Price');
   INSERT INTO employees
        VALUES (104, 20, 'Lakshmi');
   INSERT INTO employees
        VALUES (105, 20, 'Silva');
   INSERT INTO employees
        VALUES (106, 20, 'Ling');
   COMMIT;
END;
/

HR also contains a procedure that removes all employees from the specified department as long as the department does not have its staff “frozen.” I will first create this procedure as a definer’s rights unit, as shown in Listing 2.

Code Listing 2: Definer’s rights procedure that removes employee records

CREATE OR REPLACE PROCEDURE remove_emps_in_dept (
   department_id_in IN employees.department_id%TYPE)
   AUTHID DEFINER
IS
   l_freeze   departments.staff_freeze%TYPE;
BEGIN
   SELECT staff_freeze
     INTO l_freeze
     FROM HR.departments
    WHERE department_id = department_id_in;
   IF l_freeze = ‘N’
   THEN
      DELETE FROM employees
            WHERE department_id = department_id_in;
   END IF;
END;
/

And SCOTT can execute this procedure:

GRANT EXECUTE
   ON remove_emps_in_dept
   TO SCOTT
/

When SCOTT executes the procedure as shown below, it will remove three rows—but from HR’s employees table, because the procedure is a definer’s rights unit.

BEGIN
   HR.remove_emps_in_dept (20);
END;
/

I need to change this procedure so that it will remove rows from SCOTT’s employees table, not HR’s. That is precisely what invoker’s rights do. But if I change the AUTHID clause of this procedure to

AUTHID CURRENT_USER

and run the procedure again, I see this:

BEGIN
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "HR.REMOVE_EMPS_IN_DEPT", line 7
ORA-06512: at line 2

The problem is that Oracle Database is now using the privileges of SCOTT to resolve the references to two tables: HR.departments and SCOTT.employees. SCOTT has no privileges on HR’s departments table, however, so Oracle Database raises the ORA-00942 error.

Prior to Oracle Database 12c, the DBA would have had to grant the necessary privileges on HR.departments to SCOTT. Now, however, DBAs can take the following steps instead:

CREATE ROLE hr_departments
/
GRANT hr_departments TO hr
/

Connected to HR, grant the desired privilege to the role and then grant the role to the procedure:

GRANT SELECT
   ON departments
   TO hr_departments
/
GRANT hr_departments TO PROCEDURE remove_emps_in_dept
/

And now when I execute the following statements from SCOTT, the rows are removed from the SCOTT.employees table:

SELECT COUNT (*)
  FROM employees
 WHERE department_id = 20
/
  COUNT(*)
—————————————
         3
BEGIN
   hr.remove_emps_in_dept (20);
END;
/
SELECT COUNT (*)
  FROM employees
 WHERE department_id = 20
/
  COUNT(*)
—————————————
         0

Roles granted to a program unit do not affect compilation. Instead, they affect the privilege checking of SQL statements the unit issues at runtime. Thus, the procedure or function executes with the privileges of both its own roles and any other currently enabled roles.

This feature will be of most use with invoker rights program units. You will likely consider granting roles to a definer’s rights unit when that unit executes dynamic SQL, because the privileges for that dynamic statement are checked at runtime.

Oracle Database 12c offers significant improvements in flexibility and functionality when it comes to defining and executing program units. Oracle Database 12c features enable PL/SQL developers to use invoker rights with the function result cache, define and execute PL/SQL subprograms in SQL statements, restrict access to program units by way of a whitelist, and grant roles to program units.

Oracle Database 12c also enhances SQL execution in PL/SQL program units in a variety of ways, which I will cover in the next issue of Oracle Magazine.

CREATE TABLE plch_accounts
(
   account_name     VARCHAR2 (100),
   account_status   VARCHAR2 (6)
)
/
BEGIN
   INSERT INTO plch_accounts
        VALUES (‘ACME WIDGETS’, ‘ACTIVE’);
   INSERT INTO plch_accounts
        VALUES (‘BEST SHOES’, ‘CLOSED’);
   COMMIT;
END;
/

CREATE OR REPLACE PACKAGE plch_constants
IS
   active   CONSTANT VARCHAR2 (6) := ‘ACTIVE’ ;
   closed   CONSTANT VARCHAR2 (6) := ‘CLOSED’ ;
END;
/
SELECT account_name
  FROM plch_accounts
 WHERE account_status = plch_constants.active
/

CREATE OR REPLACE PACKAGE plch_constants
IS
   FUNCTION active
      RETURN VARCHAR2;
   FUNCTION closed
      RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY plch_constants
IS
   FUNCTION active
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN ‘ACTIVE’;
   END;
   FUNCTION closed
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN ‘CLOSED’;
   END;
END;
/
SELECT account_name
  FROM plch_accounts
 WHERE account_status = plch_constants.active
/

CREATE OR REPLACE PACKAGE plch_constants
IS
   active   CONSTANT VARCHAR2 (6) := ‘ACTIVE’ ;
   closed   CONSTANT VARCHAR2 (6) := ‘CLOSED’ ;
END;
/
WITH
   FUNCTION active
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN plch_constants.active;
   END;
SELECT account_name
  FROM plch_accounts
 WHERE account_status = active
/

DOWNLOAD Oracle Database 12c

TEST your PL/SQL knowledge

READ PL/SQL 101, Parts 1-12

READ more about
Oracle Database 12c
PL/SQL