oracletns中不存在名称为,Oracle-该范围中不存在名称为X的函数

The function is clearly there, because I can navigate to it using SQL Developer and it compiles all fine, but when I try to use the function with or without "call", it throws:

Error(36,24): PLS-00222: no function with name 'x' exists in this

scope

This is how the function looks like:

create or replace function testfunction

(

somevalue in varchar2

)

return varchar2

AS

cursor testcursor IS

select column1, column2 from table1 t

where t.column1 = somevalue;

testcursorrec testcursor %rowtype;

messaget VARCHAR2(500);

begin

open testcursor ;

fetch testcursor into testcursorrec ;

close testcursor ;

messaget := testcursor.column1;

return messaget ;

end;

This is how I'm calling it:

messaget := testfunction(somevalue);

where both messageT and somevalue are declared as varchar2 type.

Are cursors not allowed inside function or something like that?

解决方案

the error would be messaget := testcursor.column1; as the cursor is closed by then (you should just use testcursorrec.column2.

you're code isn't checking for no rows, nor duplicate rows. you can simplify this to

create or replace function testfunction

(

somevalue in table1.column1%type

)

return table1.column2%type

AS

messaget table1.column2%type; -- use %type where possible.

begin

select t.column2

into messaget

from table1 t

where t.column1 = somevalue

and rownum = 1;--only if you dont care if theres 2+ rows.

return messaget;

exception

when no_data_found

then

return null; -- if you want to ignore no rows.

end;