Re: WHAT IS ORA-6571

From: Doug Winter <doug.winter_at_info-com.com>
Date: 1996/07/22
Message-ID: <31F377D5.7BE_at_info-com.com>#1/1


andreas_at_orca.fhcrc.org wrote:
>
> > Bob Yeh <yehr_at_itsi.disa.mil> writes:
> > Hi,
> >
> > I got the following message when execute a function in the SQL*PLUS.
> > The function gets the next sequence number from a sequence.
> >
> >
> > ERROR at line 1:
> > ORA-06571: Function GETSEQUENCE does not guarantee not to update database.
> >
> > Can soneone tell me what this is.
> > The message book does not have ORA-6571 to ORA-5680.
> >
> > Please E-mail me if possible.
> >
> > Thanks. Bob
> >
> >>>>
> this happened to me when i had a lock in my function, but not with a procedure
> I believe you need a function that calls a procedure to get around it....

You need to use the PRAGMA RESTRICT_REFERENCES. What this does is to tell the PL/SQL compiler what your procedure updates, and what it doesn't.

For instance

PACKAGE FOO IS
FUNCTION BAR(A NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(BAR,WNDS,RNDS,RNPS,WNPS); END; The pragma indicates that PROCEDURE FOO

WNDS	Writes No Database State
RNDS	Reads No Database State
RNPS	Reads No Package State
WNPS	Writes No Package State

Which makes for a function with no side-effects, which can then be used in a query.

To use your GETSEQUENCE function you need to

PRAGMA RESTRICT_REFERENCES(GETSEQUENCE, WNDS, WNPS); and you can use it.

Cheers,

Doug.

BTW this is in the manual somewhere, but it takes some finding.

--
Doug Winter                Office: +441904435198
Technical Manager          doug.winter_at_info-com.com
Infocom (UK) Ltd           Fax: +441904435196
Received on Mon Jul 22 1996 - 00:00:00 CEST

Original text of this message