How to call a function in a cursor, which is a part of another procedure in oracle

Question

I have a stored procedure like this

create or replace procedure A is
  procedure a1 is
  ......
  end;

  procedure a2 is
   cursor c1 as
    select a,b,(select f1(x,y) var_temp from dual)data from table_a; -- error here says
                       --Error: PLS-00231: function 'f1' may not be used in SQL
   begin 
      ......
   end;

  function f1(x varchar2,y varchar2) return varchar2 is
  .....
  end;
begin
....................
end;

I want the cursor c1 to return the data using f1 function.. But it says

Error: PLS-00231: function 'f1' may not be used in SQL..

Creating a package will solve this but i have to do it in a procedure only...

Answer 1

Problem is, as the error says, that you can't use the function defined in the anonymous block in a SQL statement and that the function is used before it's defined.

What you can do is move the definition before use and get the data from cursor as is and apply function on the values while looping:

create or replace procedure A is
  procedure a1 is
  ......
  end;

  function f1(x varchar2,y varchar2) return varchar2 is
  .....
  end;

  procedure a2 is
   cursor c1 as
    select a,b from table_a;
   begin
       for i in c1 loop
           -- use f1(i.a, i.b) here
           dbms_output.put_line(f1(i.a, i.b));
       end loop;
   end;
begin
....................
end;