8 Using PL/SQL Subprograms

Subprogram Parts

A subprogram consists of a specification ("spec") and a body. To declare a subprogram, you must provide the spec, which includes descriptions of any parameters. To define a subprogram, you must provide both the spec and the body. You can either declare a subprogram first and define it later in the same block or subprogram, or you can declare and define it at the same time.

A subprogram body has the parts described in Table 8-1.

Subprogram Types

PL/SQL has two types of subprograms, procedures and functions. Typically, you use a procedure to perform an action and a function to compute and return a value.

A procedure and a function have the same structure, except that only a function has the following items:

For the syntax of subprogram declarations and definitions, including descriptions of the items in the preceding table, see "Procedure Declaration and Definition" and "Function Declaration and Definition". For additional information, see "Using the RETURN Statement" and "Using the Cross-Session PL/SQL Function Result Cache".

Subprogram Calls

A subprogram call has this form:

subprogram_name [ (parameter [, parameter]... ) 

A procedure call is a PL/SQL statement. For example:

raise_salary(emp_id, amount);

A function call is part of an expression. For example:

IF sal_ok(new_sal, new_title) THEN ...

For information about subprogram parameters, see "Passing Parameters to PL/SQL Subprograms".

Subprogram Examples

In Example 8-1, a block declares, defines, and invokes a string-manipulation procedure, double, which accepts input and output parameters and handles potential errors. The procedure double includes the optional exception-handling part.

-- Declarative part of block begins
DECLARE
  in_string  VARCHAR2(100) := 'This is my test string.';
  out_string VARCHAR2(200);
  -- Procedure spec begins
  PROCEDURE double (original IN VARCHAR2,
                    new_string OUT VARCHAR2) AS
  -- Procedure spec ends
  -- Procedure body begins
    -- Executable part begins
    BEGIN
      new_string := original || ' + ' || original;
    -- Executable part ends
    -- Exception-handling part begins
      EXCEPTION
      WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Output buffer not long enough.');
    END;
  -- Exception-handling part ends
  -- Procedure body ends
-- Declarative part of block ends
-- Executable part of block begins
BEGIN
  double(in_string, out_string);
  DBMS_OUTPUT.PUT_LINE(in_string || ' - ' || out_string);
END;
-- Executable part of block ends
/

In Example 8-2, a block declares, defines, and invokes a numeric function, square, which declares a local variable to hold temporary results and returns a value when finished. The function square omits the optional exception-handling part.

-- Declarative part of block begins
DECLARE
  -- Function spec begins
  FUNCTION square (original NUMBER)
    RETURN NUMBER
  AS
    original_squared NUMBER;
  -- Function spec ends
  -- Function body begins
    -- Executable part begins
    BEGIN
      original_squared := original * original;
      RETURN original_squared;
    END;
    -- Executable part ends
  -- Function body ends
-- Declarative part of block ends
-- Executable part of block begins
BEGIN
  DBMS_OUTPUT.PUT_LINE(square(100));
END;
-- Executable part of block ends
/

Formal and Actual Subprogram Parameters

Formal parameters are the variables declared in the subprogram specification and referenced in the subprogram body. Actual parameters are the variables or expressions that you pass to the subprogram when you invoke it. Corresponding formal and actual parameters must have compatible datatypes.

A good programming practice is to use different names for actual and formal parameters.

In Example 8-4, emp_id and amount are formal parameters and emp_num and bonus are the corresponding actual parameters.

DECLARE
  emp_num NUMBER(6) := 120;
  bonus   NUMBER(6) := 100;
  merit   NUMBER(4) := 50;
  PROCEDURE raise_salary (emp_id NUMBER,
                          amount NUMBER) IS
    BEGIN
      UPDATE employees SET salary = 
        salary + amount WHERE employee_id = emp_id;
  END raise_salary;
BEGIN
  -- Procedure call specifies actual parameters
  raise_salary(emp_num, bonus);
  -- Expressions can be used as parameters
  raise_salary(emp_num, merit + bonus);
END;
/

When you invoke a subprogram, PL/SQL evaluates each actual parameter and assigns its value to the corresponding formal parameter. If necessary, PL/SQL implicitly converts the datatype of the actual parameter to the datatype of the corresponding formal parameter before the assignment (this is why corresponding formal and actual parameters must have compatible datatypes). For information about implicit conversion, see "Implicit Conversion".

A good programming practice is to avoid implicit conversion, either by using explicit conversion (explained in "Explicit Conversion") or by declaring the variables that you intend to use as actual parameters with the same datatypes as their corresponding formal parameters. For example, suppose that pkg has this specification:

PACKAGE pkg IS
  PROCEDURE s (n IN PLS_INTEGER);
END pkg;

The following invocation of pkg.s avoids implicit conversion:

DECLARE
  y PLS_INTEGER :=1;
BEGIN
  pkg.s(y);
END;

The following invocation of pkg.s causes implicit conversion:

DECLARE
  y INTEGER :=1;
BEGIN
  pkg.s(y);
END;

i1 IN VARCHAR2 CHARACTER SET ANY_CS
i2 IN VARCHAR2 CHARACTER SET i1%CHARSET

Using Positional, Named, or Mixed Notation for PL/SQL Subprogram Parameters

When invoking a subprogram, you can specify the actual parameters using either positional, named, or mixed notation. Table 8-2 compares these notations.

Example 8-5 shows equivalent subprogram calls using positional, named, and mixed notation.

DECLARE
  emp_num NUMBER(6) := 120;
  bonus   NUMBER(6) := 50;
  PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) IS
    BEGIN
      UPDATE employees SET salary =
        salary + amount WHERE employee_id = emp_id;
  END raise_salary;
