Re: Package: Simple question from novice

From: AMARENDRA B NETTEM <nettama_at_charlie.cns.iit.edu>
Date: 1997/12/18
Message-ID: <3499597A.2B7C_at_charlie.cns.iit.edu>#1/1

Magnus S. Petersen wrote:
>
> I have just started with packages in Personal Oracle7.
> First i wrote a standalone function Addfive, called it from SQL*Plus
> and it works fine.
> Then i deleted the function and put it in a package.
> Both the package specification and body compiles fine.
> Now it does not work, because i get the following error:
> ORA-06571:
> Function ADDFIVE does not guarantee not to update database
> when i execute the following select statement:
> select test_package.addfive(10) from sys.dual;
>
> The package is as follows:
> PACKAGE TEST_PACKAGE
> IS
> FUNCTION AddFive(Amount IN NUMBER)
> RETURN NUMBER;
> END;
>
> PACKAGE BODY TEST_PACKAGE
> IS
> FUNCTION AddFive(Amount IN NUMBER)
> RETURN NUMBER
> IS
> BEGIN
> RETURN(Amount + 5);
> END AddFive;
> END Test_Package;
>
> Can anyone tell me what i am doing wrong. It should be so simple
> Regards
> Magnus

Hi,
You have to give the guarantee to the oracle that the function within the package is not manipulating the database. add the following line specification
PRAGMA RESTRICT_REFERENCES(function_name, WNDS, RNDS, WNPS, RNPS);

WNDS -- WRITE  NO DATABASE STATE
RNDS -- READ   NO DATABASE STATE
WNPS -- WRITE  NO PACKAGE STATE
RNPS -- READ   NO PACKAGE STATE

-----------------

he package is as follows:
PACKAGE     TEST_PACKAGE

IS
  FUNCTION AddFive(Amount IN NUMBER)
  RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(AddFive, WNDS, RNDS, WNPS, RNPS); END TEST_PACKAGE;
/

PACKAGE BODY TEST_PACKAGE
IS
  FUNCTION AddFive(Amount IN NUMBER)
  RETURN NUMBER
  IS
  BEGIN
      RETURN(Amount + 5);
  END AddFive;
END Test_Package;
/

Hope This Helps

AMARENDRA

-- 
******************************************************************************
AMARENDRA B NETTEM			5039 N E River Road, Apt. 1A
Oracle Certified DBA (OCP)		NORRIDGE, IL 60656
Whittman-Hart Inc., 
311 South Wacker Drive, Suite 3500
Chicago, IL 60606.  

Ph.No. (708) 583 9870 (H)  
       (312) 913 6758 (W)  

 E-mail:nettama_at_charlie.cns.iit.edu,
        anettem_at_whittman-hart.com

Homepage: http://www.iit.edu/~nettama

****************************************************************************
Opinions are mine and do not necessarily reflect those of Whittman-hart
Inc.

Received on Thu Dec 18 1997 - 00:00:00 CET