Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Standalone Function

Re: Standalone Function

From: Dima <dima_at_cit.org.by>
Date: Fri, 5 May 2000 11:28:46 +0300
Message-Id: <10488.105023@fatcity.com>


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;

..........
..........
..........

END;
/

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US