Oracle - no function with name X exists in this sc

问题:

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?

回答1:

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;