A PL/SQL subprogram is a named PL/SQL block that can be invoked repeatedly. If the subprogram has parameters, their values can differ for each invocation.
A subprogram is either a procedure or a function. Typically, you use a procedure to perform an action and a function to compute and return a value.
Subprograms support the development and maintenance of reliable, reusable code with the following features:
Subprograms are an important component of other maintainability features, such as packages (explained in PL/SQL Packages) and Abstract Data Types (explained in "Abstract Data Types" ).
You can create a subprogram either inside a PL/SQL block (which can be another subprogram), inside a package, or at schema level.
A subprogram created inside a PL/SQL block is a nested subprogram . You can either declare and define it at the same time, or you can declare it first and then define it later in the same block (see "Forward Declaration" ). A nested subprogram is stored in the database only if it is nested in a standalone or package subprogram.
A subprogram created inside a package is a package subprogram . You declare it in the package specification and define it in the package body. It is stored in the database until you drop the package. (Packages are described in PL/SQL Packages.)
A subprogram created at schema level is a standalone subprogram . You create it with the CREATE FUNCTION or CREATE PROCEDURE statement. It is stored in the database until you drop it with the DROP FUNCTION or DROP PROCEDURE statement. (These statements are described in SQL Statements for Stored PL/SQL Units.)
A stored subprogram is either a package subprogram or a standalone subprogram. A stored subprogram is affected by the AUTHID and ACCESSIBLE BY clauses, which can appear in the CREATE FUNCTION , CREATE PROCEDURE , and CREATE PACKAGE statements. The AUTHID clause affects the name resolution and privilege checking of SQL statements that the subprogram issues at run time (for more information, see "Invoker's Rights and Definer's Rights (AUTHID Property)" ). The ACCESSIBLE BY clause specifies a white list of PL/SQL units that can access the subprogram.
A subprogram invocation has this form:
subprogram_name [ ( [ parameter [, parameter]. ] ) ]
If the subprogram has no parameters, or specifies a default value for every parameter, you can either omit the parameter list or specify an empty parameter list.
A procedure invocation is a PL/SQL statement. For example:
raise_salary(employee_id, amount);
A function invocation is an expression. For example:
new_salary := get_salary(employee_id); IF salary_ok(new_salary, new_title) THEN .
"Subprogram Parameters" for more information about specifying parameters in subprogram invocations
Each subprogram property can appear only once in the subprogram declaration. The properties can appear in any order. Properties appear before the IS or AS keyword in the subprogram heading. The properties cannot appear in nested subprograms.
Only the ACCESSIBLE BY property can appear in package subprograms. Standalone subprograms may have the following properties in their declaration.
A subprogram begins with a subprogram heading , which specifies its name and (optionally) its parameter list.
Like an anonymous block, a subprogram has these parts:
Note: The declarative part of a subprogram does not begin with the keyword DECLARE , as the declarative part of an anonymous block does.
Example 9-1 Declaring, Defining, and Invoking a Simple PL/SQL Procedure
In this example, an anonymous block simultaneously declares and defines a procedure and invokes it three times. The third invocation raises the exception that the exception-handling part of the procedure handles.
DECLARE first_name employees.first_name%TYPE; last_name employees.last_name%TYPE; email employees.email%TYPE; employer VARCHAR2(8) := 'AcmeCorp'; -- Declare and define procedure PROCEDURE create_email ( -- Subprogram heading begins name1 VARCHAR2, name2 VARCHAR2, company VARCHAR2 ) -- Subprogram heading ends IS -- Declarative part begins error_message VARCHAR2(30) := 'Email address is too long.'; BEGIN -- Executable part begins email := name1 || '.' || name2 || '@' || company; EXCEPTION -- Exception-handling part begins WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE(error_message); END create_email; BEGIN first_name := 'John'; last_name := 'Doe'; create_email(first_name, last_name, employer); -- invocation DBMS_OUTPUT.PUT_LINE ('With first name first, email is: ' || email); create_email(last_name, first_name, employer); -- invocation DBMS_OUTPUT.PUT_LINE ('With last name first, email is: ' || email); first_name := 'Elizabeth'; last_name := 'MacDonald'; create_email(first_name, last_name, employer); -- invocation END; /
With first name first, email is: John.Doe@AcmeCorp With last name first, email is: Doe.John@AcmeCorp Email address is too long.
A function has the same structure as a procedure, except that:
Helps the optimizer avoid redundant function invocations.
Enables the function for parallel execution, making it safe for use in concurrent sessions of parallel DML evaluations.
Makes a table function pipelined, for use as a row source.
Stores function results in the PL/SQL function result cache.
Example 9-2 Declaring, Defining, and Invoking a Simple PL/SQL Function
In this example, an anonymous block simultaneously declares and defines a function and invokes it.
DECLARE -- Declare and define function FUNCTION square (original NUMBER) -- parameter list RETURN NUMBER -- RETURN clause AS -- Declarative part begins original_squared NUMBER; BEGIN -- Executable part begins original_squared := original * original; RETURN original_squared; -- RETURN statement END; BEGIN DBMS_OUTPUT.PUT_LINE(square(100)); -- invocation END; /
10000
The RETURN statement immediately ends the execution of the subprogram or anonymous block that contains it. A subprogram or anonymous block can contain multiple RETURN statements.
"RETURN Statement" for the syntax of the RETURN statement
In a function, every execution path must lead to a RETURN statement and every RETURN statement must specify an expression. The RETURN statement assigns the value of the expression to the function identifier and returns control to the invoker, where execution resumes immediately after the invocation.
In a pipelined table function, a RETURN statement need not specify an expression. For information about the parts of a pipelined table function, see "Creating Pipelined Table Functions" .
In Example 9-3, the anonymous block invokes the same function twice. The first time, the RETURN statement returns control to the inside of the invoking statement. The second time, the RETURN statement returns control to the statement immediately after the invoking statement.
In Example 9-4, the function has multiple RETURN statements, but if the parameter is not 0 or 1, then no execution path leads to a RETURN statement. The function compiles with warning PLW-05005: subprogram F returns without value at line 11.
Example 9-5 is like Example 9-4, except for the addition of the ELSE clause. Every execution path leads to a RETURN statement, and the function compiles without warning PLW-05005.
Example 9-3 Execution Resumes After RETURN Statement in Function
DECLARE x INTEGER; FUNCTION f (n INTEGER) RETURN INTEGER IS BEGIN RETURN (n*n); END; BEGIN DBMS_OUTPUT.PUT_LINE ( 'f returns ' || f(2) || '. Execution returns here (1).' ); x := f(2); DBMS_OUTPUT.PUT_LINE('Execution returns here (2).'); END; /
f returns 4. Execution returns here (1).Execution returns here (2).
Example 9-4 Function Where Not Every Execution Path Leads to RETURN Statement
CREATE OR REPLACE FUNCTION f (n INTEGER) RETURN INTEGER AUTHID DEFINER IS BEGIN IF n = 0 THEN RETURN 1; ELSIF n = 1 THEN RETURN n; END IF; END; /
Example 9-5 Function Where Every Execution Path Leads to RETURN Statement
CREATE OR REPLACE FUNCTION f (n INTEGER) RETURN INTEGER AUTHID DEFINER IS BEGIN IF n = 0 THEN RETURN 1; ELSIF n = 1 THEN RETURN n; ELSE RETURN n*n; END IF; END; / BEGIN FOR i IN 0 .. 3 LOOP DBMS_OUTPUT.PUT_LINE('f(' || i || ') = ' || f(i)); END LOOP; END; /
f(0) = 1 f(1) = 1 f(2) = 4 f(3) = 9
In a procedure, the RETURN statement returns control to the invoker, where execution resumes immediately after the invocation. The RETURN statement cannot specify an expression.
In Example 9-6, the RETURN statement returns control to the statement immediately after the invoking statement.
Example 9-6 Execution Resumes After RETURN Statement in Procedure
DECLARE PROCEDURE p IS BEGIN DBMS_OUTPUT.PUT_LINE('Inside p'); RETURN; DBMS_OUTPUT.PUT_LINE('Unreachable statement.'); END; BEGIN p; DBMS_OUTPUT.PUT_LINE('Control returns here.'); END; /
Inside p Control returns here.
In an anonymous block, the RETURN statement exits its own block and all enclosing blocks. The RETURN statement cannot specify an expression.
In Example 9-7, the RETURN statement exits both the inner and outer block.
Example 9-7 Execution Resumes After RETURN Statement in Anonymous Block
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; /
Inside inner block.
If nested subprograms in the same PL/SQL block invoke each other, then one requires a forward declaration, because a subprogram must be declared before it can be invoked.
A forward declaration declares a nested subprogram but does not define it. You must define it later in the same block. The forward declaration and the definition must have the same subprogram heading.
In Example 9-8, an anonymous block creates two procedures that invoke each other.
Example 9-8 Nested Subprograms Invoke Each Other
DECLARE -- Declare proc1 (forward declaration): PROCEDURE proc1(number1 NUMBER); -- Declare and define proc2: PROCEDURE proc2(number2 NUMBER) IS BEGIN proc1(number2); END; -- Define proc 1: PROCEDURE proc1(number1 NUMBER) IS BEGIN proc2 (number1); END; BEGIN NULL; END; /
If a subprogram has parameters, their values can differ for each invocation.
If you want a subprogram to have parameters, declare formal parameters in the subprogram heading. In each formal parameter declaration, specify the name and data type of the parameter, and (optionally) its mode and default value. In the execution part of the subprogram, reference the formal parameters by their names.
When invoking the subprogram, specify the actual parameters whose values are to be assigned to the formal parameters. Corresponding actual and formal parameters must have compatible data types.
You can declare a formal parameter of a constrained subtype, like this:
DECLARE SUBTYPE n1 IS NUMBER(1); SUBTYPE v1 IS VARCHAR2(1); PROCEDURE p (n n1, v v1) IS .
But you cannot include a constraint in a formal parameter declaration, like this:
DECLARE PROCEDURE p (n NUMBER(1), v VARCHAR2(1)) IS .
To avoid confusion, use different names for formal and actual parameters.
In Example 9-9, the procedure has formal parameters emp_id and amount . In the first procedure invocation, the corresponding actual parameters are emp_num and bonus , whose value are 120 and 100, respectively. In the second procedure invocation, the actual parameters are emp_num and merit + bonus , whose value are 120 and 150, respectively.
Example 9-9 Formal Parameters and Actual Parameters
DECLARE emp_num NUMBER(6) := 120; bonus NUMBER(6) := 100; merit NUMBER(4) := 50; PROCEDURE raise_salary ( emp_id NUMBER, -- formal parameter amount NUMBER -- formal parameter ) IS BEGIN UPDATE employees SET salary = salary + amount -- reference to formal parameter WHERE employee_id = emp_id; -- reference to formal parameter END raise_salary; BEGIN raise_salary(emp_num, bonus); -- actual parameters /* raise_salary runs this statement: UPDATE employees SET salary = salary + 100 WHERE employee_id = 120; */ raise_salary(emp_num, merit + bonus); -- actual parameters /* raise_salary runs this statement: UPDATE employees SET salary = salary + 150 WHERE employee_id = 120; */ END; /
If the data type of a formal parameter is a constrained subtype, then:
In a function, the clause RETURN datatype declares a hidden formal parameter and the statement RETURN value specifies the corresponding actual parameter. Therefore, if datatype is a constrained data type, then the preceding rules apply to value (see Example 9-11).
Example 9-10 shows that an actual subprogram parameter inherits the NOT NULL constraint but not the size of a VARCHAR2 subtype.
As PL/SQL Predefined Data Types shows, PL/SQL has many predefined data types that are constrained subtypes of other data types. For example, INTEGER is a constrained subtype of NUMBER :
SUBTYPE INTEGER IS NUMBER(38,0);
In Example 9-11, the function has both an INTEGER formal parameter and an INTEGER return type. The anonymous block invokes the function with an actual parameter that is not an integer. Because the actual parameter inherits the range but not the precision and scale of INTEGER , and the actual parameter is in the INTEGER range, the invocation succeeds. For the same reason, the RETURN statement succeeds in returning the noninteger value.
In Example 9-12, the function implicitly converts its formal parameter to the constrained subtype INTEGER before returning it.
"Constrained Subtypes" for general information about constrained subtypes
Example 9-10 Actual Parameter Inherits Only NOT NULL from Subtype
DECLARE SUBTYPE License IS VARCHAR2(7) NOT NULL; n License := 'DLLLDDD'; PROCEDURE p (x License) IS BEGIN DBMS_OUTPUT.PUT_LINE(x); END; BEGIN p('1ABC123456789'); -- Succeeds; size is not inherited p(NULL); -- Raises error; NOT NULL is inherited END; /
p(NULL); -- Raises error; NOT NULL is inherited * ERROR at line 12: ORA-06550: line 12, column 5: PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter ORA-06550: line 12, column 3: PL/SQL: Statement ignored
Example 9-11 Actual Parameter and Return Value Inherit Only Range From Subtype
DECLARE FUNCTION test (p INTEGER) RETURN INTEGER IS BEGIN DBMS_OUTPUT.PUT_LINE('p = ' || p); RETURN p; END test; BEGIN DBMS_OUTPUT.PUT_LINE('test(p) = ' || test(0.66)); END; /
p = .66 test(p) = .66 PL/SQL procedure successfully completed.
Example 9-12 Function Implicitly Converts Formal Parameter to Constrained Subtype
DECLARE FUNCTION test (p NUMBER) RETURN NUMBER IS q INTEGER := p; -- Implicitly converts p to INTEGER BEGIN DBMS_OUTPUT.PUT_LINE('p = ' || q); -- Display q, not p RETURN q; -- Return q, not p END test; BEGIN DBMS_OUTPUT.PUT_LINE('test(p) = ' || test(0.66)); END; /
p = 1 test(p) = 1 PL/SQL procedure successfully completed.
The PL/SQL compiler has two ways of passing an actual parameter to a subprogram:
In Example 9-13, the procedure p has one parameter, n , which is passed by value. The anonymous block invokes p three times, avoiding implicit conversion twice.
The method by which the compiler passes a specific actual parameter depends on its mode, as explained in "Subprogram Parameter Modes" .
Example 9-13 Avoiding Implicit Conversion of Actual Parameters
CREATE OR REPLACE PROCEDURE p ( n NUMBER ) AUTHID DEFINER IS BEGIN NULL; END; / DECLARE x NUMBER := 1; y VARCHAR2(1) := '1'; BEGIN p(x); -- No conversion needed p(y); -- z implicitly converted from VARCHAR2 to NUMBER p(TO_NUMBER(y)); -- z explicitly converted from VARCHAR2 to NUMBER END; /
The mode of a formal parameter determines its behavior.
Table 9-1 summarizes and compares the characteristics of the subprogram parameter modes.
Table 9-1 PL/SQL Subprogram Parameter Modes
Passes a value to the subprogram.
Must be specified.
Returns a value to the invoker.
Must be specified.
Passes an initial value to the subprogram and returns an updated value to the invoker.
Table 9-2 PL/SQL Subprogram Parameter Modes Characteristics
Formal parameter acts like a constant: When the subprogram begins, its value is that of either its actual parameter or default value, and the subprogram cannot change this value.
Actual parameter can be a constant, initialized variable, literal, or expression.
Actual parameter is passed by reference.
Formal parameter is initialized to the default value of its type. The default value of the type is NULL except for a record type with a non- NULL default value (see Example 9-16).
When the subprogram begins, the formal parameter has its initial value regardless of the value of its actual parameter. Oracle recommends that the subprogram assign a value to the formal parameter.
If the default value of the formal parameter type is NULL , then the actual parameter must be a variable whose data type is not defined as NOT NULL .
By default, actual parameter is passed by value; if you specify NOCOPY , it might be passed by reference.
Formal parameter acts like an initialized variable: When the subprogram begins, its value is that of its actual parameter. Oracle recommends that the subprogram update its value.
Actual parameter must be a variable (typically, it is a string buffer or numeric accumulator).
By default, actual parameter is passed by value (in both directions); if you specify NOCOPY , it might be passed by reference.
Do not use OUT and IN OUT for function parameters. Ideally, a function takes zero or more parameters and returns a single value. A function with IN OUT parameters returns multiple values and has side effects.
The specifications of many packages and types that Oracle Database supplies declare formal parameters with this notation:
i1 IN VARCHAR2 CHARACTER SET ANY_CS i2 IN VARCHAR2 CHARACTER SET i1%CHARSET
Do not use this notation when declaring your own formal or actual parameters. It is reserved for Oracle implementation of the supplied packages types.
Regardless of how an OUT or IN OUT parameter is passed:
CREATE OR REPLACE PROCEDURE p (x OUT INTEGER, y OUT INTEGER) AS BEGIN x := 17; y := 93; END; /
When an OUT or IN OUT parameter is passed by reference, the actual and formal parameters refer to the same memory location. Therefore, if the subprogram changes the value of the formal parameter, the change shows immediately in the actual parameter (see "Subprogram Parameter Aliasing with Parameters Passed by Reference" ).
In Example 9-14, the procedure p has two IN parameters, one OUT parameter, and one IN OUT parameter. The OUT and IN OUT parameters are passed by value (the default). The anonymous block invokes p twice, with different actual parameters. Before each invocation, the anonymous block prints the values of the actual parameters. The procedure p prints the initial values of its formal parameters. After each invocation, the anonymous block prints the values of the actual parameters again.
In Example 9-15, the anonymous block invokes procedure p (from Example 9-14) with an actual parameter that causes p to raise the predefined exception ZERO_DIVIDE , which p does not handle. The exception propagates to the anonymous block, which handles ZERO_DIVIDE and shows that the actual parameters for the IN and IN OUT parameters of p have retained the values that they had before the invocation. (Exception propagation is explained in "Exception Propagation" .)
In Example 9-16, the procedure p has three OUT formal parameters: x , of a record type with a non- NULL default value; y , of a record type with no non- NULL default value; and z , which is not a record.
The corresponding actual parameters for x , y , and z are r1 , r2 , and s , respectively. s is declared with an initial value. However, when p is invoked, the value of s is initialized to NULL . The values of r1 and r2 are initialized to the default values of their record types, 'abcde' and NULL , respectively.
Example 9-14 Parameter Values Before, During, and After Procedure Invocation
CREATE OR REPLACE PROCEDURE p ( a PLS_INTEGER, -- IN by default b IN PLS_INTEGER, c OUT PLS_INTEGER, d IN OUT BINARY_FLOAT ) AUTHID DEFINER IS BEGIN -- Print values of parameters: DBMS_OUTPUT.PUT_LINE('Inside procedure p:'); DBMS_OUTPUT.PUT('IN a = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(a), 'NULL')); DBMS_OUTPUT.PUT('IN b = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(b), 'NULL')); DBMS_OUTPUT.PUT('OUT c = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(c), 'NULL')); DBMS_OUTPUT.PUT_LINE('IN OUT d = ' || TO_CHAR(d)); -- Can reference IN parameters a and b, -- but cannot assign values to them. c := a+10; -- Assign value to OUT parameter d := 10/b; -- Assign value to IN OUT parameter END; / DECLARE aa CONSTANT PLS_INTEGER := 1; bb PLS_INTEGER := 2; cc PLS_INTEGER := 3; dd BINARY_FLOAT := 4; ee PLS_INTEGER; ff BINARY_FLOAT := 5; BEGIN DBMS_OUTPUT.PUT_LINE('Before invoking procedure p:'); DBMS_OUTPUT.PUT('aa = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa), 'NULL')); DBMS_OUTPUT.PUT('bb = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(bb), 'NULL')); DBMS_OUTPUT.PUT('cc = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(cc), 'NULL')); DBMS_OUTPUT.PUT_LINE('dd = ' || TO_CHAR(dd)); p (aa, -- constant bb, -- initialized variable cc, -- initialized variable dd -- initialized variable ); DBMS_OUTPUT.PUT_LINE('After invoking procedure p:'); DBMS_OUTPUT.PUT('aa = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa), 'NULL')); DBMS_OUTPUT.PUT('bb = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(bb), 'NULL')); DBMS_OUTPUT.PUT('cc = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(cc), 'NULL')); DBMS_OUTPUT.PUT_LINE('dd = ' || TO_CHAR(dd)); DBMS_OUTPUT.PUT_LINE('Before invoking procedure p:'); DBMS_OUTPUT.PUT('ee = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(ee), 'NULL')); DBMS_OUTPUT.PUT_LINE('ff = ' || TO_CHAR(ff)); p (1, -- literal (bb+3)*4, -- expression ee, -- uninitialized variable ff -- initialized variable ); DBMS_OUTPUT.PUT_LINE('After invoking procedure p:'); DBMS_OUTPUT.PUT('ee = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(ee), 'NULL')); DBMS_OUTPUT.PUT_LINE('ff = ' || TO_CHAR(ff)); END; /
Before invoking procedure p: aa = 1 bb = 2 cc = 3 dd = 4.0E+000 Inside procedure p: IN a = 1 IN b = 2 OUT c = NULL IN OUT d = 4.0E+000 After invoking procedure p: aa = 1 bb = 2 cc = 11 dd = 5.0E+000 Before invoking procedure p: ee = NULL ff = 5.0E+000 Inside procedure p: IN a = 1 IN b = 20 OUT c = NULL IN OUT d = 5.0E+000 After invoking procedure p: ee = 11 ff = 5.0E-001 PL/SQL procedure successfully completed.
Example 9-15 OUT and IN OUT Parameter Values After Exception Handling
DECLARE j PLS_INTEGER := 10; k BINARY_FLOAT := 15; BEGIN DBMS_OUTPUT.PUT_LINE('Before invoking procedure p:'); DBMS_OUTPUT.PUT('j = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(j), 'NULL')); DBMS_OUTPUT.PUT_LINE('k = ' || TO_CHAR(k)); p(4, 0, j, k); -- causes p to exit with exception ZERO_DIVIDE EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('After invoking procedure p:'); DBMS_OUTPUT.PUT('j = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(j), 'NULL')); DBMS_OUTPUT.PUT_LINE('k = ' || TO_CHAR(k)); END; /
Before invoking procedure p: j = 10 k = 1.5E+001 Inside procedure p: IN a = 4 IN b = 0 OUT c = NULL IN OUT d = 1.5E+001 After invoking procedure p: j = 10 k = 1.5E+001 PL/SQL procedure successfully completed.
Example 9-16 OUT Formal Parameter of Record Type with Non-NULL Default Value
CREATE OR REPLACE PACKAGE r_types AUTHID DEFINER IS TYPE r_type_1 IS RECORD (f VARCHAR2(5) := 'abcde'); TYPE r_type_2 IS RECORD (f VARCHAR2(5)); END; / CREATE OR REPLACE PROCEDURE p ( x OUT r_types.r_type_1, y OUT r_types.r_type_2, z OUT VARCHAR2) AUTHID CURRENT_USER IS BEGIN DBMS_OUTPUT.PUT_LINE('x.f is ' || NVL(x.f,'NULL')); DBMS_OUTPUT.PUT_LINE('y.f is ' || NVL(y.f,'NULL')); DBMS_OUTPUT.PUT_LINE('z is ' || NVL(z,'NULL')); END; / DECLARE r1 r_types.r_type_1; r2 r_types.r_type_2; s VARCHAR2(5) := 'fghij'; BEGIN p (r1, r2, s); END; /
x.f is abcde y.f is NULL z is NULL PL/SQL procedure successfully completed.
Aliasing is having two different names for the same memory location. If a stored item is visible by more than one path, and you can change the item by one path, then you can see the change by all paths.
Subprogram parameter aliasing always occurs when the compiler passes an actual parameter by reference, and can also occur when a subprogram has cursor variable parameters.
When the compiler passes an actual parameter by reference, the actual and formal parameters refer to the same memory location. Therefore, if the subprogram changes the value of the formal parameter, the change shows immediately in the actual parameter.
The compiler always passes IN parameters by reference, but the resulting aliasing cannot cause problems, because subprograms cannot assign values to IN parameters.
The compiler might pass an OUT or IN OUT parameter by reference, if you specify NOCOPY for that parameter. NOCOPY is only a hint—each time the subprogram is invoked, the compiler decides, silently, whether to obey or ignore NOCOPY . Therefore, aliasing can occur for one invocation but not another, making subprogram results indeterminate. For example:
"NOCOPY" for the cases in which the compiler always ignores NOCOPY
In Example 9-17, the procedure has an IN OUT NOCOPY formal parameter, to which it assigns the value 'aardvark' . The anonymous block assigns the value 'aardwolf' to a global variable and then passes the global variable to the procedure. If the compiler obeys the NOCOPY hint, then the final value of the global variable is 'aardvark' . If the compiler ignores the NOCOPY hint, then the final value of the global variable is 'aardwolf' .
In Example 9-18, the procedure has an IN parameter, an IN OUT parameter, and an IN OUT NOCOPY parameter. The anonymous block invokes the procedure, using the same actual parameter, a global variable, for all three formal parameters. The procedure changes the value of the IN OUT parameter before it changes the value of the IN OUT NOCOPY parameter. However, if the compiler obeys the NOCOPY hint, then the latter change shows in the actual parameter immediately. The former change shows in the actual parameter after the procedure is exited successfully and control returns to the anonymous block.
Example 9-17 Aliasing from Global Variable as Actual Parameter
DECLARE TYPE Definition IS RECORD ( word VARCHAR2(20), meaning VARCHAR2(200) ); TYPE Dictionary IS VARRAY(2000) OF Definition; lexicon Dictionary := Dictionary(); -- global variable PROCEDURE add_entry ( word_list IN OUT NOCOPY Dictionary -- formal NOCOPY parameter ) IS BEGIN word_list(1).word := 'aardvark'; END; BEGIN lexicon.EXTEND; lexicon(1).word := 'aardwolf'; add_entry(lexicon); -- global variable is actual parameter DBMS_OUTPUT.PUT_LINE(lexicon(1).word); END; /
aardvark
Example 9-18 Aliasing from Same Actual Parameter for Multiple Formal Parameters
DECLARE n NUMBER := 10; PROCEDURE p ( n1 IN NUMBER, n2 IN OUT NUMBER, n3 IN OUT NOCOPY NUMBER ) IS BEGIN n2 := 20; -- actual parameter is 20 only after procedure succeeds DBMS_OUTPUT.put_line(n1); -- actual parameter value is still 10 n3 := 30; -- might change actual parameter immediately DBMS_OUTPUT.put_line(n1); -- actual parameter value is either 10 or 30 END; BEGIN p(n, n, n); DBMS_OUTPUT.put_line(n); END; /
Result if the compiler obeys the NOCOPY hint:
10 30 20
Result if the compiler ignores the NOCOPY hint:
10 10 30
Cursor variable parameters are pointers. Therefore, if a subprogram assigns one cursor variable parameter to another, they refer to the same memory location. This aliasing can have unintended results.
In Example 9-19, the procedure has two cursor variable parameters, emp_cv1 and emp_cv2 . The procedure opens emp_cv1 and assigns its value (which is a pointer) to emp_cv2 . Now emp_cv1 and emp_cv2 refer to the same memory location. When the procedure closes emp_cv1 , it also closes emp_cv2 . Therefore, when the procedure tries to fetch from emp_cv2 , PL/SQL raises an exception.
Example 9-19 Aliasing from Cursor Variable Subprogram Parameters
DECLARE TYPE EmpCurTyp IS REF CURSOR; c1 EmpCurTyp; c2 EmpCurTyp; PROCEDURE get_emp_data ( emp_cv1 IN OUT EmpCurTyp, emp_cv2 IN OUT EmpCurTyp ) IS emp_rec employees%ROWTYPE; BEGIN OPEN emp_cv1 FOR SELECT * FROM employees; emp_cv2 := emp_cv1; -- now both variables refer to same location FETCH emp_cv1 INTO emp_rec; -- fetches first row of employees FETCH emp_cv1 INTO emp_rec; -- fetches second row of employees FETCH emp_cv2 INTO emp_rec; -- fetches third row of employees CLOSE emp_cv1; -- closes both variables FETCH emp_cv2 INTO emp_rec; -- causes error when get_emp_data is invoked END; BEGIN get_emp_data(c1, c2); END; /
DECLARE * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at line 19 ORA-06512: at line 22
When you declare a formal IN parameter, you can specify a default value for it. A formal parameter with a default value is called an optional parameter , because its corresponding actual parameter is optional in a subprogram invocation. If the actual parameter is omitted, then the invocation assigns the default value to the formal parameter. A formal parameter with no default value is called a required parameter , because its corresponding actual parameter is required in a subprogram invocation.
Omitting an actual parameter does not make the value of the corresponding formal parameter NULL . To make the value of a formal parameter NULL , specify NULL as either the default value or the actual parameter.
In Example 9-20, the procedure has one required parameter and two optional parameters.
In Example 9-20, the procedure invocations specify the actual parameters in the same order as their corresponding formal parameters are declared—that is, the invocations use positional notation. Positional notation does not let you omit the second parameter of raise_salary but specify the third; to do that, you must use either named or mixed notation. For more information, see "Positional, Named, and Mixed Notation for Actual Parameters" .
The default value of a formal parameter can be any expression whose value can be assigned to the parameter; that is, the value and parameter must have compatible data types. If a subprogram invocation specifies an actual parameter for the formal parameter, then that invocation does not evaluate the default value.
In Example 9-21, the procedure p has a parameter whose default value is an invocation of the function f . The function f increments the value of a global variable. When p is invoked without an actual parameter, p invokes f , and f increments the global variable. When p is invoked with an actual parameter, p does not invoke f , and value of the global variable does not change.
Example 9-22 creates a procedure with two required parameters, invokes it, and then adds a third, optional parameter. Because the third parameter is optional, the original invocation remains valid.
Example 9-20 Procedure with Default Parameter Values
DECLARE PROCEDURE raise_salary ( emp_id IN employees.employee_id%TYPE, amount IN employees.salary%TYPE := 100, extra IN employees.salary%TYPE := 50 ) IS BEGIN UPDATE employees SET salary = salary + amount + extra WHERE employee_id = emp_id; END raise_salary; BEGIN raise_salary(120); -- same as raise_salary(120, 100, 50) raise_salary(121, 200); -- same as raise_salary(121, 200, 50) END; /
Example 9-21 Function Provides Default Parameter Value
DECLARE global PLS_INTEGER := 0; FUNCTION f RETURN PLS_INTEGER IS BEGIN DBMS_OUTPUT.PUT_LINE('Inside f.'); global := global + 1; RETURN global * 2; END f; PROCEDURE p ( x IN PLS_INTEGER := f() ) IS BEGIN DBMS_OUTPUT.PUT_LINE ( 'Inside p. ' || ' global = ' || global || ', x = ' || x || '.' ); DBMS_OUTPUT.PUT_LINE('--------------------------------'); END p; PROCEDURE pre_p IS BEGIN DBMS_OUTPUT.PUT_LINE ( 'Before invoking p, global = ' || global || '.' ); DBMS_OUTPUT.PUT_LINE('Invoking p.'); END pre_p; BEGIN pre_p; p(); -- default expression is evaluated pre_p; p(100); -- default expression is not evaluated pre_p; p(); -- default expression is evaluated END; /
Before invoking p, global = 0. Invoking p. Inside f. Inside p. global = 1, x = 2. -------------------------------- Before invoking p, global = 1. Invoking p. Inside p. global = 1, x = 100. -------------------------------- Before invoking p, global = 1. Invoking p. Inside f. Inside p. global = 2, x = 4. --------------------------------
Example 9-22 Adding Subprogram Parameter Without Changing Existing Invocations
CREATE OR REPLACE PROCEDURE print_name ( first VARCHAR2, last VARCHAR2 ) AUTHID DEFINER IS BEGIN DBMS_OUTPUT.PUT_LINE(first || ' ' || last); END print_name; /
BEGIN print_name('John', 'Doe'); END; /
John Doe
Add third parameter with default value:
CREATE OR REPLACE PROCEDURE print_name ( first VARCHAR2, last VARCHAR2, mi VARCHAR2 := NULL ) AUTHID DEFINER IS BEGIN IF mi IS NULL THEN DBMS_OUTPUT.PUT_LINE(first || ' ' || last); ELSE DBMS_OUTPUT.PUT_LINE(first || ' ' || mi || '. ' || last); END IF; END print_name; /
BEGIN print_name('John', 'Doe'); -- original invocation print_name('John', 'Public', 'Q'); -- new invocation END; /
John Doe John Q. Public
When invoking a subprogram, you can specify the actual parameters using either positional, named, or mixed notation. Table 9-3 summarizes and compares these notations.
Table 9-3 PL/SQL Actual Parameter Notations
Specify the actual parameters in the same order as the formal parameters are declared.
You can omit trailing optional parameters.
Specifying actual parameters in the wrong order can cause problems that are hard to detect, especially if the actual parameters are literals.
Subprogram invocations must change if the formal parameter list changes, unless the list only acquires new trailing optional parameters (as in Example 9-22).
Reduced code clarity and maintainability. Not recommended if the subprogram has a large number of parameters.
Specify the actual parameters in any order, using this syntax:
formal => actual
formal is the name of the formal parameter and actual is the actual parameter.
You can omit any optional parameters.
There is no wrong order for specifying actual parameters.
Subprogram invocations must change only if the formal parameter list acquires new required parameters.
Recommended when you invoke a subprogram defined or maintained by someone else.
Start with positional notation, then use named notation for the remaining parameters.
In the positional notation, you can omit trailing optional parameters; in the named notation, you can omit any optional parameters.
Convenient when you invoke a subprogram that has required parameters followed by optional parameters, and you must specify only a few of the optional parameters.
In the positional notation, the wrong order can cause problems that are hard to detect, especially if the actual parameters are literals.
Changes to the formal parameter list might require changes in the positional notation.
In Example 9-23, the procedure invocations use different notations, but are equivalent.
In Example 9-24, the SQL SELECT statements invoke the PL/SQL function compute_bonus , using equivalent invocations with different notations.
Example 9-23 Equivalent Invocations with Different Notations in Anonymous Block
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 -- Equivalent invocations: raise_salary(emp_num, bonus); -- positional notation raise_salary(amount => bonus, emp_id => emp_num); -- named notation raise_salary(emp_id => emp_num, amount => bonus); -- named notation raise_salary(emp_num, amount => bonus); -- mixed notation END; /
Example 9-24 Equivalent Invocations with Different Notations in SELECT Statements
CREATE OR REPLACE FUNCTION compute_bonus ( emp_id NUMBER, bonus NUMBER ) RETURN NUMBER AUTHID DEFINER IS emp_sal NUMBER; BEGIN SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; RETURN emp_sal + bonus; END compute_bonus; / 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
When the PL/SQL compiler encounters a subprogram invocation, it searches for a matching subprogram declaration—first in the current scope and then, if necessary, in successive enclosing scopes.
A declaration and invocation match if their subprogram names and parameter lists match. The parameter lists match if each required formal parameter in the declaration has a corresponding actual parameter in the invocation.
If the compiler finds no matching declaration for an invocation, then it generates a semantic error.
Figure 9-1 shows how the PL/SQL compiler resolves a subprogram invocation.
Figure 9-1 How PL/SQL Compiler Resolves Invocations
In Example 9-25, the function balance tries to invoke the enclosing procedure swap , using appropriate actual parameters. However, balance contains two nested procedures named swap , and neither has parameters of the same type as the enclosing procedure swap . Therefore, the invocation causes compilation error PLS-00306.
Example 9-25 Resolving PL/SQL Procedure Names
DECLARE PROCEDURE swap ( n1 NUMBER, n2 NUMBER ) IS num1 NUMBER; num2 NUMBER; FUNCTION balance (bal NUMBER) RETURN NUMBER IS x NUMBER := 10; PROCEDURE swap ( d1 DATE, d2 DATE ) IS BEGIN NULL; END; PROCEDURE swap ( b1 BOOLEAN, b2 BOOLEAN ) IS BEGIN NULL; END; BEGIN -- balance swap(num1, num2); RETURN x; END balance; BEGIN -- enclosing procedure swap NULL; END swap; BEGIN -- anonymous block NULL; END; -- anonymous block /
swap(num1, num2); * ERROR at line 33: ORA-06550: line 33, column 7: PLS-00306: wrong number or types of arguments in call to 'SWAP' ORA-06550: line 33, column 7: PL/SQL: Statement ignored
PL/SQL lets you overload nested subprograms, package subprograms, and type methods. You can use the same name for several different subprograms if their formal parameters differ in name, number, order, or data type family. (A data type family is a data type and its subtypes. For the data type families of predefined PL/SQL data types, see PL/SQL Predefined Data Types. For information about user-defined PL/SQL subtypes, see "User-Defined PL/SQL Subtypes" .) If formal parameters differ only in name, then you must use named notation to specify the corresponding actual parameters. (For information about named notation, see "Positional, Named, and Mixed Notation for Actual Parameters" .)
Example 9-26 defines two subprograms with the same name, initialize . The procedures initialize different types of collections. Because the processing in the procedures is the same, it is logical to give them the same name.
You can put the two initialize procedures in the same block, subprogram, package, or type body. PL/SQL determines which procedure to invoke by checking their formal parameters. The version of initialize that PL/SQL uses depends on whether you invoke the procedure with a date_tab_typ or num_tab_typ parameter.
For an example of an overloaded procedure in a package, see Example 11-9.
Example 9-26 Overloaded Subprogram
DECLARE TYPE date_tab_typ IS TABLE OF DATE INDEX BY PLS_INTEGER; TYPE num_tab_typ IS TABLE OF NUMBER INDEX BY PLS_INTEGER; hiredate_tab date_tab_typ; sal_tab num_tab_typ; PROCEDURE initialize (tab OUT date_tab_typ, n INTEGER) IS BEGIN DBMS_OUTPUT.PUT_LINE('Invoked first version'); FOR i IN 1..n LOOP tab(i) := SYSDATE; END LOOP; END initialize; PROCEDURE initialize (tab OUT num_tab_typ, n INTEGER) IS BEGIN DBMS_OUTPUT.PUT_LINE('Invoked second version'); FOR i IN 1..n LOOP tab(i) := 0.0; END LOOP; END initialize; BEGIN initialize(hiredate_tab, 50); initialize(sal_tab, 100); END; /
Invoked first version Invoked second version
You can overload subprograms if their formal parameters differ only in numeric data type. 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 be more precise.
To avoid problems or unexpected results when passing parameters to such overloaded subprograms:
PL/SQL looks for matching numeric parameters in this order:
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 data type before passing it to the SQRT function.
If PL/SQL must convert a parameter to another data type, it first tries to convert it to a higher data type. For example:
You cannot overload these subprograms:
PROCEDURE s (p IN VARCHAR2) IS . PROCEDURE s (p OUT VARCHAR2) IS .
PROCEDURE s (p INTEGER) IS . PROCEDURE s (p REAL) IS .
FUNCTION f (p INTEGER) RETURN BOOLEAN IS . FUNCTION f (p INTEGER) RETURN INTEGER IS .
The PL/SQL compiler catches overload errors as soon as it determines that it cannot tell which subprogram was invoked. When subprograms have identical headings, the compiler catches the overload error when you try to compile the subprograms themselves (if they are nested) or when you try to compile the package specification that declares them. Otherwise, the compiler catches the error when you try to compile an ambiguous invocation of a subprogram.
When you try to compile the package specification in Example 9-27, which declares subprograms with identical headings, you get compile-time error PLS-00305.
Although the package specification in Example 9-28 violates the rule that you cannot overload subprograms whose formal parameters differ only in subtype, you can compile it without error.
However, when you try to compile an invocation of pkg2 . s , as in Example 9-29, you get compile-time error PLS-00307.
Suppose that you correct the overload error in Example 9-28 by giving the formal parameters of the overloaded subprograms different names, as in Example 9-30.
Now you can compile an invocation of pkg2 . s without error if you specify the actual parameter with named notation, as in Example 9-31. (If you specify the actual parameter with positional notation, as in Example 9-29, you still get compile-time error PLS-00307.)
The package specification in Example 9-32 violates no overload 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 9-33.
When trying to determine which subprogram was invoked, if the PL/SQL compiler implicitly converts one parameter to a matching type, then the compiler looks for other parameters that it can implicitly convert to matching types. If there is more than one match, then compile-time error PLS-00307 occurs, as in Example 9-34.
Example 9-27 Overload Error Causes Compile-Time Error
CREATE OR REPLACE PACKAGE pkg1 AUTHID DEFINER IS PROCEDURE s (p VARCHAR2); PROCEDURE s (p VARCHAR2); END pkg1; /
Example 9-28 Overload Error Compiles Successfully
CREATE OR REPLACE PACKAGE pkg2 AUTHID DEFINER IS SUBTYPE t1 IS VARCHAR2(10); SUBTYPE t2 IS VARCHAR2(10); PROCEDURE s (p t1); PROCEDURE s (p t2); END pkg2; /
Example 9-29 Invoking Subprogram in Example 9-28 Causes Compile-Time Error
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER IS a pkg2.t1 := 'a'; BEGIN pkg2.s(a); -- Causes compile-time error PLS-00307 END p; /
Example 9-30 Correcting Overload Error in Example 9-28
CREATE OR REPLACE PACKAGE pkg2 AUTHID DEFINER IS SUBTYPE t1 IS VARCHAR2(10); SUBTYPE t2 IS VARCHAR2(10); PROCEDURE s (p1 t1); PROCEDURE s (p2 t2); END pkg2; /
Example 9-31 Invoking Subprogram in Example 9-30
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER IS a pkg2.t1 := 'a'; BEGIN pkg2.s(p1=>a); -- Compiles without error END p; /
Example 9-32 Package Specification Without Overload Errors
CREATE OR REPLACE PACKAGE pkg3 AUTHID DEFINER IS PROCEDURE s (p1 VARCHAR2); PROCEDURE s (p1 VARCHAR2, p2 VARCHAR2 := 'p2'); END pkg3; /
Example 9-33 Improper Invocation of Properly Overloaded Subprogram
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER IS a1 VARCHAR2(10) := 'a1'; a2 VARCHAR2(10) := 'a2'; BEGIN pkg3.s(p1=>a1, p2=>a2); -- Compiles without error pkg3.s(p1=>a1); -- Causes compile-time error PLS-00307 END p; /
Example 9-34 Implicit Conversion of Parameters Causes Overload Error
CREATE OR REPLACE PACKAGE pack1 AUTHID DEFINER AS PROCEDURE proc1 (a NUMBER, b VARCHAR2); PROCEDURE proc1 (a NUMBER, b NUMBER); END; / CREATE OR REPLACE PACKAGE BODY pack1 AS PROCEDURE proc1 (a NUMBER, b VARCHAR2) IS BEGIN NULL; END; PROCEDURE proc1 (a NUMBER, b NUMBER) IS BEGIN NULL; END; END; / BEGIN pack1.proc1(1,'2'); -- Compiles without error pack1.proc1(1,2); -- Compiles without error pack1.proc1('1','2'); -- Causes compile-time error PLS-00307 pack1.proc1('1',2); -- Causes compile-time error PLS-00307 END; /
A recursive subprogram invokes itself. Recursion is a powerful technique for simplifying an algorithm.
A recursive subprogram must have at least two execution paths—one leading to the recursive invocation and one leading to a terminating condition. Without the latter, recursion continues until PL/SQL runs out of memory and raises the predefined exception STORAGE_ERROR .
In Example 9-35, the function implements the following recursive definition of n factorial ( n! ), the product of all integers from 1 to n :
n! = n * (n - 1)!
In Example 9-36, the function returns the n th Fibonacci number, which is the sum of the n -1st and n -2nd Fibonacci numbers. The first and second Fibonacci numbers are zero and one, respectively.
The function in Example 9-36 is a good candidate for result caching. For more information, see "Result-Cached Recursive Function" .
Each recursive invocation of a subprogram creates an instance of each item that the subprogram declares and each SQL statement that it executes.
A recursive invocation inside a cursor FOR LOOP statement, or between an OPEN or OPEN FOR statement and a CLOSE statement, opens another cursor at each invocation, which might cause the number of open cursors to exceed the limit set by the database initialization parameter OPEN_CURSORS .
Example 9-35 Recursive Function Returns n Factorial (n!)
CREATE OR REPLACE FUNCTION factorial ( n POSITIVE ) RETURN POSITIVE AUTHID DEFINER IS BEGIN IF n = 1 THEN -- terminating condition RETURN n; ELSE RETURN n * factorial(n-1); -- recursive invocation END IF; END; / BEGIN FOR i IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE(i || '! = ' || factorial(i)); END LOOP; END; /
1! = 1 2! = 2 3! = 6 4! = 24 5! = 120
Example 9-36 Recursive Function Returns nth Fibonacci Number
CREATE OR REPLACE FUNCTION fibonacci ( n PLS_INTEGER ) RETURN PLS_INTEGER AUTHID DEFINER IS fib_1 PLS_INTEGER := 0; fib_2 PLS_INTEGER := 1; BEGIN IF n = 1 THEN -- terminating condition RETURN fib_1; ELSIF n = 2 THEN RETURN fib_2; -- terminating condition ELSE RETURN fibonacci(n-2) + fibonacci(n-1); -- recursive invocations END IF; END; / BEGIN FOR i IN 1..10 LOOP DBMS_OUTPUT.PUT(fibonacci(i)); IF i < 10 THEN DBMS_OUTPUT.PUT(', '); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(' . '); END; /
0, 1, 1, 2, 3, 5, 8, 13, 21, 34 .
A subprogram has side effects if it changes anything except the values of its own local variables. For example, a subprogram that changes any of the following has side effects:
Side effects can prevent the parallelization of a query, yield order-dependent (and therefore, indeterminate) results, or require that package state be maintained across user sessions.
Minimizing side effects is especially important when defining a result-cached function or a stored function for SQL statements to invoke.
Oracle Database Development Guide for information about controlling side effects in PL/SQL functions invoked from SQL statements
When a PL/SQL function has the RESULT_CACHE option, its results are cached so sessions can reuse these results when available.
Oracle Database automatically detects all data sources (tables and views) that are queried while a result-cached function is running. If changes to any of these data sources are committed, the cached result becomes invalid across all instances. The best candidates for result-caching are functions that are invoked frequently but depend on information that changes infrequently or never.
A result object is the result of a query or result cached function execution. A temp object is the result of a query or result-cached function execution that exceeds the limit set by the multiplication of the RESULT_CACHE_MAX_SIZE by RESULT_CACHE_MAX_RESULT parameters. Temp objects are temporary segments stored in the temporary tablespace defined for the SYS user.
You can view the result and temp objects together by joining the V$RESULT_CACHE_OBJECTS using the type Temp for temp object and type Result for result objects.
SELECT rc1.NAME, rc2.STATUS, rc3.STATUS, rc2.BLOCK_COUNT FROM V$RESULT_CACHE_OBJECTS rc1, V$RESULT_CACHE_OBJECTS rc2 WHERE rc1.TYPE = 'Result' AND rc2.TYPE = 'Temp' AND rc1.CACHE_KEY = rc2.CACHE_KEY;
The RESULT_CACHE_MAX_TEMP_SIZE parameter sets the maximum amount of temporary tablespace that the result cache can consume in a PDB.
The result cache usage is optimized for best performance based on changes in the application workload.
Before fetching a cached result from a remote instance, the database uses heuristics to determine if it is more cost efficient to recompute the result on the local instance.
Oracle Database tracks recently used result-cached functions. Using this history, the database only caches a result-cached function and arguments pair if it has seen it x times in recent history, where x is set by the initialization parameter RESULT_CACHE_EXECUTION_THRESHOLD . Assuming the default value of 2, the result is cached on the second execution and reused on the third execution.
You can assess the health of your result cache by running the following query. It shows the distribution of the reuse rate of cached functions. If you notice a majority of these results have a scan count of 0, consider increasing the value of the RESULT_CACHE_EXECUTION_THRESHOLD by 1 or 2.
SELECT SCAN_COUNT, COUNT(CACHE_KEY) FROM V$RESULT_CACHE_OBJECTS WHERE NAMESPACE = 'PLSQL' GROUP BY SCAN_COUNT;