Function 'myfunc' may not be used in SQL
Kevin S
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?
Aya the Vampire Slayer
>Hello,
>Any suggestions?
Use "decode" instead of "myfnc".
--
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators
"Keep your fingers off the lens." --Elton Byington, English Translator
sybr...@hccnet.nl
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
DA Morgan
If the function is in a package then there is no function named myfnc.
Try package_name.function_name.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Malcolm Dew-Jones
: 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
The SQL statement does not have access to the function unless it is
declared in the package (header) and referenced using its full name.
-- in package (header)
FUNCTION myfnc(p1 IN t1.c1%TYPE) RETURN VARCHAR2 ;
: Update t1
: set c1 = myfnc(c2);
Update t1
set c1 = myPackage.myfnc(c2);
Kevin S
Thank you for the responses to which I have the following comments.
To Aya the Vampire Slayer
Yes, decode would work but I want to create code which is easy to read
and maintain and where the business rules can be encapsulated in their
own modules.
To Sybrand
As soon as I left work I though about my posting and realised I had
omitted the version which would no doubt have been helpful to those
trying to respond. The Oracle version is 10.2.0.1.0.
I did not realise pragma restrict_references is obsolete so I will
check out the DETERMINISTIC keyword now.
WRT Case and Decode, as mentioned above they don't allow business
rules to be implemented in a modular fashion (rightly or wrongly, I am
trying to follow the Feuerstein doctrines).
To Daniel
I am already referencing the function using the
package_name.function_name format.
To Malcolm
The statement, I am calling the function from, is an update statement
in a separate procedure in the same package.
Once again thank you for taking the time to look at this. I am not
sure if the above points point to anything more concrete. In the
meantime I shall check out the DETERMINISTIC keyword.
K
Laurenz Albe
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
DA Morgan
> To Daniel
> I am already referencing the function using the
> package_name.function_name format.
Then what you posted is not your real code so no further
help is possible.
Kevin S
> Then what you posted is not your real code so no further
> help is possible.
Please note the sentence in my original posting which states 'I use it
in a procedure which exists in the same package as the function...'.
As such what I have posted is my real code.
Shakespeare
"Kevin S" <Sea...@googlemail.com> schreef in bericht
news:9816723e-24d2-42c6...@s1g2000prg.googlegroups.com...
No, because this is not:
Update t1
set c1 = myfnc(c2); <-- NO PREFIX, AND YOU SAY YOU USE A PREFIX
AND your error message is
Function 'myfunc' may not be used in SQL. <-- NOTE THE U IN MYFUNC
So this can't be your real code.....
Shakespeare
Kevin S
You have good eyes.
In copying and pasting my code and removing all the business specific
identifiers, I made a typo.
Doh!
Note to self: run the damn code before you post it.
