CONNECT hr/hr@oracle11 DROP PACKAGE emplu11g / DROP TABLE employees_for_11g / CREATE TABLE employees_for_11g AS SELECT * FROM employees / CREATE OR REPLACE PACKAGE emplu11g IS TYPE last_names_aat IS TABLE OF employees_for_11g.last_name%TYPE INDEX BY pls_INTEGER; TYPE employees_aat IS TABLE OF employees_for_11g%ROWTYPE INDEX BY pls_INTEGER; FUNCTION last_name (employee_id_in IN employees_for_11g.employee_id%TYPE) RETURN employees_for_11g.last_name%TYPE result_cache ; FUNCTION last_names (department_id_in IN employees_for_11g.department_id%TYPE) RETURN last_names_aat result_cache ; FUNCTION employees_in_dept (department_id_in IN employees_for_11g.department_id%TYPE) RETURN employees_aat result_cache ; FUNCTION restrict_employees_for_11g (schema_in VARCHAR2, NAME_IN VARCHAR2) RETURN VARCHAR2; END; / CREATE OR REPLACE PACKAGE BODY emplu11g IS FUNCTION last_name (employee_id_in IN employees_for_11g.employee_id%TYPE) RETURN employees_for_11g.last_name%TYPE result_cache relies_on (employees_for_11g) IS onerow_rec employees_for_11g%ROWTYPE; BEGIN DBMS_OUTPUT.PUT_LINE ( 'Looking up last name for employee ID ' || employee_id_in ); SELECT * INTO onerow_rec FROM employees_for_11g WHERE employee_id = employee_id_in; RETURN onerow_rec.last_name; END; FUNCTION last_names (department_id_in IN employees_for_11g.department_id%TYPE) RETURN last_names_aat result_cache relies_on (employees_for_11g) IS l_names last_names_aat; BEGIN DBMS_OUTPUT.PUT_LINE ( 'Getting all last names for department ID ' || department_id_in ); SELECT last_name BULK COLLECT INTO l_names FROM employees_for_11g WHERE department_id = department_id_in; RETURN l_names; END; FUNCTION employees_in_dept (department_id_in IN employees_for_11g.department_id%TYPE) RETURN employees_aat result_cache relies_on (employees_for_11g) IS l_employees employees_aat; BEGIN DBMS_OUTPUT.PUT_LINE ( 'Getting all rows for department ID ' || department_id_in ); SELECT * BULK COLLECT INTO l_employees FROM employees_for_11g WHERE department_id = department_id_in; RETURN l_employees; END; FUNCTION restrict_employees_for_11g (schema_in VARCHAR2, NAME_IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN (CASE USER WHEN 'HR' THEN '1 = 1' ELSE '1 = 2' END ); END restrict_employees_for_11g; END; / /* Can I cache an entire collection of strings? Yes! */ DECLARE l_names emplu11g.last_names_aat; BEGIN l_names := emplu11g.last_names (50); DBMS_OUTPUT.put_line ('1. Count of names in department 50 = ' || l_names.COUNT); l_names := emplu11g.last_names (50); l_names := emplu11g.last_names (60); DBMS_OUTPUT.put_line ('2. Count of names in department 60 = ' || l_names.COUNT); END; / /* Can I cache an entire collection of records? Yes! */ DECLARE l_employees emplu11g.employees_aat; BEGIN l_employees := emplu11g.employees_in_dept (50); DBMS_OUTPUT.put_line ('1. Count of names in department 50 = ' || l_employees.COUNT); l_employees := emplu11g.employees_in_dept (50); l_employees := emplu11g.employees_in_dept (60); DBMS_OUTPUT.put_line ('2. Count of names in department 60 = ' || l_employees.COUNT); END; / /* Will the cache show "dirty" data in the same session? NO! */ SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.put_line ('BEFORE update 1: ' || emplu11g.last_name (198)); DBMS_OUTPUT.put_line ('BEFORE update 2: ' || emplu11g.last_name (198)); DBMS_OUTPUT.PUT_LINE ( '-' ); UPDATE employees_for_11g SET last_name = 'Uh-oh' WHERE employee_id = 198; /* Shows me the new value and caching is disabled. */ DBMS_OUTPUT.put_line ('AFTER update 1: ' || emplu11g.last_name (198)); DBMS_OUTPUT.put_line ('AFTER update 2: ' || emplu11g.last_name (198)); DBMS_OUTPUT.PUT_LINE ( '-' ); /* After commit, will it start caching again? Yes! */ COMMIT; DBMS_OUTPUT.put_line ('AFTER commit 1: ' || emplu11g.last_name (198)); DBMS_OUTPUT.put_line ('AFTER commit 2: ' || emplu11g.last_name (198)); DBMS_OUTPUT.PUT_LINE ( '-' ); /* Reset last name */ UPDATE employees_for_11g SET last_name = 'OConnell' WHERE employee_id = 198; COMMIT; END; / /* Let's examine the impact of fine-grained access or virtual private database on the result cache. */ GRANT SELECT ON employees_for_11g TO scott / GRANT EXECUTE ON emplu11g TO scott / BEGIN BEGIN DBMS_RLS.drop_policy ('HR', 'employees_for_11g', 'rls_and_rc'); EXCEPTION WHEN OTHERS THEN IF SQLCODE = -28102 THEN NULL; ELSE RAISE; END IF; END; DBMS_RLS.add_policy (object_schema => 'HR' , object_name => 'employees_for_11g' , policy_name => 'rls_and_rc' , function_schema => 'HR' , policy_function => 'emplu11g.restrict_employees_for_11g' , statement_types => 'SELECT,UPDATE,DELETE,INSERT' , update_check => TRUE ); END; / BEGIN /* Should see OConnell */ DBMS_OUTPUT.put_line (emplu11g.last_name (198 /* Department 50 */)); END; / CONNECT scott/tiger@oracle11 SET SERVEROUTPUT ON BEGIN /* Should raise NO_DATA_FOUND but instead.... */ DBMS_OUTPUT.put_line (hr.emplu11g.last_name (198 /* Department 50 */)); END; / CONNECT hr/hr@oracle11 SET SERVEROUTPUT ON BEGIN /* Flush the cache */ UPDATE employees_for_11g SET last_name = SUBSTR (last_name, 1, 500); COMMIT; END; / CONNECT scott/tiger@oracle11 SET SERVEROUTPUT ON BEGIN /* Should raise NO_DATA_FOUND */ DBMS_OUTPUT.put_line (hr.emplu11g.last_name (198 /* Department 50 */)); END; / /*====================================================================== | Supplement to the fifth edition of Oracle PL/SQL Programming by Steven | Feuerstein with Bill Pribyl, Copyright (c) 1997-2009 O'Reilly Media, Inc. | To submit corrections or find more code samples visit | http://oreilly.com/catalog/9780596514464/ */