Re: Function 'myfunc' may not be used in SQL

Kevin S <SearleK_at_googlemail.com> wrote:
> I have created a simple function for use in an update statement. The
> fuction is
>
> FUNCTION myfnc(p1 IN t1.c1%TYPE)
> RETURN VARCHAR2
> IS
> BEGIN
> IF p1 = 3 THEN
> RETURN 'A';
> ELSE
> RETURN 'B';
> END IF;
> END myfnc;
>
> I use it in a procedure which exists in the same package as the
> function as follows
>
> Update t1
> 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.
>
> Any suggestions?

The error you get is PLS-231, right?

Since you do not show the whole code, I'm reduced to guessing, but this looks like bug 516663 to me, which is described in Metalink Note 1033798.6.

Is function "myfnc" declared in the package spec?

If it is indeed that bug, there are two workarounds: - Declare the function in the package header. - Avoid using the function in an SQL statement.

In your example, you could declare a PL/SQL variable

   helpvar t1.c1%TYPE;

and use it like this:

   helpvar := myfnc(c2);
   UPDATE t1 SET c1 = helpvar;

Yours,
Laurenz Albe Received on Wed Oct 08 2008 - 09:53:45 CDT