Merging cursor data

I am trying to merge table data with cursor data. I have the following scenario

tbl1
rn      ID
1              143
2              144
3              145
4              146
5              147
6              148
 
 
 
Cusror has a row number and i need to join with table data to retreive the ID from tbl1
 
cursor.rn=tbl.rn
 
and
 
Insert the tbl1 ID into table with rest of cursor data.





Does anyone know how I can achieve?
earngreenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
If I understand what you need, take a loot at the MERGE sql statement:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

If you can provide some sample data and expected results we can probably come up with a working example.
0
earngreenAuthor Commented:
I am trying to merge a cursor with table data not just a simple merge statement.
0
slightwv (䄆 Netminder) Commented:
Please post sample data and expected results.

Your requirements are pretty vague.

I don't know what "merge a cursor" means.

If you have an object declared as a cursor or ref_cursor you will likely need to loop through it one row at a time and perform the necessary statements.

Is this really not the same question that you asked before?

You have a cursor and want to insert.
http://www.experts-exchange.com/questions/28705325/Cursor-with-multiple-inserts.html

It is pretty much the same logic and as I suggested in your previous questions, I would likely still lean towards using MERGE.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
Guessing at your requirements, see if this works for you:
drop table tab1 purge;
create table tab1(rn number, id number);

insert into tab1 values(1,143);
insert into tab1 values(2,144);
commit;


declare
                cursor c1 is select 1 cur_rn ,145 cur_id from dual union select 3, 146 from dual;
                v_rn       number;
                v_id       number;
begin
                open c1;
                loop
                                fetch c1 into v_rn, v_id;
                                exit when c1%notfound;

                                merge into tab1 t1
                                using (select v_rn cur_rn, v_id cur_id from dual) cur_vals
                                on ( t1.rn = cur_vals.cur_rn )
                                when not matched then insert values(cur_vals.cur_rn,cur_vals.cur_id);

                end loop;
                close c1;
end;
/

select * from tab1;

Open in new window

0
earngreenAuthor Commented:
The problem is that I cannot do a union. In ur example c1.rn would reference another table that has a rn. So it is basically doing a lookup.
0
slightwv (䄆 Netminder) Commented:
I would probably just create a primary key or unique index on the table.

Then you could just loop through the cursor, do the insert and ignore the dup_val_on_index exception.

drop table tab1 purge;
create table tab1(rn number, id number);

insert into tab1 values(1,143);
insert into tab1 values(2,144);
commit;

create unique index tab1_rn_idx on tab1(rn);

declare
                cursor c1 is select 1 cur_rn ,145 cur_id from dual union select 3, 146 from dual;
                v_rn       number;
                v_id       number;
begin
                open c1;
                loop
                                fetch c1 into v_rn, v_id;
                                exit when c1%notfound;

                                begin
                                                insert into tab1 values(v_rn,v_id);
                                                exception when dup_val_on_index then
                                                                null;
                                end;

                end loop;
                close c1;
end;
/

select * from tab1;

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>The problem is that I cannot do a union. In ur example c1.rn would reference another table that has a rn.

The union in my example is just a way to create two rows in my cursor.

I don't have your cursor and you never provided sample data.

So, I made up a cursor that returned two rows.
0
earngreenAuthor Commented:
let’s say that we had this proc and the table that I have defined. I want to insert the id from tbl1
 
tbl1
rn      ID
1              143
2              144
3              145
4              146
5              147
6              148
 
CREATE OR REPLACE PROCEDURE fast_way AUTHID CURRENT_USER IS
TYPE myarray IS TABLE OF parent%ROWTYPE;
l_data myarray;
 
cursor r is
SELECT rn, stock_num, stock_name FROM parent;
BEGIN
  OPEN r;
  LOOP
    FETCH r BULK COLLECT INTO l_data LIMIT 1000;
 
 
    forall i in 1..l_data.count
    INSERT INTO child1,id VALUES l_data(i),tbl1.id;
 
    forall i in 1..l_data.count
    INSERT INTO child2,id VALUES l_data(i), tbl1.id;
 
    EXIT WHEN r%NOTFOUND;
  END LOOP;
  COMMIT;
  CLOSE r;
END fast_way;
0
slightwv (䄆 Netminder) Commented:
>>and the table that I have defined. I want to insert the id from tbl1

Sorry but I do not understand.

What do you want to do with tbl1?  Insert the id where?

Since you posted code, I think we covered this in your previous questions:
I would not do the bulk collect.
0
earngreenAuthor Commented:
Tbl1 needs to join with cursor data and insert
0
slightwv (䄆 Netminder) Commented:
then add it to the select for tbl1 to the cursor:
cursor r is
 SELECT rn, stock_num, stock_name FROM parent union all select rn,id from tbl1;



I know that probably isn't what you want but without more detail it is the best I can do.

You could help speed up the answer if you were to provide the requested sample data and expected results.

