PL/SQL enhancements in Oracle 12c

PL/SQL enhancements in Oracle 12c

Oracle 12c enhances a series of methods for defining and executing PL/SQL program units. This article covers several new features of Oracle 12c:

1. optimized the caller permission function for the result cache condition.
2. PL/SQL functions can be defined and executed in SQL statements.
3. Use the accessible by condition to specify a whitelist to restrict access to program units.
4. You can directly authorize a role to a specified program unit.

Caller permission and PL/SQL function result Cache
Oracle 11g introduces PL/SQL function result caching, providing a very powerful, effective, and easy-to-use caching mechanism. The main goal is to ensure that if the last retrieved record does not change, the same results are returned from the cache without executing any SQL statements.
This feature is available to the entire database instance. That is to say, if a user USER_ONE executes a result cache function to obtain the row ID = 100 from the employees table. When another user USER_TWO executes the row with the same function request ID = 100, the result will be returned directly from the cache without executing a SELECT statement.
If you have never used this feature, I strongly recommend that you study it and use it-be sure to coordinate with DBA to set a reasonable result cache.
Even in Oracle 11.2, you still cannot use it with the caller permission (AUTHID CURRENT_USER) and function result cache (RESULT_CACHE. This attempt will only result in compilation failure:
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
Rochelle return employees. last_name % TYPE;
BEGIN
SELECT last_name
INTO l_return
FROM employees
WHERE employee_id = employee_id_in;

RETURN l_return;
END;
/

This compilation error occurs:

PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms in Invoker-Rights modules1
The cause of the failure is the caller permission. During running, the PL/SQL engine uses the current user permission to process related database objects, such as tables and views. However, if the function has the RESULT_CACHE condition, the user USER_ONE executes the function. After the 100 parameter is input, the user USER_TWO calls the same function, the function body will not be executed at all and the related table EMPLOYEES will not be checked based on the USER_TWO permission. This will cause serious security problems!
The good news is that this restriction is temporary. In 12c, We can compile the above last_name function without reporting an error!
Let's take a look at the background. Oracle 12c passes the current user as an implicit parameter. This parameter will be cached together with other input parameters!
This means that the result cache of the caller permission function is based on the current user partition. Therefore, the result cache of the caller permission function improves performance only for repeated calls with the same user parameters. In Oracle 11g, we can use another method to achieve the same effect. Just change the implementation of the last_name function:
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
Rochelle 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
Rochelle 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 description and the result is not cached. Instead, a public function only calls a private function (defined only in the function body). We can see that we have added 2nd more parameters: USER!
So every time I call employee_api.last_name, Oracle will determine whether the user has been cached. Obviously, this method is a bit redundant! In 12c, we only need to consider whether it is worthwhile to add the result cache to the caller permission program!

Define PL/SQL subroutines in SQL statements
Developers have long been able to call their own PL/SQL functions in SQL statements. In this case, I have created a function BETWNSTR which features similar to substr user-defined functions:
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 call it like this:

SELECT betwnstr (last_name, 3, 5)
FROM employees

This method extends the use of the SQL language. The disadvantage is that you need to switch between the SQL and PL/SQL Execution engines!
To Oracle 12c, you can use the WITH clause to define PL/SQL functions and procedures and then call the subquery to return results. This feature enables us to upgrade the BETWNSTR function and query into a 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 do developers want to copy PL/SQL functions to SQL statements? To improve performance. When I call my own PL/SQL functions in an SQL statement, the SQL engine must execute a context that affects performance and switch to the PL/SQL engine. Moving code to an SQL statement means no context switch will occur.
3. Reference a constant in a package
Although you can call functions in a package in SQL, you cannot reference constants in a package (unless you place SQL statements in PL/SQL blocks for execution ). The following example shows the restriction:

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 work und is to define a function in the package to return this constant (enough to fight... (⊙ ﹏ ⊙ ))

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

So much code is added to reference a constant! However, in Oracle 12c, this approach is not necessary. We only need to create a udf in the with clause to return the constants in the package:

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 very useful for SQL languages. However, you should think about this question before using it: Do I need to use it in multiple places in the program?
If necessary, you should weigh the performance improvement brought by with function and the disadvantages of copying and pasting this logic into multiple SQL statements.
4. whitelist and accessible by clause
Most PL/SQL-based applications are composed of many packages, some of which are top-level APIs for developers to call to meet user needs, while others are help packages, it is called only by a specific package.
Before 12c, PL/SQL could not block any program in the user session use package with the package execution permission. Since 12c, in contrast, all PL/SQL program units have an ACCESSIBLE BY clause option to specify which program units can call the program units currently being created and modified.
Let's look at an example. First, I create a public Package Description for other developers to call to create an application.

Create or replace package public_pkg
IS
PROCEDURE do_only_this;
END;
/

Next, I created my "private" package description. Make sure that only the public_pkg public package is allowed. So I added the ACCESSIBLE BY clause.

Create or replace package private_pkg
Accessible by (public_pkg)
IS
PROCEDURE do_this;

PROCEDURE do_that;
END;
/

Now, it is time to implement the package body. Public_pkg.do_only_this process calls the private_pkg subroutine.

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;
/

Now you can run this public package without any problem:

BEGIN
Public_pkg.do_only_this;
END;
/
THIS
THAT

However, if I try to call the sub-process of a private package in an anonymous block, the following error occurs: (Hey! You can't afford it! Hmm, it's interesting !)

BEGIN
Private_pkg.do_this;
END;
/

ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00904: insufficient privilege
Access object PRIVATE_PKG
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

When a program tries to call a subprogram of a private package, the same error will be reported during compilation:

SQL> CREATE OR REPLACE PROCEDURE
Use_private
2 IS
3 BEGIN
4 private_pkg.do_this;
5 END;
6/
Warning: Procedure created
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

After reading this, it is the "PLS" error prompt, which will be caught during compilation. Using this feature will not affect the runtime performance.
5. Authorize the role to the program Unit
Before 12c, a program unit (defined with authid definer or unspecified) with a DEFINER permission is always executed with the permission of the unit owner. A caller permission Program unit (defined by AUTHID CURRENT_USER) is always executed based on the caller's permission.
One result of this setting is that if a program needs to be executed by all users, the program will be set as the definer permission unit. In this way, all the permissions of the definer will be granted to execute program units, which is not very good in terms of security.
From 12c, You can authorize the role to PL/SQL packages and pattern-level processes and functions. Role-based permission-based program units allow developers to allocate corresponding program units to callers in greater detail.
You can now define a program unit with caller permissions, and then assign limited permissions to corresponding roles to supplement the caller permissions.
Let's look at the following example to show how to authorize a role to a program unit. Assume that the HR mode includes the departments and employees tables, and the data definition and filling are as follows:

Create table orders ments
(
Department_id INTEGER,
Department_name VARCHAR2 (100 ),
Staff_freeze CHAR (1)
)
/

BEGIN
Insert into orders ments
VALUES (10, 'it', 'y ');

Insert into orders ments
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;
/

Assuming that the SCOTT mode only contains the employees table, the data definition and filling are 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, 'lastshmi ');

Insert into employees
VALUES (105, 20, 'silva ');

Insert into employees
VALUES (106, 20, 'ling ');
COMMIT;
END;
/

HR also includes a process for removing all employees in a department. I first use the definer permission to create the process as follows:
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
Rochelle freeze parameters. 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;
/

SCOTT can execute this process at this time:

GRANT EXECUTE
ON remove_emps_in_dept
TO SCOTT
/

When SCOTT executes the process in the following way, three rows will be removed from the HR table employees! This is because the user permission unit is used.

BEGIN
HR. remove_emps_in_dept (20 );
END;
/

I need to change this process so that the data in the SCOTT table employees is deleted, not under HR. Change to the caller permission.
AUTHID CURRENT_USER

However, the following error occurs:

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 databases cannot find the table HR. Orders ments in SCOTT mode. SCOTT does not have any permissions on the HR. departments table.
Before 12c, DBAs had to grant necessary permissions to SCOTT. DBAs can now take the following steps:

Create role hr_departments
/

GRANT hr_administrative ments TO hr
/

Connect to HR, authorize the desired permissions to the role, and then authorize the role to the process:

GRANT SELECT
ON orders ments
TO hr_departments
/

GRANT hr_departments to procedure remove_emps_in_dept
/

Go back and execute again. The data is correctly 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

The role authorized to the Program unit does not affect compilation. Instead, they affect the SQL statement permission check during running. Therefore, a process or function runs with its own role and any currently available role permissions.
This feature will be most useful to caller permission program units. You are more likely to consider granting the role permission unit to the definer who executes dynamic SQL, because the permission for dynamic statements is checked at runtime.

Basic Design of PL/SQL programs for Oracle databases

PL/SQL Developer Practical Skills