Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Standalone Function
Hi,
If you are using 8i then you can solve the problem making the function running in autonomous transaction:
CREATE OR REPLACE FUNCTION Canceltest(aExternalReference CHAR)
RETURN NUMBER AS
PRAGMA AUTONOMOUS_TRANSACTION;
.......... .......... ..........
HTH
Dima
> 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-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Fri May 05 2000 - 03:28:46 CDT