I think you can try it:TYPE udt_ref_cur IS REF CURSOR; PROCEDURE Get_Submission( io_curSubmissionDetailsData IN OUT udt_ref_cur, in_numSubmissionID IN NUMBER, -- NOT NULL in_numUserID IN NUMBER) -- NOT NULL IS v_sql varchar2(500); BEGIN v_sql := 'SELECT SD.policy_type_id,SD.county_code,SD.month_id,SD.inforce_num,SD.canceled_num,SD.nonrenewed_num,SD.h_canceled_num,SD.h_nonrenewed_num,SD.new_written_num,SD.exclude_wind_num,SD.total_dollar_num,SD.total_premium_num FROM IQUASR.SUBMISSION S,IQUASR.SUBMISSION_DETAILS SD WHERE S.submission_id = in_numSubmissionID AND S.submission_id = SD.submission_id (+)'; open io_curSubmissionDetailsData for v_sql; ..... -- Load the data for cur_Submission_Detail
Thanks for your response. I want to load cur_Submission_Detail cursor first, and check up lots of stuff for changing some parts of data in that cursor. Then pass (or convert) it to io_curSubmissionDetailsData cursor.
I'm not sure if you want to pass the whole cursor to another or just use one record to feed into target. Would you please explain more detail before we can help further on?
It is not possible to use one query to load the cursor which I like to have. So I want to create the structure for the cur_Submission_Detail cursor first. Then load data to cur_Submission_Detail cursor from different tables at various conditions. Finally pass the whole cur_Submission_Detail cursor to io_curSubmissionDetailsData which will return to the procedure loader. I don't know if there is a way to do it.Thanks.
I write a example which use table object replace ref cursor:SQL> create or replace type objectype as object (id int,zyz1 varchar2(50)); 2 /Type created.Elapsed: 00:00:00.81 SQL> create or replace type tabletype as table of objectype; 2 /Type created.Elapsed: 00:00:00.00 SQL> create or replace procedure testtable (tabcursor in out tabletype,tableid i n number) 2 as 3 cursor v_cursor is select id,zyz1 from test2 where id>tableid; 4 begin 5 for v_tempcursor in v_cursor loop 6 tabcursor.extend; 7 tabcursor(tabcursor.count) := objectype(v_tempcursor.id,v_tempcursor.zy z1); 8 end loop; 9 end; 10 /Procedure created.Elapsed: 00:00:01.33 SQL> commit;Commit complete.Elapsed: 00:00:00.20 SQL> set serveroutput on SQL> declare 2 v_table tabletype :=tabletype(); 3 i number :=0; 4 begin 5 testtable(v_table,20); 6 testtable(v_table,1); 7 loop 8 i := i+1; 9 dbms_output.put_line(';' || v_table(i).id || ';' || v_table(i).zyz1 | | ';'); 10 exit when i = v_table.count; 11 end loop; 12 end; 13 / ;62;; ;24;; ;62;; ;4;ryuy; ;24;;
Thanks ZhaoYangZhu! I have to return a cursor to the procedure loader (VB-COM, or Crystal Reports). Is it possible to pass the table object to a ref cursor?
PROCEDURE Get_Submission(
io_curSubmissionDetailsData IN OUT udt_ref_cur,
in_numSubmissionID IN NUMBER, -- NOT NULL
in_numUserID IN NUMBER) -- NOT NULL
IS
v_sql varchar2(500);
BEGIN
v_sql := 'SELECT SD.policy_type_id,SD.county_code,SD.month_id,SD.inforce_num,SD.canceled_num,SD.nonrenewed_num,SD.h_canceled_num,SD.h_nonrenewed_num,SD.new_written_num,SD.exclude_wind_num,SD.total_dollar_num,SD.total_premium_num FROM IQUASR.SUBMISSION S,IQUASR.SUBMISSION_DETAILS SD WHERE S.submission_id = in_numSubmissionID AND S.submission_id = SD.submission_id (+)';
open io_curSubmissionDetailsData for v_sql; ..... -- Load the data for cur_Submission_Detail
2 /Type created.Elapsed: 00:00:00.81
SQL> create or replace type tabletype as table of objectype;
2 /Type created.Elapsed: 00:00:00.00
SQL> create or replace procedure testtable (tabcursor in out tabletype,tableid i
n number)
2 as
3 cursor v_cursor is select id,zyz1 from test2 where id>tableid;
4 begin
5 for v_tempcursor in v_cursor loop
6 tabcursor.extend;
7 tabcursor(tabcursor.count) := objectype(v_tempcursor.id,v_tempcursor.zy
z1);
8 end loop;
9 end;
10 /Procedure created.Elapsed: 00:00:01.33
SQL> commit;Commit complete.Elapsed: 00:00:00.20
SQL> set serveroutput on
SQL> declare
2 v_table tabletype :=tabletype();
3 i number :=0;
4 begin
5 testtable(v_table,20);
6 testtable(v_table,1);
7 loop
8 i := i+1;
9 dbms_output.put_line(';' || v_table(i).id || ';' || v_table(i).zyz1 |
| ';');
10 exit when i = v_table.count;
11 end loop;
12 end;
13 /
;62;;
;24;;
;62;;
;4;ryuy;
;24;;
I have to return a cursor to the procedure loader (VB-COM, or Crystal Reports). Is it possible to pass the table object to a ref cursor?