BEGIN
  -- Positional notation:
  raise_salary(emp_num, bonus);
  -- Named notation (parameter order is insignificant):
  raise_salary(amount => bonus, emp_id => emp_num);
  raise_salary(emp_id => emp_num, amount => bonus);
  -- Mixed notation:
  raise_salary(emp_num, amount => bonus);
END;
/

CREATE OR REPLACE FUNCTION compute_bonus (emp_id NUMBER, bonus NUMBER)
  RETURN NUMBER
IS
  emp_sal NUMBER;
BEGIN
  SELECT salary INTO emp_sal
    FROM employees
      WHERE employee_id = emp_id;
  RETURN emp_sal + bonus;
END compute_bonus;
/

The following equivalent SELECT statements invoke the PL/SQL subprogram in Example 8-5 using positional, named, and mixed notation:

SELECT compute_bonus(120, 50) FROM DUAL;                   -- positional
SELECT compute_bonus(bonus => 50, emp_id => 120) FROM DUAL; -- named
SELECT compute_bonus(120, bonus => 50) FROM DUAL;           -- mixed

Specifying Subprogram Parameter Modes

Parameter modes define the action of formal parameters. The three parameter modes are IN (the default), OUT, and IN OUT.

Any parameter mode can be used with any subprogram. Avoid using the OUT and IN OUT modes with functions. To have a function return multiple values is poor programming practice. Also, make functions free from side effects, which change the values of variables not local to the subprogram.

Topics:

• Using IN Mode

• Using OUT Mode

• Using IN OUT Mode

• Summary of Subprogram Parameter Modes

DECLARE
  emp_num       NUMBER(6) := 120;
  bonus         NUMBER(6) := 50;
  emp_last_name VARCHAR2(25);
  PROCEDURE raise_salary (emp_id IN NUMBER, amount IN NUMBER, 
                          emp_name OUT VARCHAR2) IS
    BEGIN
      UPDATE employees SET salary =
        salary + amount WHERE employee_id = emp_id;
      SELECT last_name INTO emp_name
        FROM employees
       WHERE employee_id = emp_id;
  END raise_salary;
BEGIN
  raise_salary(emp_num, bonus, emp_last_name);
  DBMS_OUTPUT.PUT_LINE
    ('Salary was updated for: ' || emp_last_name);
END;
/

Using Default Values for Subprogram Parameters

By initializing formal IN parameters to default values, you can pass different numbers of actual parameters to a subprogram, accepting the default values for omitted actual parameters. You can also add new formal parameters without having to change every call to the subprogram.

If an actual parameter is omitted, the default value of its corresponding formal parameter is used.

You cannot skip a formal parameter by omitting its actual parameter. To omit the first parameter and specify the second, use named notation (see "Using Positional, Named, or Mixed Notation for PL/SQL Subprogram Parameters" on ).

You cannot assign NULL to an uninitialized formal parameter by omitting its actual parameter. You must either assign NULL as a default value or pass NULL explicitly.

Example 8-7 illustrates the use of default values for subprogram parameters.

DECLARE
  emp_num NUMBER(6) := 120;
  bonus   NUMBER(6);
  merit   NUMBER(4);
  PROCEDURE raise_salary (emp_id IN NUMBER,
    amount IN NUMBER DEFAULT 100,
                          extra IN NUMBER DEFAULT 50) IS
    BEGIN
      UPDATE employees SET salary = salary + amount + extra 
        WHERE employee_id = emp_id;
  END raise_salary;
BEGIN
  -- Same as raise_salary(120, 100, 50)
  raise_salary(120);
  -- Same as raise_salary(120, 100, 25)
  raise_salary(emp_num, extra => 25);
END;
/

If the default value of a formal parameter is an expression, and you provide a corresponding actual parameter when you invoke the subprogram, the expression is not evaluated (see Example 8-8).

DECLARE
  cnt pls_integer := 0;
  FUNCTION dflt RETURN pls_integer IS
  BEGIN
    cnt := cnt + 1;
    RETURN 42;
  END dflt;
  -- Default is expression
  PROCEDURE p(i IN pls_integer DEFAULT dflt()) IS
     BEGIN
       DBMS_Output.Put_Line(i);
  END p;
