Re: can't invoke function (PL/SQL)

Vijay Vardhineni wrote:
>
> If you want to use a pl/sql function in you SQL statements you need to use
>
> PRAGMA RESTRICT_REFERENCES ....
> in you function. For more info, see any PL/SQL book.
>
> --
> Vijaya Kumar Vardhineni
> Certified Oracle DBA, EDS
> Plano, TX
>
> Michael Agbaglo <byteshif_at_cs.tu-berlin.de> wrote in article
> <01bc408f$6ffa58f0$0200000a_at_pitfall>...
> > I compiled the function below in Oracle for Win95
> >
> > CREATE OR REPLACE FUNCTION IsNumeric( C IN CHAR ) RETURN BOOLEAN
> > IS
> > BEGIN
> > RETURN ( C >= '0' AND C <= '9' );
> > END;
> >
> > I verified it with ...
> >
> > SELECT object_type, object_name,status
> > FROM user_objects
> > WHERE object_type = 'FUNCTION'
> >
> > --> valid !
> >
> > I did a
> >
> > SELECT IsNumeric('p') FROM DUAL;
> >
> > and got an error: no such column ... or similar...
> >
> > WHY?
> >

RESTRICT_REFERENCES is only required inside a packaged function/procedure. It is not required within a standalone procedure such as this appears to be.
It is more likely that the function was being invoked by a different user from the one that created the function. In this case, the function name must be qualified by the creator name on use and, of course, the user must have EXECUTE permisiion on the function.

Chrysalis. Received on Sun Apr 06 1997 - 00:00:00 CST