Re: WHAT IS ORA-6571
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: +441904435196Received on Mon Jul 22 1996 - 00:00:00 CEST