Function 'myfunc' may not be used in SQL

#1

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?

#2

Kevin S <SearleK (AT) googlemail (DOT) com> wa:

Quote:

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?

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

#3

Kevin S <SearleK (AT) googlemail (DOT) com> wa:

Quote:

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?

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

#4

Kevin S <SearleK (AT) googlemail (DOT) com> wa:

Quote:

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?

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

#5

Kevin S <SearleK (AT) googlemail (DOT) com> wa:

Quote:

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?

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

#6

On Tue, 7 Oct 2008 08:29:58 -0700 (PDT), Kevin S
<SearleK (AT) googlemail (DOT) com> wrote:

Quote:

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

#7

On Tue, 7 Oct 2008 08:29:58 -0700 (PDT), Kevin S
<SearleK (AT) googlemail (DOT) com> wrote:

Quote:

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

#8

On Tue, 7 Oct 2008 08:29:58 -0700 (PDT), Kevin S
<SearleK (AT) googlemail (DOT) com> wrote:

Quote:

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

#9

On Tue, 7 Oct 2008 08:29:58 -0700 (PDT), Kevin S
<SearleK (AT) googlemail (DOT) com> wrote:

Quote:

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

#10

Kevin S wrote:

Quote:

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?

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
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org