Re: Standalone Function

Jun,
Just like the error message says, you are updating the database and so cannot use it in a SELECT statement. You will need to invoke it in PL/SQL like this:

DECLARE
   return_value NUMBER(1);
BEGIN
   return_value := canceltest('WN.D.18065824');    IF return_value = 1 THEN

      COMMIT;
    ELSE
       ROLLBACK;
    END IF;
END;
/

Marc Perkowitz
MTP Systems Consulting, Ltd.

In a message dated 5/4/00 4:35:14 PM Central Daylight Time, jfeng_at_netsol.com writes:

<< 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 >> Received on Thu May 04 2000 - 16:54:03 CDT