BEGIN
  FOR j IN 1..5 LOOP
    p(j);  -- Actual parameter is provided
  END loop;
  DBMS_Output.Put_Line('cnt: '||cnt);
  p();  -- Actual parameter is not provided
  DBMS_Output.Put_Line('cnt: '||cnt);
END;

The output of Example 8-8 is:

1
2
3
4
5
Cnt: 0
42
Cnt: 1

Guidelines for Overloading with Numeric Types

You can overload subprograms if their formal parameters differ only in numeric datatype. This technique is useful in writing mathematical application programming interfaces (APIs), because several versions of a function can use the same name, and each can accept a different numeric type. For example, a function that accepts BINARY_FLOAT might be faster, while a function that accepts BINARY_DOUBLE might provide more precision.

To avoid problems or unexpected results passing parameters to such overloaded subprograms:

• Ensure that the expected version of a subprogram is invoked for each set of expected parameters. For example, if you have overloaded functions that accept BINARY_FLOAT and BINARY_DOUBLE, which is invoked if you pass a VARCHAR2 literal such as '5.0'?

• Qualify numeric literals and use conversion functions to make clear what the intended parameter types are. For example, use literals such as 5.0f (for BINARY_FLOAT), 5.0d (for BINARY_DOUBLE), or conversion functions such as TO_BINARY_FLOAT, TO_BINARY_DOUBLE, and TO_NUMBER.

PL/SQL looks for matching numeric parameters in this order:

1. PLS_INTEGER (or BINARY_INTEGER, an identical datatype)

2. NUMBER

3. BINARY_FLOAT

4. BINARY_DOUBLE

A VARCHAR2 value can match a NUMBER, BINARY_FLOAT, or BINARY_DOUBLE parameter.

PL/SQL uses the first overloaded subprogram that matches the supplied parameters. For example, the SQRT function takes a single parameter. There are overloaded versions that accept a NUMBER, a BINARY_FLOAT, or a BINARY_DOUBLE parameter. If you pass a PLS_INTEGER parameter, the first matching overload is the one with a NUMBER parameter.

The SQRT function that takes a NUMBER parameter is likely to be slowest. To use a faster version, use the TO_BINARY_FLOAT or TO_BINARY_DOUBLE function to convert the parameter to another datatype before passing it to the SQRT function.

If PL/SQL must convert a parameter to another datatype, it first tries to convert it to a higher datatype. For example:

• The ATAN2 function takes two parameters of the same type. If you pass parameters of different types—for example, one PLS_INTEGER and one BINARY_FLOAT—PL/SQL tries to find a match where both parameters use the higher type. In this case, that is the version of ATAN2 that takes two BINARY_FLOAT parameters; the PLS_INTEGER parameter is converted upwards.

• A function takes two parameters of different types. One overloaded version takes a PLS_INTEGER and a BINARY_FLOAT parameter. Another overloaded version takes a NUMBER and a BINARY_DOUBLE parameter. If you invoke this function and pass two NUMBER parameters, PL/SQL first finds the overloaded version where the second parameter is BINARY_FLOAT. Because this parameter is a closer match than the BINARY_DOUBLE parameter in the other overload, PL/SQL then looks downward and converts the first NUMBER parameter to PLS_INTEGER.

Restrictions on Overloading

You cannot overload the following subprograms:

• Standalone subprograms

• Subprograms whose formal parameters differ only in mode; for example:

  PACKAGE pkg IS
    PROCEDURE s (p IN  VARCHAR2);
    PROCEDURE s (p OUT VARCHAR2);
  END pkg;
  

• Subprograms whose formal parameters differ only in subtype; for example:

  PACKAGE pkg IS
    PROCEDURE s (p INTEGER);
    PROCEDURE s (p REAL);
  END pkg;
  

  INTEGER and REAL are subtypes of NUMBER, so they belong to the same datatype family.

• Functions that differ only in return value datatype, even if the datatypes are in different families; for example:

  PACKAGE pkg IS
    FUNCTION f (p INTEGER) RETURN BOOLEAN;
    FUNCTION f (p INTEGER) RETURN INTEGER;
  END pkg;
  

When Compiler Catches Overloading Errors

The PL/SQL compiler catches overloading errors as soon as it can determine that it will be unable to tell which subprogram was invoked. When subprograms have identical headings, the compiler catches the overloading error when you try to compile the subprograms themselves (if they are local) or when you try to compile the package specification that declares them (if they are packaged); otherwise, it catches the error when you try to compile an ambiguous invocation of a subprogram.

When you try to compile the package specification in Example 8-10, which declares subprograms with identical headings, you get compile-time error PLS-00305.

PACKAGE pkg1 IS
  PROCEDURE s (p VARCHAR2);
  PROCEDURE s (p VARCHAR2);
END pkg1;

Although the package specification in Example 8-11 violates the rule that you cannot overload subprograms whose formal parameters differ only in subtype, you can compile it without error.

