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.

There's plenty wrong with this stored proc. For one thing, why open a ref cursor multiple times in a loop? All you'll get is the results for the last iteration of the loop, assuming there is a last iteration. How are tables table1 and table2 related? Why does your cursor c not select the id column from table1? It gives you a bunch of max values of cde, grouped by id, without telling you which id each max value corresponds to. Also, your parameter named out is an IN parameter: if you want to return it from your proc, you have to declare it out out sys_refcursor. — Luke Woodward
– Luke Woodward, Commented Oct 15, 2020 at 19:59
But the single biggest question is why bother with looping over a cursor at all? I strongly suspect that the entire body of this stored proc could be replaced with a single query which you open the output ref cursor for. — Luke Woodward
– Luke Woodward, Commented Oct 15, 2020 at 20:01

B Cheng · Accepted Answer · 2020-10-15 17:29:34Z

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:

both table1 and table2 have been defined with what I think are the columns
function is defined at schema level
local variable v_cde is now defined
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;

Collectives™ on Stack Overflow

How to use ref_cursor with user defined function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related