(11) Subprograms: Stored Functions

[ Oracle PL/SQL ]

About Functions:

• Although a function can return more than one value, it is not a good programming practice to do so.
• Functions can accept parameters in three modes: IN, OUT, and IN OUT.
• It IS NOT good programming practice to build functions with parameter modes other than IN because functions always return a value.
• Functions having compilation errors will have an invalid status, and these functions cannot be invoked.
• Host or bind variables CANNOT be referenced in the PL/SQL block of the function.
• There can be more than one RETURN statement in the executable section of the function, but the function stops executing when it meets the first RETURN statement.

• HEADING: must have a RETURN clause.
• (A PROCEDURE SHOULD NOT have a RETURN clause).
• A function heading can include these options:
• DETERMINISTIC - tells optimizer to avoid redundant invocations. Asserts that EVERY call to the functions with the same values will lead to the same result.
• PARALLEL_ENABLE - Make the function safe for use in slave sessions of parallel DML Evaluations.
• PIPELINED -

• EXECUTABLE section:
• EVERY execution path MUST lead to a RETURN statement. (or PL/SQL issue a compile-time warning:.
• "PLW-05005: subprogram F returns without value at line n."

• RETURN statement: Is optional and NOT RECOMMENDED in procedures.
• Procedure: the RETURN statement returns control to the invoker, where execution resumes immediately after the invocation. The RETURN statement cannot specify an expression.
• Anonymous block: the RETURN statement exits its own block and all enclosing blocks. The RETURN statement cannot specify an expression.

set serveroutput on 
BEGIN
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Inside inner block.');
    RETURN;
    DBMS_OUTPUT.PUT_LINE('Unreachable statement.');
  END;
  DBMS_OUTPUT.PUT_LINE('Inside outer block. Unreachable statement.');
END;

Invoking functions from SQL statements

1. Function should be stored in the database.
2. Function should have ONLY parameters of the IN mode.
3. Return DATA TYPE should be compatible with SQL datatypes.
• (i.e., BOOLEAN is NOT an SQL type, so a function returning BOOLEAN cannot be invoked from a SQL statement.)
• (i.e., You cannot use PL/SQL data types, such as BOOLEAN, RECORD, or TABLE for returning values.)
4. Must use POSITIONAL notation to pass values to the IN parameters.
5. Function that MODIFY any DB Table CANNOT be used in SELECT statemnts
• (attempts on the contrary will raise: "ORA-14551: cannot perform a DML operation inside a query")
• the restriction on a function that it cannot issue any INSERT, UPDATE, and DELETE statements, when called from within a SELECT statement, will not stop you from creating a packaged function that violates this rule. The violation will be detected at run time.
6. Functions used on DELETE or UPDATE cannot query or modify any database tables modified by that statements.
• (attempts otherwise will lead to a MUTATING TABLE error)
• (i.e. ORA-04091: table is mutating, trigger/function may not see it)
7. Functions called from SQL statements CANNOT use:
• TCL: transaction control language, such as COMMIT
• SessionCL: session control language, such as SET ROLE.
• SysCL: System control language, such as ALTER SYSTEM, and
• DDL statements, such as CREATE.

Using Pragma RESTRICT_REFERENCES

• PRAGMA RESTRICT_REFERENCES allow a packaged function to be used in a GROUP BY clause of a SELECT statement.
• i.e. pragma restrict_references (check_emp, wnds, wnps, rnps, rnds);
• The PRAGMA RESTRICT_REFERENCES indicates to the compiler that the function:
• Writes No Database State (WNDS) – The function cannot issue any INSERT, UPDATE, or DELETE.
• Writes No Package State (WNPS) – The function does not make any changes to the packaged variables.
• Reads No Package State (RNPS) – The function reads no package constructs.
• Reads No Database State (RNDS) – The function issues no SELECT statements.

Using pragma RESTRICT_REFERENCES

SQL> create table empsm (name varchar2(20), salary number);

SQL> create or replace package test_restrict_ref 
is
 function check_emp return number;
 pragma restrict_references (check_emp, wnds, wnps, rnps, rnds);
end test_restrict_ref;
/
PACKAGE BODY test_restrict_ref compiled

SQL> create or replace package body test_restrict_ref
is 
  function check_emp
    return number is
   sum_sal number := 0; 
  begin
   select avg(salary) into sum_sal   -- violates rdns
   from employees;
   insert into empsm                 -- violates wdns
   values ('New emp', sum_sal);
   return sum_sal;
  exception
    when no_data_found then
       sum_sal := 0;
       return sum_sal;
  end check_emp;
end;
/
PACKAGE BODY test_restrict_ref compiled
Warning: execution completed with warning


show errors;
3/3            PLS-00452: Subprogram 'CHECK_EMP' violates its associated pragma

Using the RESULT_CACHE option RESULT_CACHE [OPTION (declaration) | CLAUSE (definition)] Stores function results in the PL/SQL function result cache

PL/SQL Function Result Cache

• Is a language-supported and system-managed way to cache the results of PL/SQL functions in a shared global area (SGA).
• To enable result-caching for a function, use the RESULT_CACHE clause.
• Each time a result-cached PL/SQL function is invoked with different parameter values, those parameters and their result are stored in the cache.
• If the system needs more memory, it ages out (deletes) one or more cached results.
• If changes to any of the data sources (tables and views) queried by the function are committed, the cached result becomes invalid and must be recomputed.
• Can save significant space and time.
• The best candidates for result-caching are functions that are invoked frequently but depend on information that changes infrequently or never.

RESULT_CACHE hint (query)

RESULT_CACHE hint instructs the database to cache the results of the current query or query fragment in memory and then to use the cached results in future executions of the query or query fragment. The hint is recognized in the top-level query, the subquery_factoring_clause, or FROM clause inline view. The cached results reside in the result cache memory portion of the shared pool. A cached result is automatically invalidated whenever a database object used in its creation is successfully modified. This hint takes precedence over settings of the RESULT_CACHE_MODE initialization parameter. The query is eligible for result caching only if all functions entailed in the query—for example, built-in or user-defined functions or virtual columns—are deterministic. If the query is executed from OCI client and OCI client result cache is enabled, then RESULT_CACHE hint enables client caching for the current query.

Using the RESULT_CACHE option/clause The clause needs to be included in the Function declaration

SQL> CREATE OR REPLACE PACKAGE department_pkg IS
 type dept_info_rec is RECORD (
   dept_name departments.department_name%TYPE,
   mgr_name  employees.last_name%type,
   dept_size pls_integer);
 
 -- function declaration
 FUNCTION get_dept_info (dept_id pls_integer)
   RETURN dept_info_rec
   RESULT_CACHE;                   -- result_cache option declared.
END department_pkg;
/
PACKAGE department_pkg compiled.


-- Because get_dept_info depends on the DEPARTMENTS and EMPLOYEES tables, 
-- ANY COMMITTED change to DEPARTMENTS or EMPLOYEES invalidates 
-- all cached results for get_dept_info
SQL> CREATE OR REPLACE PACKAGE BODY department_pkg IS
  -- function definition.
 FUNCTION get_dept_info (dept_id pls_integer)
   return dept_info_rec
   result_cache RELIES_ON (departments, employees) -- identify dependencies
 IS
   rec dept_info_rec; 
 BEGIN
   SELECT department_name into rec.dept_name
   from departments
   where department_id = dept_id;
   
   select e.last_name INTO rec.mgr_name
   from departments d, employees e
   where d.department_id = dept_id
   and d.manager_id = e.employee_id;
   
   select count(*) INTO rec.dept_size
   from employees
   where department_id = dept_id;
   
   return rec;
 END get_dept_info;
END department_pkg;

set serveroutput on
declare
  rec department_pkg.dept_info_rec;
begin
  rec := department_pkg.get_dept_info(10);
  dbms_output.put_line('Department:    '|| rec.dept_name);
  dbms_output.put_line('Manager:       '|| rec.mgr_name);
  dbms_output.put_line('Num Employees: '|| rec.dept_size);
end;
/
anonymous block completed
Department:    Administration
Manager:       Whalen
Num Employees: 1

Restrictions to result-cache functions

• Cannot be defined in a module that has invoker's rights.
• Cannot be defined in an anonymous block
• Cannot be pipelined
• Does not references dictionary tables, temp tables, sequences or nondeterministic SQL functions.
• Has no OUT or IN OUT parameters
• No IN parameter of type: BLOB, CLOB, NCLOB, REF CURSOR, Collection, Object, Record
• RETURN type cannot be: BLOB, CLOB, NCLOB, REF CURSOR, record or collection with an unsupported return type.

Recommended for result-cached functions:

• Should not have side effects.
• Should not depend on session-specific settings
• should not depend on session-specific application contexts

when does a subprogram has side effects?

If it changes ANYTHING except the values of its own local variables. i.e.

• changes its own OUT or IN OUT parameter
• changes a global variable
• changes a public variable in a package
• changes a database table
• changes the database
• changes the external state (use dbms_output or send email).

What kinds of PL/SQL FUnctions can be called by SQL Statements?

The function must obey these PURITY RULES (control side effects):

1. Function is invoked from SELECT or parallelized Insert/Update/Delete (I/U/D): Subprogram CANNOT modify any DB tables.
2. Invoked from I/U/D: subprogram cannot query or modify any DB tables modified by the statement. (ORA-04091 - Mutating Table)
3. Invoked from SELECT I/U/D: Subprogram CANNOT execute any of these statement types: (except if uses PRAGMA Autonomous_transaction)
• Transaction Control (i.e. COMMIT)
• Session Control (i.e. SET ROLE, ALTER SESSION)
• System control (i.e. ALTER SYSTEM)
• DDL (i.e. CREATE), which are commited automatically.

Result Cache Bypass

• Ensures that the user of each session sees his or hers own uncommitted changes
  Put the result cache in bypass mode.
  

BEGIN 
 DBMS_RESULT_CACHE.Bypass(TRUE);
END;

Result Cache management:

• Initialization Parameters
• RESULT_CACHE_MAX_SIZE: Max amount of SGA (bytes)
• RESULT_CACHE_MAX_RESULT: Max % of the result caceh any single result can use.
• Package:
• DBMS_RESULT_CACHE: provides interface allowing DBA to administer the RESULT_CACHE in the SGA.
• Dynamic performance views:
• [G]V$RESULT_CACHE_STATISTICS
• [G]V$RESULT_CACHE_MEMORY
• [G]V$RESULT_CACHE_OBJECTS
• [G]V$RESULT_CACHE_DEPENDENCY

How Result-cached functions handle session-specific application contexts?

• Application Context: Either GLOBAL or session-specific.
• Is a set of attributes and their values.
• When does a Pl/sql function depends on session-specific context?
1. when it directly invokes SQL function SYS_CONTEXT
2. Indirectly invokes SYS_CONTEXT by using Virtual Private Database mechanisms for fine-grained security.
• If you must cache the results of a function that depends on session-specific application contexts, you must pass the application context to the function as a parameter.

Using Application Context and the Result Cache Assume that config_tab table has a VPD policy that translates query A into query B below::

SELECT value from config_tab where name = param_name;    -- Query A

SELECT value from config_tab                             -- Query B
where name = param_name
And app_id = sys_context('Cofig', 'App_ID');  --VPD policy adds to the WHERE clause

• To use the RESULT CACHE, the application context needs to be passed as parameter to the function:

create or replace function get_param_value (
 param_name varchar2,
 appctx     varchar2 default sys_context('Config', 'App_ID')) -- pass app context as parameter
 RETURN VARCHAR
 RESULT_CACHE
IS
 rec varchar2(2000);
BEGIN
 select val into rec
 from config_tab
 where name = param_name;
 RETURN rec;
END;

Execution Right: Invoker's vs. Definer's

• A program unit can execute within the permissions of the user that created the program (definer's right) or within the permissions of the user that invokes the program (invoker's right).
• To set execution right: AUTHID = { CURRENT_USER | DEFINER (default)}
• DBA_PROCEDURES: Check AUTHID values.

• You can define AUTHID for: CREATE FUNCTION, CREATE PACKAGE, CREATE PROCEDURE, CREATE TYPE, and ALTER TYPE.
• Procedures and Functions take the definer's right by default.
• An anonymous block always behaves like an Invoker's Right (CURRENT_USER) unit.
• A trigger or view always behaves like a Definer's Right (DEFINER) unit.
• You can create a database link to use invoker's rights:
CREATE DATABASE LINK link_name CONNECT TO CURRENT_USER USING connect_string;
• A current-user link lets you connect to a remote database as another user, with that user's privileges.