How to use ref_cursor with user defined function?

Question

I written a code to return set of records as below

CREATE PROCEDURE test (from_dt date, to_dt date, out sys_refcursor)
as
    cursor c
    is
    select max(cde)
    from table1
    where to_char(dt, 'yyyymmdd') between from_dt and to_dt
    group by id;

begin
        for i in c
        loop
            select function(i.cde) into v_cde from dual;
            
            open out for select column1, v_cde, column2, column2 from table2
            where to_char(dt, 'yyyymmdd') between from_dt and to_dt;
        end loop;
end;

After execute the above procedure all records are retruned correctly except v_cde function return value. It returns same value for all records. I think cde value is not went under looping. How can i use function along with ref_cursor in loop?.

I anyone have suggestions, tell me.

Answer 1

The given code is not complete as Barbaros is attempting to show. Here is a different attempt at completing the code. Even then, the procedure test still does not compile but the error is now reduced to the ref cursor problem:

LINE/COL ERROR

---

16/13 PL/SQL: SQL Statement ignored 16/18 PLS-00361: IN cursor 'OUT' cannot be OPEN'ed

Changes to the example:

1. both table1 and table2 have been defined with what I think are the columns
2. function is defined at schema level
3. local variable v_cde is now defined
4. function is now defined at schema level

Here is the new example:

drop table table1;
drop table table2;

create table table1 (cde number, dt date, id number, from_dt date, to_dt date);

create table table2 (column1 number, v_cde number, column2 number, dt date, id number, from_dt date, to_dt date);

Rem This function returns the same numeric value given
create or replace function func1(arg1 number) return number is
  begin
    return arg1;
  end;
/
show errors;

CREATE or REPLACE PROCEDURE test (from_dt date, to_dt date, rf1 sys_refcursor)
as
    v_cde number;
    cursor c
    is
    select max(cde) cde
    from table1
    where to_char(dt, 'yyyymmdd') between from_dt and to_dt
    group by id;

begin
        for i in c loop
            v_cde := func1(i.cde);
            
            open rf1 for select column1, v_cde, column2, column2 from table2
            where to_char(dt, 'yyyymmdd') between from_dt and to_dt;
        end loop;
end;
/
show errors;