Re: PL/SQL Package Functions in SELECT statements...

From: Stefan Langemalm <stla_at_gbg.ifsab.se>
Date: 1995/09/08
Message-ID: <42n50q$eb4_at_simba.lejonet.se>


Peter Derrick <pld> wrote:

>I have created a PL/SQL package containing several functions to convert column
>data as part of a view. When I try to use the functions in a simple query
>such as:
 

>select funcs.funcX(columnX) from dual;
 

>I get the following response from oracle:
 

>ORA-06571: Function FUNCX does not guarantee not to update database
> ...
>Peter Derrick.

This will do the trick for you:

CREATE PACKAGE funcs IS
FUNCTION funcX (

   columnX IN VARCHAR2 ) RETURN VARCHAR2; PRAGMA restrict_references(funcX, WNDS, WNPS); END; From Oracle help file:


Calling Stored Functions from SQL

A stored function is a user-defined PL/SQL function created with an Oracle tool and stored in the data dictionary. It is a database object, which can be referenced by any number of applications connected
to that database. There are two types of stored functions: packaged and standalone. Packaged functions are defined within a PL/SQL package;
standalone functions are defined independently.

Unlike functions, which are called as part of an expression, procedures
are called as statements. Therefore, procedures cannot be called directly from SQL expressions. However, you can call stored functions

from the SELECT, VALUES, SET, WHERE, START WITH, GROUP BY, HAVING, and

ORDER BY clauses--wherever expressions are allowed in a SQL statement.

Calling Syntax

To call a stored function from a SQL expression, you use the

following syntax:

    [schema.][package.]function[_at_dblink][(arg[, arg] ... )]

You must write the arguments (actual parameters) using positional notation; you cannot use named or mixed notation. In the following example, you call the standalone function "gross_pay," which is stored in a remote Oracle database:

    SELECT gross_pay_at_newyork(eenum,stime,otime) INTO pay FROM dual;

Using Default Values

The stored function "gross_pay" initializes two of its the 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.
  • Remote or parallelized functions cannot read or write the values of packaged variables.
  • 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 database 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 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 that a function allows. That way, the PL/SQL compiler will never reject the function unnecessarily.

    CREATE PACKAGE finance AS -- package specification

       interest  REAL;  -- public packaged variable 
       ... 
       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;

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; 
       ... 

    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 -- initialization part

       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.

Name Precedence

In SQL statements, the names of database columns take precedence over the names of parameterless functions. For example, if user "scott" executes the statements

    CREATE TABLE stats (rand_num NUMBER, ...);     CREATE FUNCTION rand_num RETURN NUMBER AS ...

then the following select-item refers to the column "rand_num":

    SELECT rand_num, ... INTO start_val, ...

        FROM stats WHERE ...

In this case, to call the stored function "rand_num," you must specify the schema, as follows:

    SELECT scott.rand_num, ... INTO start_val, ...

        FROM stats WHERE ...

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

  • these functions return 'T' or 'F' (for TRUE or FALSE) FUNCTION valid (x NUMBER) RETURN CHAR;
       FUNCTION valid (x DATE) RETURN CHAR; 
       PRAGMA RESTRICT_REFERENCES (valid, WNDS); 
       ... 

    END tests;

Stefan Langemalm
IFS Business Systems AB, SWEDEN
stla_at_gbg.ifsab.se Received on Fri Sep 08 1995 - 00:00:00 CEST

Original text of this message