PACKAGE pkg2 IS
  SUBTYPE t1 IS VARCHAR2(10);
  SUBTYPE t2 IS VARCHAR2(10);
  PROCEDURE s (p t1);
  PROCEDURE s (p t2);
END pkg2;

However, when you try to compile an invocation of pkg2.s, such as the one in Example 8-12, you get compile-time error PLS-00307.

PROCEDURE p IS
  a pkg.t1 := 'a';
BEGIN
  pkg.s(a)  -- Causes compile-time error PLS-00307;
END p;

Suppose that you correct the overloading violation in Example 8-11 by giving the formal parameters of the overloaded subprograms different names, as follows:

PACKAGE pkg2 IS
  SUBTYPE t1 IS VARCHAR2(10);
  SUBTYPE t2 IS VARCHAR2(10);
  PROCEDURE s (p1 t1);
  PROCEDURE s (p2 t2);
END pkg2;

Now you can compile an invocation of pkg2.s without error if you specify the actual parameter with named notation. For example:

PROCEDURE p IS
  a pkg.t1 := 'a';
BEGIN
  pkg.s(p1=>a);  -- Compiles without error
END p;

If you specify the actual parameter with positional notation, as in Example 8-12, you still get compile-time error PLS-00307.

The package specification in Example 8-13 violates no overloading rules and compiles without error. However, you can still get compile-time error PLS-00307 when invoking its overloaded procedure, as in the second invocation in Example 8-14.

PACKAGE pkg3 IS
  PROCEDURE s (p1 VARCHAR2);
  PROCEDURE s (p1 VARCHAR2, p2 VARCHAR2 := 'p2');
END pkg3;

PROCEDURE p IS
  a1 VARCHAR2(10) := 'a1';
  a2 VARCHAR2(10) := 'a2';
BEGIN
  pkg.s(p1=>a1, p2=>a2);  -- Compiles without error
  pkg.s(p1=>a1);          -- Causes compile-time error PLS-00307
END p;

Advantages of Invoker's Rights

Invoker's-rights (IR) subprograms let you reuse code and centralize application logic. They are especially useful in applications that store data using identical tables in different schemas. All the schemas in one instance can invoke procedures owned by a central schema. You can even have schemas in different instances invoke centralized procedures using a database link.

Consider a company that uses a stored procedure to analyze sales. If the company has several schemas, each with a similar SALES table, it usually also needs several copies of the stored procedure, one in each schema.

To solve the problem, the company installs an IR version of the stored procedure in a central schema. Now, all the other schemas can invoke the same procedure, which queries the appropriate to SALES table in each case.

You can restrict access to sensitive data by invoking, from an IR subprogram, a DR subprogram that queries or updates the table containing the sensitive data. Although multiple users can invoke the IR subprogram, they do not have direct access to the sensitive data.

Specifying Subprogram Privileges (AUTHID Clause)

To implement invoker's rights, create the subprogram with the AUTHID CURRENT_USER clause, which specifies that the subprogram executes with the privileges of its current user. (The default is AUTHID DEFINER, which specifies that the subprogram executes with the privileges of its owner.) The AUTHID clause also specifies whether external references (that is, references to objects outside the subprogram) are resolved in the schema of the owner or the current user.

The AUTHID clause is allowed only in the header of a standalone subprogram, a package spec, or an object type spec. In the CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE, or CREATE TYPE statement, you can include either AUTHID CURRENT_USER or AUTHID DEFINER immediately before the IS or AS keyword that begins the declaration section.

In a package or object type, the AUTHID clause applies to all subprograms.

Most supplied PL/SQL packages (such as DBMS_LOB, DBMS_PIPE, DBMS_ROWID, DBMS_SQL, and UTL_REF) are IR packages.

Who Is the Current User During Subprogram Execution?

Initially, the current user is the session user. When an IR subprogram is invoked, the current user does not change. When a DR subprogram is invoked, the owner of that subprogram becomes the current user. Note that when an IR subprogram is invoked from a DR subprogram, the current user is the owner of the DR subprogram, not the session user.

To verify who the current user is at any time, you can check the static data dictionary view USER_USERS. Inside an IR subprogram, the value from this view might be different from the value of the USER built-in function, which always returns the name of the session user. In addition, the current user setting can be checked by invoking the built-in function SYS_CONTEXT('USERENV','CURRENT_USER').

How External References Are Resolved in IR Subprograms

External references in an IR subprogram are resolved as follows:

• In the following statements, external references are resolved in the schema of the current user, whose privileges are checked at run time:

  • Data manipulation statements SELECT, INSERT, UPDATE, and DELETE

  • Transaction control statement LOCK TABLE

  • Cursor-control statements OPEN and OPEN-FOR

  • Dynamic SQL statements EXECUTE IMMEDIATE and OPEN-FOR-USING

  • SQL statements parsed with DBMS_SQL.PARSE procedure

• In all other statements, external references are resolved in the schema of the owner, whose privileges are checked at compile time.

• External references to constants follow the preceding rules only if the constants are not inlined.

  One optimization that the PL/SQL compiler can perform is constant inlining. Constant inlining replaces a reference to a constant with its value. The value of an inlined constant becomes part of the calling program, and is therefore available to it. Outside the calling program, however, the caller cannot access the constant.

In Example 8-17, the IR procedure above_salary includes two external references to the function num_above_salary in the package emp_actions in Example 1-14. The external reference in the SELECT statement is resolved in the schema of the current user. The external reference in the assignment statement is resolved in the schema of the owner of the procedure above_salary.

CREATE PROCEDURE above_salary
  (emp_id IN NUMBER)
  AUTHID CURRENT_USER
AS
  emps NUMBER;
BEGIN
  -- External reference is resolved in schema of current user:
  SELECT * FROM employees
    WHERE employee_id = emp_actions.num_above_salary(emp_id);

  -- External reference is resolved in schema of owner of above_salary:
  emps := emp_actions.num_above_salary(emp_id);
  DBMS_OUTPUT.PUT_LINE
    ('Number of employees with higher salary: ' || TO_CHAR(emps));
END;
/
CALL above_salary(120);
/

In Example 8-18, the IR procedure test is owned by schema_a and has an external reference to the constant c. When compiling test, the PL/SQL compiler inlines the external reference to c, replacing it with the value of c (which is one). The value of c (one) becomes part of the procedure test. Therefore, when schema_b invokes test, the external reference to c within test succeeds. However, schema_b cannot access c directly.

-- In schema_a:
CREATE OR REPLACE PACKAGE pkg IS
  c CONSTANT NUMBER :=  1;
END;
/
CREATE OR REPLACE PACKAGE c_test AUTHID CURRENT_USER IS
  PROCEDURE test; -- IR procedure owned by schema_a
END;
/
CREATE OR REPLACE PACKAGE BODY c_test IS
  PROCEDURE test IS
    n NUMBER;
  BEGIN
  -- External reference to constant c in package pkg:
    SELECT schema_a.pkg.c INTO n FROM DUAL;
  -- If external reference succeeds:
    DBMS_OUTPUT.PUT_LINE ('c_test: referenced schema_a.pkg.c');
  -- If external reference fails:
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE
        ('c_test: caught exception referencing schema_a.pkg.c: '
         || SQLCODE || ': ' || SQLERRM);
  END;
END;
/
-- Allow schema_b to execute package c_test:
GRANT EXECUTE ON schema_a.c_test TO schema_b;

-- In schema_b, invoke procedure test in package c_test:
SQL> BEGIN schema_a.c_test.test; END;
  2  /
-- External reference to constant c from inside procedure test succeeds:
c_test: referenced schema_a.pkg.c

PL/SQL procedure successfully completed.

-- External reference to constant c from outside procedure test fails:
SQL> DECLARE n NUMBER; BEGIN n := schema_a.pkg.c; END;
  2  /
DECLARE n NUMBER; BEGIN n := schema_a.pkg.c; END;
                             *
ERROR at line 1:
ORA-06550: line 1, column 30:
PLS-00201: identifier 'schema_a.pkg' must be declared
ORA-06550: line 1, column 25:
PL/SQL: Statement ignored

Need for Template Objects in IR Subprograms

The PL/SQL compiler must resolve all references to tables and other objects at compile time. The owner of an IR subprogram must have objects in the same schema with the right names and columns, even if they do not contain any data. At run time, the corresponding objects in the invoker's schema must have matching definitions. Otherwise, you get an error or unexpected results, such as ignoring table columns that exist in the invoker's schema but not in the schema that contains the subprogram.

Overriding Default Name Resolution in IR Subprograms

If you want an unqualified name to refer to a particular schema, rather than to the schema of the invoker, create a public synonym for the object; for example:

CREATE PUBLIC SYNONYM emp FOR hr.employees;

When the IR subprogram refers to emp, it matches the public synonym, which resolves to hr.employees.

Granting Privileges to IR Subprograms

To invoke a subprogram directly, users must have the EXECUTE privilege on that subprogram. By granting the privilege, you allow a user to:

• Invoke the subprogram directly

• Compile functions and procedures that invoke the subprogram

For external references resolved in the current user's schema (such as those in DML statements), the current user must have the privileges needed to access schema objects referenced by the subprogram. For all other external references (such as function calls), the owner's privileges are checked at compile time, and no run-time check is done.

A DR subprogram operates under the security domain of its owner, no matter who is executing it. The owner must have the privileges needed to access schema objects referenced by the subprogram.

You can write a program consisting of multiple subprograms, some with definer's rights and others with invoker's rights. Then, you can use the EXECUTE privilege to restrict program entry points. That way, users of an entry-point subprogram can execute the other subprograms indirectly but not directly.

