Oracle7 Server Application Developer's Guide

Calling Stored Functions from SQL Expressions

You can include user-written PL/SQL functions in SQL expressions. (You must be using PL/SQL release 2.1 or greater.) By using PL/SQL functions in SQL statements, you can do the following:

Increase user productivity by extending SQL. Expressiveness of the SQL statement increases where activities are too complex, too awkward, or unavailable with SQL.

Increase query efficiency. Functions used in the WHERE clause of a query can filter data using criteria that would otherwise have to be evaluated by the application.

Manipulate character strings to represent special datatypes (for example, latitude, longitude, or temperature).

Provide parallel query execution. If the query is parallelized, SQL statements in your PL/SQL function may be executed in parallel also (using the parallel query option).

Using PL/SQL Functions

PL/SQL functions must be created as top-level functions or declared within a package specification before they can be named within a SQL statement. Stored PL/SQL functions are used in the same manner as built-in Oracle functions (such as SUBSTR or ABS). PL/SQL functions can be placed wherever an Oracle function can be placed within a SQL statement; that is, wherever expressions can occur in SQL.

For example, they can be called from the following:

the select list of the SELECT command

the condition of the WHERE and HAVING clause

the CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses

the VALUES clause of the INSERT command

the SET clause of the UPDATE command

However stored PL/SQL functions cannot be called from a CHECK constraint clause of a CREATE or ALTER TABLE command or be used to specify a default value for a column. These situations require an unchanging definition.

Note: Unlike functions, which are called as part of an expression, procedures are called as statements. Therefore, PL/SQL procedures are not directly callable from SQL statements. However, functions called from a PL/SQL statement or referenced in a SQL expression can call a PL/SQL procedure.

Syntax

Use the following syntax to reference a PL/SQL function from SQL:

[[schema.]package.]function_name[@dblink][(param_1...param_n)]

For example, to reference a function that you have created that is called MY_FUNC, in the MY_FUNCS_PKG package, in the SCOTT schema, and that takes two numeric parameters, you could call it as

SELECT scott.my_funcs_pkg.my_func(10,20) from dual

Naming Conventions

If only one of the optional schema or package names is given, the first identifier can be either a schema name or a package name. For example, to determine whether PAYROLL in the reference PAYROLL.TAX_RATE is a schema or package name, Oracle proceeds as follows:

Oracle first checks for the PAYROLL package in the current schema.

If a PAYROLL package is not found, Oracle looks for a schema named PAYROLL that contains a top-level TAX_RATE function. If the TAX_RATE function is not found in the PAYROLL schema, an error message is returned.

If the PAYROLL package is found in the current schema, Oracle looks for a TAX_RATE function in the PAYROLL package. If a TAX_RATE function is not found in the PAYROLL package, an error message is returned.

You can also refer to a stored top-level function using any synonym that you have defined for it.

Name Precedence

In SQL statements, the names of database columns take precedence over the names of functions with no parameters. For example, if schema SCOTT creates the following two objects:

CREATE TABLE emp(new_sal NUMBER ...);
CREATE FUNCTION new_sal RETURN NUMBER IS ...;

Then in the following two statements, the reference to NEW_SAL refers to the column EMP.NEW_SAL:

SELECT new_sal FROM emp;
SELECT emp.new_sal FROM emp;

To access the function NEW_SAL, you would enter the following:

SELECT scott.new_sal FROM emp;

Example

For example, to call the TAX_RATE PL/SQL function from schema SCOTT, execute it against the SS_NO and SAL columns in TAX_TABLE, and place the results in the variable INCOME_TAX, specify the following:

SELECT scott.tax_rate (ss_no, sal) 
	INTO income_tax
	FROM tax_table
	WHERE ss_no = tax_id;

Listed below are sample calls to PL/SQL functions that are allowed in SQL expressions.

circle_area(radius)
payroll.tax_rate(empno)
scott.payroll.tax_rate(dependents, empno)@ny

Arguments

To pass any number of arguments to a function, supply the arguments within the parentheses. You must use positional notation; named notation is not currently supported. For functions that do not accept arguments, omit the parentheses.

The argument's datatypes and the function's return type are limited to those types that are supported by SQL. For example, you cannot call a PL/SQL function that returns a PL/SQL BINARY_INTEGER from a SQL statement.

Using Default Values

The stored function gross_pay initializes two of its formal parameters to default values using the DEFAULT clause, as follows:

CREATE FUNCTION gross_pay

      (emp_id IN NUMBER,

       st_hrs IN NUMBER DEFAULT 40,

       ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS

...

When calling gross_pay from a procedural statement, you can always accept the default value of st_hrs. That is because you can use named notation, which lets you skip parameters, as in

IF gross_pay(eenum,ot_hrs => otime) > pay_limit THEN ...

However, when calling gross_pay from a SQL expression, you cannot accept the default value of st_hrs unless you accept the default value of ot_hrs. That is because you cannot use named notation.

Meeting Basic Requirements

To be callable from SQL expressions, a user-defined PL/SQL function must meet the following basic requirements:

It must be a stored function, not a function defined within a PL/SQL block or subprogram.

It must be a row function, not a column (group) function; that is, it cannot take an entire column of data as its argument.

All its formal parameters must be IN parameters; none can be an OUT or IN OUT parameter.

The datatypes of its formal parameters must be Oracle Server internal types such as CHAR, DATE, or NUMBER, not PL/SQL types such as BOOLEAN, RECORD, or TABLE.

Its return type (the datatype of its result value) must be an Oracle Server internal type.

For example, the following stored function meets the basic requirements:

CREATE FUNCTION gross_pay 
      (emp_id IN NUMBER, 
       st_hrs IN NUMBER DEFAULT 40, 
       ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS 
   st_rate  NUMBER; 
   ot_rate  NUMBER; 

BEGIN 
   SELECT srate, orate INTO st_rate, ot_rate FROM payroll 
      WHERE acctno = emp_id; 
   RETURN st_hrs * st_rate + ot_hrs * ot_rate; 
END gross_pay;

Controlling Side Effects

To execute a SQL statement that calls a stored function, the Oracle Server must know the purity level of the function. That is, the extent to which the function is free of side effects. In this context, side effects are references to database tables or packaged variables.

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 (which is not allowed). Therefore, the following rules apply to stored functions called from SQL expressions:

The function cannot modify database tables; therefore, it cannot execute an INSERT, UPDATE, or DELETE statement.

Functions that read or write the values of packaged variables cannot be executed remotely or in parallel.

Only functions called from a SELECT, VALUES, or SET clause can write the values of packaged variables.

The function cannot call another subprogram that breaks one of the foregoing rules. Also, the function cannot reference a view that breaks one of the foregoing rules. (Oracle replaces references to a view with a stored SELECT operation, which can include function calls.)

For standalone functions, Oracle can enforce these rules by checking the function body. However, the body of a packaged function is hidden; only its specification is visible. So, for packaged functions, you must use the pragma (compiler directive) RESTRICT_REFERENCES to enforce the rules.

The pragma tells the PL/SQL compiler to deny the packaged function read/write access to database tables, packaged variables, or both. If you try to compile a function body that violates the pragma, you get a compilation error.

Calling Packaged Functions

To call a packaged function from SQL expressions, you must assert its purity level by coding the pragma RESTRICT_REFERENCES in the package specification (not in the package body). The pragma must follow the function declaration but need not follow it immediately. Only one pragma can reference a given function declaration.

To code the pragma RESTRICT_REFERENCES, you use the syntax

PRAGMA RESTRICT_REFERENCES (

    function_name, WNDS [, WNPS] [, RNDS] [, RNPS]);

where:

WNDS means "writes no database state" (does not modify database tables)
WNPS means "writes no package state" (does not change the values of packaged variables)
RNDS means "reads no database state" (does not query database tables)
RNPS means "reads no package state" (does not reference the values of packaged variables)

You can pass the arguments in any order, but you must pass the argument WNDS. No argument implies another. For instance, RNPS does not imply WNPS.

In the example below, the function compound neither reads nor writes database or package state, so you can assert the maximum purity level. Always assert the highest purity level a function allows. That way, the PL/SQL compiler will never reject the function unnecessarily.

CREATE PACKAGE finance AS  -- package specification 
   ... 
   FUNCTION compound 
         (years  IN NUMBER, 
          amount IN NUMBER, 
          rate   IN NUMBER) RETURN NUMBER; 
   PRAGMA RESTRICT_REFERENCES (compound, WNDS, WNPS, RNDS, RNPS); 
END finance; 
 
CREATE PACKAGE BODY finance AS  --package body 
   ... 
   FUNCTION compound 
         (years  IN NUMBER, 
          amount IN NUMBER, 
          rate   IN NUMBER) RETURN NUMBER IS 

   BEGIN 
      RETURN amount * POWER((rate / 100) + 1, years); 
   END compound; 
                   -- no pragma in package body 
END finance;

Later, you might call compound from a PL/SQL block, as follows:

BEGIN 
   ...
    SELECT finance.compound(yrs,amt,rte)  -- function call       INTO interest       FROM accounts       WHERE acctno = acct_id;

Referencing Packages with an Initialization Part

Packages can have an initialization part, which is hidden in the package body. Typically, the initialization part holds statements that initialize public variables. In the following example, the SELECT statement initializes the public variable prime_rate:

CREATE PACKAGE loans AS

   prime_rate  REAL;  -- public packaged variable

...

END loans;

CREATE PACKAGE BODY loans AS

...

BEGIN  -- initialization part

   SELECT prime INTO prime_rate FROM rates;

END loans;

The initialization code is run only once--the first time the package is referenced. If the code reads or writes database state or package state other than its own, it can cause side effects. Moreover, a stored function that references the package (and thereby runs the initialization code) can cause side effects indirectly. So, to call the function from SQL expressions, you must use the pragma RESTRICT_REFERENCES to assert or imply the purity level of the initialization code.

To assert the purity level of the initialization code, you use a variant of the pragma RESTRICT_REFERENCES, in which the function name is replaced by a package name. You code the pragma in the package specification, where it is visible to other users. That way, anyone referencing the package can see the restrictions and conform to them.

To code the variant pragma RESTRICT_REFERENCES, you use the syntax

PRAGMA RESTRICT_REFERENCES (

    package_name, WNDS [, WNPS] [, RNDS] [, RNPS]);

where the arguments WNDS, WNPS, RNDS, and RNPS have the usual meaning.

In the example below, the initialization code reads database state and writes package state. However, you can assert WNPS because the code is writing the state of its own package, which is permitted. So, you assert WNDS, WNPS, RNPS--the highest purity level the function allows. (If the public variable prime_rate were in another package, you could not assert WNPS.)

CREATE PACKAGE loans AS

   PRAGMA RESTRICT_REFERENCES (loans, WNDS, WNPS, RNPS);

   prime_rate  REAL;

...

END loans;

CREATE PACKAGE BODY loans AS

...

BEGIN

   SELECT prime INTO prime_rate FROM rates;

END loans;

You can place the pragma anywhere in the package specification, but placing it at the top (where it stands out) is a good idea.

To imply the purity level of the initialization code, your package must have a RESTRICT_REFERENCES pragma for one of the functions it declares. From the pragma, Oracle can infer the purity level of the initialization code (because the code cannot break any rule enforced by a pragma). In the next example, the pragma for the function discount implies that the purity level of the initialization code is at least WNDS:

CREATE PACKAGE loans AS

...

   FUNCTION discount (...) RETURN NUMBER;

   PRAGMA RESTRICT_REFERENCES (discount, WNDS);

END loans;

...

To draw an inference, Oracle can combine the assertions of all RESTRICT_REFERENCES pragmas. For example, the following pragmas (combined) imply that the purity level of the initialization code is at least WNDS, RNDS:

CREATE PACKAGE loans AS

...

   FUNCTION discount (...) RETURN NUMBER;

   FUNCTION credit_ok (...) RETURN CHAR;

   PRAGMA RESTRICT_REFERENCES (discount, WNDS);

   PRAGMA RESTRICT_REFERENCES (credit_ok, RNDS);

END loans;

...

Avoiding Problems

To call a packaged function from SQL expressions, you must assert its purity level using the pragma RESTRICT_REFERENCES. However, if the package has an initialization part, the PL/SQL compiler might not let you assert the highest purity level the function allows. As a result, you might be unable to call the function remotely, in parallel, or from certain SQL clauses.

This happens when a packaged function is purer than the package initialization code. Remember, the first time a package is referenced, its initialization code is run. If that reference is a function call, any additional side effects caused by the initialization code occur during the call. So, in effect, the initialization code lowers the purity level of the function.

To avoid this problem, move the package initialization code into a subprogram. That way, your application can run the code explicitly (rather than implicitly during package instantiation) without affecting your packaged functions.

A similar problem arises when a packaged function is purer than a subprogram it calls. This lowers the purity level of the function. Therefore, the RESTRICT_REFERENCES pragma for the function must specify the lower purity level. Otherwise, the PL/SQL compiler will reject the function. In the following example, the compiler rejects the function because its pragma asserts RNDS but the function calls a procedure that reads database state:

CREATE PACKAGE finance AS 
   ... 
   FUNCTION compound (years  IN NUMBER, 
                      amount IN NUMBER) RETURN NUMBER; 
   PRAGMA RESTRICT_REFERENCES (compound, WNDS, WNPS, RNDS, RNPS); 
END finance; 
 
CREATE PACKAGE BODY finance AS 
   ... 
   FUNCTION compound (years  IN NUMBER, 
                      amount IN NUMBER) RETURN NUMBER IS 
      rate  NUMBER; 
      PROCEDURE calc_loan_rate (loan_rate OUT NUMBER) IS 
         prime_rate REAL; 
      BEGIN 
         SELECT p_rate INTO prime_rate FROM rates; 
         ... 
      END; 
   BEGIN 
      calc_loan_rate(rate); 
      RETURN amount * POWER((rate / 100) + 1, years); 
   END compound; 
END finance;

Overloading

PL/SQL lets you overload packaged (but not standalone) functions. That is, you can use the same name for different functions if their formal parameters differ in number, order, or datatype family.

However, a RESTRICT_REFERENCES pragma can apply to only one function declaration. So, a pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration. In the following example, the pragma applies to the second declaration of valid:

CREATE PACKAGE tests AS

   FUNCTION valid (x NUMBER) RETURN CHAR;

   FUNCTION valid (x DATE) RETURN CHAR;

   PRAGMA RESTRICT_REFERENCES (valid, WNDS);

...

Privileges Required

To call a PL/SQL function from SQL, you must either own or have EXECUTE privileges on the function. To select from a view defined with a PL/SQL function, you are required to have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view.

WNDS	means "writes no database state" (does not modify database tables)
WNPS	means "writes no package state" (does not change the values of packaged variables)
RNDS	means "reads no database state" (does not query database tables)
RNPS	means "reads no package state" (does not reference the values of packaged variables)