Re: Function 'myfunc' may not be used in SQL
Date: Tue, 07 Oct 2008 18:26:23 +0200
On Tue, 7 Oct 2008 08:29:58 -0700 (PDT), Kevin S <SearleK_at_googlemail.com> wrote:
>I have created a simple function for use in an update statement. The
> FUNCTION myfnc(p1 IN t1.c1%TYPE)
> RETURN VARCHAR2
> IF p1 = 3 THEN
> RETURN 'A';
> RETURN 'B';
> END IF;
> END myfnc;
>I use it in a procedure which exists in the same package as the
>function as follows
>set c1 = myfnc(c2);
>I am getting Function 'myfunc' may not be used in SQL.
>There is no db manipulation going on and I have tried using
>PRAGMA RESTRICT_REFERENCES (myfnc, WNDS, WNPS, RNDS, RNPS);
>but to no avail.
ALWAYS post your 4 digit version number. Most answers are version dependent.
If you don't want to include a version, consider the people responding are volunteers and won't keep track of you and your version. In that case, consider getting (paid) support from Metalink.
ALWAYS post an exactt error message, and ALWAYS look this error up on http://tahiti.oracle.com PRIOR to posting.
pragma restrict_references is obsolete since 9i. You need to use the DETERMINISTIC keyword now.
However, suggestion 4:
Why don't you just use built-ins like CASE and DECODE?
-- Sybrand Bakker Senior Oracle DBAReceived on Tue Oct 07 2008 - 11:26:23 CDT