Example: Granting Privileges on an IR Subprogram

Suppose user UTIL grants the EXECUTE privilege on subprogram FFT to user APP:

GRANT EXECUTE ON util.fft TO app;

Now, user APP can compile functions and procedures that invoke subprogram FFT. At run time, no privilege checks on the calls are done. As Figure 8-2 shows, user UTIL need not grant the EXECUTE privilege to every user who might invoke FFT indirectly.

Since subprogram util.fft is invoked directly only from IR subprogram app.entry, user util must grant the EXECUTE privilege only to user APP. When util.fft is executed, its current user can be APP, SCOTT, or BLAKE even though SCOTT and BLAKE were not granted the EXECUTE privilege.

Figure 8-2 Indirect Calls to an IR Subprogram

Description of "Figure 8-2 Indirect Calls to an IR Subprogram"

Using Views and Database Triggers with IR Subprograms

For IR subprograms executed within a view expression, the schema that created the view, not the schema that is querying the view, is considered to be the current user. This rule also applies to database triggers.

Using Database Links with IR Subprograms

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. To connect, Oracle uses the username of the current user (who must be a global user). Suppose an IR subprogram owned by user OE references the following database link. If global user HR invokes the subprogram, it connects to the Dallas database as user HR, who is the current user.

CREATE DATABASE LINK dallas CONNECT TO CURRENT_USER USING ...

If it were a definer's rights subprogram, the current user would be OE, and the subprogram would connect to the Dallas database as global user OE.

Using Object Types with IR Subprograms

To define object types for use in any schema, specify the AUTHID CURRENT_USER clause. For information on object types, see Oracle Database Object-Relational Developer's Guide.

Suppose user HR creates the following object type:

CREATE TYPE person_typ AUTHID CURRENT_USER AS OBJECT (
  person_id   NUMBER,
  person_name VARCHAR2(30),
  person_job  VARCHAR2(10),
  STATIC PROCEDURE new_person_typ (
    person_id NUMBER, person_name VARCHAR2, person_job VARCHAR2,
    schema_name VARCHAR2, table_name VARCHAR2),
  MEMBER PROCEDURE change_job (SELF IN OUT NOCOPY person_typ,
                               new_job VARCHAR2) 
  );
/
CREATE TYPE BODY person_typ AS
  STATIC PROCEDURE new_person_typ (
    person_id NUMBER, person_name VARCHAR2, person_job VARCHAR2,
    schema_name VARCHAR2, table_name VARCHAR2) IS
    sql_stmt VARCHAR2(200);
  BEGIN 
    sql_stmt := 'INSERT INTO ' || schema_name || '.'
       || table_name || ' VALUES (HR.person_typ(:1, :2, :3))';
    EXECUTE IMMEDIATE sql_stmt
      USING person_id, person_name, person_job;
  END;
  MEMBER PROCEDURE change_job (SELF IN OUT NOCOPY person_typ,
                               new_job VARCHAR2) IS
  BEGIN
    person_job := new_job;
  END;
END;
/

Then, user HR grants the EXECUTE privilege on object type person_typ to user OE:

GRANT EXECUTE ON person_typ TO OE;

Finally, user OE creates an object table to store objects of type person_typ, then invokes procedure new_person_typ to populate the table:

CONNECT OE/password;
CREATE TABLE person_tab OF hr.person_typ;

BEGIN
  hr.person_typ.new_person_typ(1001,
                               'Jane Smith',
                               'CLERK',
                               'oe',
                               'person_tab');
  hr.person_typ.new_person_typ(1002,
                               'Joe Perkins',
                               'SALES','oe',
                               'person_tab');
  hr.person_typ.new_person_typ(1003,
                               'Robert Lange',
                               'DEV',
                               'oe', 'person_tab');
                               'oe', 'person_tab');
END;
/

The calls succeed because the procedure executes with the privileges of its current user (OE), not its owner (HR).

For subtypes in an object type hierarchy, the following rules apply:

• If a subtype does not explicitly specify an AUTHID clause, it inherits the AUTHID of its supertype.

• If a subtype does specify an AUTHID clause, its AUTHID must match the AUTHID of its supertype. Also, if the AUTHID is DEFINER, both the supertype and subtype must have been created in the same schema.

Invoking IR Instance Methods

An IR instance method executes with the privileges of the invoker, not the creator of the instance. Suppose that person_typ is an IR object type as created in Example 8-19, and that user HR creates p1, an object of type person_typ. If user OE invokes instance method change_job to operate on object p1, the current user of the method is OE, not HR, as shown in Example 8-20.

-- oe creates a procedure that invokes change_job
CREATE PROCEDURE reassign
  (p IN OUT NOCOPY hr.person_typ, new_job VARCHAR2) AS
BEGIN
   p.change_job(new_job); -- executes with the privileges of oe
END;
/
-- OE grants EXECUTE to HR on procedure reassign
GRANT EXECUTE ON reassign to HR;
CONNECT HR/password