If you could provide some data for the parent table, then provide what the results of your inserts inside the procedure would produce, we can set up a working example.
0
earngreenAuthor Commented:
cursor
rn      stock_num   stock_name
1              0000001     hats
2              0000002     shoes
3              0000003     boots
4              0000004     Grocery
5              0000005     toys
6              0000006     Grocery
 
 
tbl1
rn      ID
1              143
2              144
3              145
4              146
5              147
6              148
 
desired output
 
 
rn   ID         stock_num   stock_name
1    143   0000001     hats
2    144   0000002     shoes
3    145   0000003     boots
4    146   0000004     Grocery
5    147   0000005     toys
6    148   0000006     Grocery
0
slightwv (䄆 Netminder) Commented:
Is the desired output to be inserted into a new table?
Is the desired output updates to the parent table?
or
Do you want the cursor to contain those values?

If the last:
cursor r is select p.rn, id, stock_num, stock_name
from parent p join tbl1 t on t.rn=p.rn;
0
earngreenAuthor Commented:
Cursors already been created cannot do that join. Need to join the data after the cursor has been created for insert.
0
slightwv (䄆 Netminder) Commented:
>>Need to join the data after the cursor has been created for insert.

You cannot change a cursor once it has been created.  You can only loop through it one row at a time.

Since you cannot change the cursor, you'll need to query tbl1 to retrieve the ID inside the cursor loop but before the insert.  This means you cannot do the bulk collect the way you have it.

What you might be able to do is create your own TYPE that has all the columns of PARENT and a null ID column.

Then you could bulk collect from the cursor and loop through the pl/sql table adding the ID from tbl1.

See all the extra work you have to do when using bulk collect and pl/sql tables?
0
earngreenAuthor Commented:
I agree can u help with syntax for

What you might be able to do is create your own TYPE that has all the columns of PARENT and a null ID column.

Then you could bulk collect from the cursor and loop through the pl/sql table adding the ID from tbl1.
0
slightwv (䄆 Netminder) Commented:
>>Then you could bulk collect from the cursor and loop through the pl/sql table adding the ID from tbl1.

You will need to change the CURSOR's select statement.  You mentioned above you cannot do that to add the join to tbl1 (which should solve everything).

Can you change the cursor definition to add the dummy column?
0
earngreenAuthor Commented:
Yes
0
slightwv (䄆 Netminder) Commented:
Then why can't you do the join to tbl1?  It will be a LOT less work.
0
earngreenAuthor Commented:
I am actually generated some records into tbl1 dynamically before I need to do the join.
0
slightwv (䄆 Netminder) Commented:
OK. Let me work on an example.
0
slightwv (䄆 Netminder) Commented:
Here is my test setup and example.  It is a pl/sql block instead of a procedure for simplification.

/*
drop table myParent purge;
create table myParent(rn number, stock_num varchar2(10), stock_name varchar2(10));
insert into myParent values(1,'0000001','hats');
insert into myParent values(2,'0000002','shoes');
insert into myParent values(3,'0000003','boots');
insert into myParent values(4,'0000004','Grocery');
insert into myParent values(5,'0000005','toys');
insert into myParent values(6,'0000006','Grocery');
commit;
  
drop table tab1 purge;
create table tab1(rn number, ID number);
insert into tab1 values(1,143);
insert into tab1 values(2,144);
insert into tab1 values(3,145);
insert into tab1 values(4,146);
insert into tab1 values(5,147);
insert into tab1 values(6,148);
commit;
*/
  
declare

                TYPE ResultRec IS RECORD (
                                rn number,
                                stock_num varchar2(10),
                                stock_name varchar2(10),
                                id number
                );

                TYPE myarray IS TABLE OF ResultRec;


                l_data myarray;
  
 cursor r is
SELECT rn, stock_num, stock_name, null id FROM myparent;

BEGIN
   OPEN r;
   LOOP
     FETCH r BULK COLLECT INTO l_data LIMIT 1000;
  
                                -- load up the id field
                                for i in l_data.first .. l_data.last loop
                                                select id into l_data(i).id from tab1 where rn=l_data(i).rn;
                                end loop;

                                --check them all
                                for i in l_data.first .. l_data.last loop
                                                dbms_output.put_line(
                                                                l_data(i).rn || ':' || 
                                                                l_data(i).id || ':' || 
                                                                l_data(i).stock_num || ':' || 
                                                                l_data(i).stock_name
                                                );
                                end loop;
  
     EXIT WHEN r%NOTFOUND;
   END LOOP;
   COMMIT;
   CLOSE r;
END; 
 /

Open in new window

0
slightwv (䄆 Netminder) Commented:
oops.. forgot to tweak my type.  You can use the table types in the record.

Change:
TYPE ResultRec IS RECORD (
                                rn number,
                                stock_num varchar2(10),
                                stock_name varchar2(10),
                                id number
                );

to:
TYPE ResultRec IS RECORD (
                                rn myParent.rn%type,
                                stock_num myParent.stock_num%type,
                                stock_name myParent.stock_name%type,
                                id tab1.id%type
                );
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.