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

On Tue, 7 Oct 2008 08:29:58 -0700 (PDT), Kevin S <SearleK_at_googlemail.com> wrote:

>Hello,
>
>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?

Suggestion 1:
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.

Suggestion 2:
ALWAYS post an exactt error message, and ALWAYS look this error up on http://tahiti.oracle.com PRIOR to posting.

Suggestion 3:
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 DBA