-- user hr passes a person_typ object to the procedure reassign
DECLARE
   p1 person_typ;
BEGIN
   p1 := person_typ(1004,  'June Washburn', 'SALES');
   oe.reassign(p1, 'CLERK'); -- current user is oe, not hr
END;
/

Enabling Result-Caching for a Function

To make a function result-cached, do the following:

• In the function declaration, include the option RESULT_CACHE.

• In the function definition:

  • Include the RESULT_CACHE clause.

  • In the optional RELIES_ON clause, specify any tables or views on which the function results depend.

For the syntax of the RESULT_CACHE and RELIES_ON clauses, see "Function Declaration and Definition".

In Example 8-27, the package PKG declares and then defines a result-cached function, get_dept_info, which returns the average salary and number of employees in a given department. get_dept_info depends on the database table EMPLOYEES.

-- Package specification
CREATE OR REPLACE PACKAGE department_pks IS
  TYPE dept_info_record IS RECORD (average_salary      NUMBER,
                                   number_of_employees NUMBER);
  -- Function declaration
  FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record
  RESULT_CACHE;
  ...
END department_pks;
/
CREATE OR REPLACE PACKAGE BODY department_pks AS
  -- Function definition
  FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record

RESULT_CACHE RELIES_ON (EMPLOYEES)
  IS

rec dept_info_record;
  BEGIN
    SELECT AVG(SALARY), COUNT(*) INTO result
      FROM EMPLOYEES
        WHERE DEPARTMENT_ID = dept_id;

RETURN rec;
  END get_dept_info;
  ...
END department_pks;
/
DECLARE
  dept_id   NUMBER := 50;
  avg_sal   NUMBER;
  no_of_emp NUMBER;
BEGIN
  avg_sal   := department_pks.get_dept_info(50).average_salary;
  no_of_emp :=department_pks.get_dept_info(50).number_of_employees;
  DBMS_OUTPUT.PUT_LINE('dept_id:=' ||dept_id );
  DBMS_OUTPUT.PUT_LINE('  average_salary= '|| avg_sal);
  DBMS_OUTPUT.PUT_LINE('   Number_of_employees' ||no_of_emp );
END;

/

You invoke the function get_dept_info as you invoke any function. For example, the following call returns the average salary and the number of employees in department number 10:

PKG.get_dept_info(10);

The following call returns only the average salary in department number 10:

PKG.get_dept_info(10).average_salary;

If the result for get_dept_info(10) is already in the result cache, the result is returned from the cache; otherwise, the result is computed and added to the cache. Because the RELIES_ON clause specifies EMPLOYEES, any update to EMPLOYEES invalidates all cached results for PKG.get_dept_info, relieving you of programming cache invalidation logic everywhere that EMPLOYEES might change.

Developing Applications with Result-Cached Functions

When developing an application that uses a result-cached function, make no assumptions about the number of times the body of the function will execute for a given set of parameter values.

Some situations in which the body of a result-cached function executes are:

• The first time a session on this database instance invokes the function with these parameter values

• When the cached result for these parameter values is invalid

  A cached result becomes invalid when any database object specified in the RELIES_ON clause of the function definition changes.

• When the cached results for these parameter values have aged out

  If the system needs memory, it might discard the oldest cached values.

• When the function bypasses the cache (see "Bypassing the Result Cache")

Restrictions on Result-Cached Functions

To be result-cached, a function must meet all of the following criteria:

• It is not defined in a module that has invoker's rights or in an anonymous block.

• It is not a pipelined table function.

• It has no OUT or IN OUT parameters.

• No IN parameter has one of the following types:

  • BLOB

  • CLOB

  • NCLOB

  • REF CURSOR

  • Collection

  • Object

  • Record

• The return type is none of the following:

  • BLOB

  • CLOB

  • NCLOB

  • REF CURSOR

  • Object

  • Record or PL/SQL collection that contains one of the preceding unsupported return types

It is recommended that a result-cached function also meet the following criteria:

• It has no side effects.

  For example, it does not modify the database state, or modify the external state by invoking DBMS_OUTPUT or sending email.

• It does not depend on session-specific settings.

  For more information, see "Making Result-Cached Functions Handle Session-Specific Settings".

• It does not depend on session-specific application contexts.

  For more information, see "Making Result-Cached Functions Handle Session-Specific Application Contexts".

Examples of Result-Cached Functions

The best candidates for result-caching are functions that are invoked frequently but depend on information that changes infrequently (as might be the case in the first example). Result-caching avoids redundant computations in recursive functions.

Examples:

• Result-Cached Application Configuration Parameters

• Result-Cached Recursive Function

-- Global Configuration Settings
CREATE TABLE global_config_params
   ( name  VARCHAR2(20), -- parameter NAME
     value VARCHAR2(20), -- parameter VALUE
     PRIMARY KEY (name)
   )
/

