PL/SQL EnhancementsBy Steven Feuerstein
Oracle Database 12c enhances the PL/SQL function result cache, improves PL/SQL execution in SQL, adds a whitelist, and fine-tunes privileges.
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:
Invoker Rights and the PL/SQL Function Result Cache
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; /
PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms in Invoker-Rights modules
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; /
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.
Define PL/SQL Subprograms in a SQL Statement
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;
SELECT betwnstr (last_name, 3, 5) FROM employees
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
Reference a Packaged Constant
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
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
WITH FUNCTION year_number RETURN INTEGER IS BEGIN RETURN pkg.year_number; END; SELECT year_number FROM employees WHERE employee_id = 138
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.
Whitelists and the ACCESSIBLE BY Clause
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; /
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; /
BEGIN public_pkg.do_only_this; END; / THIS THAT
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
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
Grant Roles to Program Units
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; /
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; /
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 /
BEGIN HR.remove_emps_in_dept (20); END; /
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
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 /
GRANT SELECT ON departments TO hr_departments / GRANT hr_departments TO PROCEDURE remove_emps_in_dept /
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
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.
Up Next: PL/SQL Enhancements for Executing SQL
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.