-
Iterate through a ref cursor??????
I have a package that returns a ref cursor. For example:
create or replace package test as
type gencursor is ref cursor;
procedure mytest(
x out gencursor);
end test;
/
create or replace package body test as
procedure mytest(
x out gencursor) as
rc gencursor;
begin
open rc for
select a,b,c,d from test_tables;
return;
end mytest;
end test;
/
Then in SQLPlus I have
declare
x test.gencursor;
begin
test.mytest(x);
for v1 in x loop
dbms_output.put_line(x.a||' '||x.b||' '||x.c||' '||x.d);
end loop;
end;
/
for v1 in x loop
*
ERROR at line 5:
ORA-06550: line 5, column 11:
PLS-00221: 'X' is not a procedure or is undefined
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
How do I iterate through the cursor that is being passed back from my procedure???
Thanks for your help.
-
correction
That should say
open x for
select a,b,c,d from test_tables;
Thanks again.
-
example
Code:
DECLARE
type ref_cursor is ref cursor;
c1 ref_cursor;
cursor ch_cursor is select co_id, ch_seqno, ch_status, userlastmod, entdate
from contract_history ch1
where rownum = 1;
r_contract_histroy ch_cursor%rowtype;
fr_entdate CONSTANT DATE := trunc(sysdate - 7);
to_entdate CONSTANT DATE := trunc(sysdate - 1);
BEGIN
OPEN c1 for 'select co_id, ch_seqno, ch_status, userlastmod, entdate
from (select co_id, ch_seqno, ch_status, userlastmod, entdate,
dense_rank() over (partition by co_id order by ch_seqno desc) rango
from contract_history
where co_id < 11
and trunc(entdate) <= :to_entdate) iv_ch
where rango = 1
or (ch_seqno = 1 and trunc(entdate) <= :to_entdate)
order by co_id, ch_seqno' using to_entdate, to_entdate;
LOOP
FETCH c1 into r_contract_histroy;
EXIT when c1%notfound;
dbms_output.put_line(r_contract_histroy.co_id || r_contract_histroy.ch_seqno ||
r_contract_histroy.ch_status || r_contract_histroy.userlastmod ||
r_contract_histroy.entdate);
END LOOP;
CLOSE c1;
END;
/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|