-- Application-Level Configuration Settings
CREATE TABLE app_level_config_params
   ( app_id  VARCHAR2(20), -- application ID
     name    VARCHAR2(20), -- parameter NAME
     value   VARCHAR2(20), -- parameter VALUE
     PRIMARY KEY (app_id, name)
   )
/

-- Role-Level Configuration Settings
CREATE TABLE role_level_config_params
   ( role_id  VARCHAR2(20), -- application (role) ID
     name     VARCHAR2(20),  -- parameter NAME
     value    VARCHAR2(20),  -- parameter VALUE
     PRIMARY KEY (role_id, name)
   )
/

FUNCTION get_value (p_param VARCHAR2,
                    p_app_id  NUMBER,
                    p_role_id NUMBER) RETURN VARCHAR2
                    RESULT_CACHE RELIES_ON (role_level_config_params,
                                            app_level_config_params,
                                            global_config_params)
IS
  answer VARCHAR2(20);
BEGIN
  -- Is parameter set at role level?
  BEGIN
    SELECT value INTO answer
      FROM role_level_config_params
        WHERE role_id = p_role_id
          AND name = p_param;
    RETURN answer;  -- Found
    EXCEPTION
      WHEN no_data_found THEN
        NULL;  -- Fall through to following code
  END;
  -- Is parameter set at application level?
  BEGIN
    SELECT value INTO answer
      FROM app_level_config_params
        WHERE app_id = p_app_id
          AND name = p_param;
    RETURN answer;  -- Found
    EXCEPTION
      WHEN no_data_found THEN
        NULL;  -- Fall through to following code
  END;
  -- Is parameter set at global level?
    SELECT value INTO answer
     FROM global_config_params
      WHERE name = p_param;
    RETURN answer;
END;

FUNCTION fibonacci (n NUMBER) RETURN NUMBER RESULT_CACHE IS
BEGIN
  IF (n =0) OR (n =1) THEN
    RETURN 1;
  ELSE
    RETURN fibonacci(n - 1) + fibonacci(n - 2);
  END IF;
END;

Advanced Result-Cached Function Topics

Topics:

• Rules for a Cache Hit

• Bypassing the Result Cache

• Making Result-Cached Functions Handle Session-Specific Settings

• Making Result-Cached Functions Handle Session-Specific Application Contexts

• Choosing Result-Caching Granularity

• Result Caches in Oracle RAC Environment

• Managing the Result Cache

• Hot-Patching PL/SQL Program Units on Which Result-Cached Functions Depend

FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR
 RESULT_CACHE RELIES_ON (HR.EMPLOYEES) IS
  date_hired DATE;
BEGIN
  SELECT hire_date INTO date_hired
   FROM HR.EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;
  RETURN TO_CHAR(date_hired);
END;

SELECT value FROM config_tab
   WHERE name = param_name;

SELECT value FROM config_tab
   WHERE name = param_name
     AND app_id = SYS_CONTEXT('Config', 'App_ID');

FUNCTION get_param_value
  ( param_name VARCHAR,
    appctx     VARCHAR
    DEFAULT SYS_CONTEXT('Config', 'App_ID')
  )
  RETURN VARCHAR
  RESULT_CACHE RELIES_ON (config_tab)
IS
  result VARCHAR(2000);
BEGIN
  SELECT value INTO result
    FROM config_tab
      WHERE Name = param_name;
END;

NAME                     NULL?      TYPE
----------------------   --------   ---------------
PRODUCT_ID               NOT NULL   NUMBER(6)
LANGUAGE_ID              NOT NULL   VARCHAR2(3)
TRANSLATED_NAME          NOT NULL   NVARCHAR2(50)
TRANSLATED_DESCRIPTION   NOT NULL   NVARCHAR2(2000)

FUNCTION get_product_name (prod_id NUMBER, lang_id VARCHAR2)
  RETURN NVARCHAR2
  RESULT_CACHE RELIES_ON (Product_Descriptions)
IS
  result VARCHAR2(50);
BEGIN
  SELECT translated_name INTO result
    FROM product_descriptions
      WHERE PRODUCT_ID = prod_id
        AND LANGUAGE_ID = lang_id;
  RETURN result;
END;

FUNCTION get_product_name (prod_id NUMBER, lang_id VARCHAR2)
  RETURN NVARCHAR2
IS
  TYPE product_names IS TABLE OF NVARCHAR2(50) INDEX BY PLS_INTEGER;

  FUNCTION all_product_names (lang_id NUMBER) RETURN product_names
    RESULT_CACHE RELIES_ON (product_descriptions)
  IS
    all_names product_names;
  BEGIN
    FOR c IN (SELECT * FROM product_descriptions
      WHERE LANGUAGE_ID = lang_id) LOOP
        all_names(c.PRODUCT_ID) = c.TRANSLATED_NAME;
    END LOOP;
  END;
  RETURN all_names;
BEGIN
  RETURN all_product_names(lang_id)(prod_id);
END;