Oracle - there is no function named X in this area

Question

The function explicitly exists, because I can go to it using SQL Developer, and it compiles everything fine, but when I try to use the function with or without a call, it throws:

Error (36.24): PLS-00222: there is no function with the name "x" in this function

This is what 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 what I call it:

 messaget := testfunction(somevalue); 

where both messageT and some value are declared as type varchar2.

Are cursors inside a function or something like that forbidden?

Answer 1

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

you code does not check either lines or duplicate lines. you can simplify this for

 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;