| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Standalone Function
Hi Jun,
Whenever we use a userdefined function as a embedded function in SQL we would have to guarantee that the function does not change any database state, package etc...because SELECT by definition/nature are supposed to be used as a read-only function.
For more details, I have enclosed the documentation from Oracle on this.
Regards
Rajagopal Venkataramany
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;
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.
----Original Message Follows----
From: "Feng, Jun" <jfeng_at_netsol.com>
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Subject: Standalone Function
Date: Thu, 04 May 2000 14:18:43 -0800
Hi,
I have a standalone function created as following:
CREATE OR REPLACE FUNCTION Canceltest(aExternalReference CHAR)
RETURN NUMBER AS vProductId TEST.productId%TYPE;
vProductRowId ROWID;
vAmount TEST.Amount%TYPE;
BEGIN
SELECT RowId , amount
INTO vProductRowId, vAmount
FROM TEST
WHERE ExternalReference = aExternalReference;
IF (vAmount > 30) THEN
UPDATE TEST
SET Amount = 999
WHERE RowId = vProductRowId;
END IF;
RETURN (1);
EXCEPTION WHEN OTHERS THEN RETURN (-1);
END Canceltest;
The function was created without error, but when I tried to use it I had following error:
SQL> select canceltest('WN.D.18065824') from dual; select canceltest('WN.D.18065824') from dual
*
ERROR at line 1:
ORA-06571: Function CANCELTEST does not guarantee not to update database
Please help me to fix this.
Thanks,
Jun
-- Author: Feng, Jun INET: jfeng_at_netsol.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Thu May 04 2000 - 17:34:57 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |