Multiple Cursors into 1 table

[Guest_imported]

Hi,

I have 8 tables, each of which has data that needs to be manipulated and consolidated into 1 table.

My approach is (and has to be) like this:

******************************************

create or replace package body XMB_MIS is

PROCEDURE test
(
p_type IN varchar2,
)
IS

cursor cur_One is
select
... as myField1
... as myField2
... as myField3
from
tblOne;

cursor cur_Two is
select
... as myField1
... as myField2
... as myField3
from
tblTwo;


--etc


cursor cur_Eight is
select
... as myField1
... as myField2
... as myField3
from
tblEight;

BEGIN

if p_type = 'One' then
open cur_One;

elsif p_type = 'Two' then
open cur_Two;
end if

'stuck here

for rec in cur_One
loop
begin
insert into myNewtable
(
myField1,
myField2,
myField3...
)
values
(
rec.myField1,
rec.myField2,
rec.myField3,
);
end;
end loop;

commit;
end;

*****************************

So, I'd like to reuse the INSERT statement.
The parameter p_type passed to the function would determine the cursor to be used in the single INSERT statement.

Any ideas/suggestions?

Cheers

 

[sem]

You may use ref cursor to open the same cursor for different statements. If you're on 8.1.6+ you may even open it for dynamically created statement.

 

[Guest_imported]

That looks good. Thanks
I now have a related problem...

I havr re-written the procedure to look like this (using ref cursor)

*-----------------------

PROCEDURE test
(
p_type IN varchar2,
)
IS

type cur_type is ref cursor;
cur_var cur_type;

begin

if p_type = 'One' then
open cur_One for
select
... as myField1
... as myField2
... as myField3
from
tblOne;

elsif p_type = 'Two' then
open cur_Two for
select
... as myField1
... as myField2
... as myField3
from
tblTwo;


--I get problems here with a PLS-00221 error, cur_var not a procedure or unidentified

for rec in cur_var
loop
begin
insert into myNewtable
(
myField1,
myField2,
myField3...
)
values
(
rec.myField1,
rec.myField2,
rec.myField3,
);
end;
end loop;

commit;
end;

*--------------------

As you can see form my comment half way through, I get a PLS00221 error when I try to loop through the cursor...

Any ideas?

Thanks

 

[Bveoracle]

Use explicit fetch, implicit FOR..LOOP is not allowed.

This is from the manual:

Fetching from a Cursor Variable
The FETCH statement retrieves rows from the result set of a multi-row query. Here is the syntax:

FETCH {cursor_variable_name | :host_cursor_variable_name}
[BULK COLLECT]
INTO {variable_name[, variable_name]... | record_name};


In the following example, you fetch rows one at a time from the cursor variable emp_cv into the user-defined record emp_rec:

LOOP
/* Fetch from cursor variable. */
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched
-- process data record
END LOOP;

Gr. Bart.