Re: PL/SQL Package Functions in SELECT statements...
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 bodyEND 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