We are using 2 parameterized cursors where the 1st cursor's value is needed in the second cursor to fetch the data accordingly, but it's fetching the values from both cursors. Rather it should fetch from the 2nd cursor only.
Here there are the 2 cursors. From the 1st cursor it needs to fetch the value for attribute 1 and pass it to cursor2 as a parameterized cursor. But while inserting, it's inserting both 1st and 2nd cursor values.
Both actual and expected results are given in the code snippet.
/* Table Creation script:*/
create table tab1 (order_no number,order_item varchar2(40),header_id number)
/
create table tab2 (header_id number,line_id number,attribute1 number)
/
create table final_tab(order_no number, order_item varchar2(40), line_id number)
/
/* Insertion script:*/
insert into tab1 values (1,'ABC',12345)
/
insert into tab1 values (11,'DEF',34567)
/
insert into tab2 values (12345,56789,11)
/
insert into tab2 values (12345,23489,11)
/
insert into tab2 values (34567,32156,null)
/
insert into tab2 values (34567,12534,null)
/
commit
/
/* Anonymous Block: */
DECLARE
CURSOR c1
IS
SELECT a.order_no,
a.order_item,
b.attribute1 end_ord_no,
a.header_id,
b.line_id
FROM tab1 a, tab2 b
WHERE a.header_id = b.header_id AND a.order_no = 1;
CURSOR c2 (i_ord_no NUMBER)
IS
SELECT a.order_no,
a.order_item,
a.header_id,
b.line_id
FROM tab1 a, tab2 b
WHERE a.header_id = b.header_id AND a.order_no = i_ord_no;
BEGIN
FOR c1_rec IN c1
LOOP
FOR c2_rec IN c2 (c1_rec.end_ord_no)
LOOP
INSERT INTO final_tab (order_no, order_item, line_id)
VALUES (c2_rec.order_no, c2_rec.order_item, c2_rec.line_id);
END LOOP;
END LOOP;
COMMIT;
END;
/* Actual Result:*/
Order_NO | Order_Item | Line_id
11 | DEF | 32156
11 | DEF | 12534
11 | DEF | 32156
11 | DEF | 12534
/*Expected Result:*/
Order_NO | Order_Item | Line_id
11 | DEF | 32156
11 | DEF | 12534
The problem is that your first cursor returns two rows, both of which have the value 11 for END_ORD_NO. The second cursor is executed for each of the two rows returned by the first cursor, and each execution of the second cursor returns two rows, values from which are duly inserted into FINAL_TAB.
To fix this you should combine your two cursors into a single cursor:
SELECT a.order_no,
a.order_item,
a.header_id,
b.line_id
FROM tab1 a, tab2 b
WHERE a.header_id = b.header_id AND
a.order_no IN (SELECT b.attribute1
FROM tab1 a, tab2 b
WHERE a.header_id = b.header_id AND
a.order_no = 1)
which reduces your code block to
DECLARE
CURSOR cc IS
SELECT a.order_no,
a.order_item,
a.header_id,
b.line_id
FROM tab1 a, tab2 b
WHERE a.header_id = b.header_id AND
a.order_no IN (SELECT b.attribute1
FROM tab1 a, tab2 b
WHERE a.header_id = b.header_id AND
a.order_no = 1);
BEGIN
FOR rec IN cc LOOP
INSERT INTO final_tab (order_no, order_item, line_id)
VALUES (rec.order_no, rec.order_item, rec.line_id);
END LOOP;
COMMIT;
END;
This will not only provide the results you wanted but it will also reduce the chance of errors by simplifying the code, and reduces runtime by eliminating the nested loops.
However, even this can be further simplified to
INSERT INTO final_tab (order_no, order_item, line_id)
SELECT a.order_no,
a.order_item,
b.line_id
FROM tab1 a, tab2 b
WHERE a.header_id = b.header_id AND
a.order_no IN (SELECT b.attribute1
FROM tab1 a, tab2 b
WHERE a.header_id = b.header_id AND
a.order_no = 1)
This has the advantage that it accomplishes the entire task without requiring the use of any looping, and is faster because the entire operation is performed by the database without having to slow down to interface with an external procedure.
If you must use both cursors, perhaps you could use a MERGE statement, as follows:
DECLARE
CURSOR c1
IS
SELECT a.order_no,
a.order_item,
b.attribute1 end_ord_no,
a.header_id,
b.line_id
FROM tab1 a, tab2 b
WHERE a.header_id = b.header_id AND a.order_no = 1;
CURSOR c2 (i_ord_no NUMBER)
IS
SELECT a.order_no,
a.order_item,
a.header_id,
b.line_id
FROM tab1 a, tab2 b
WHERE a.header_id = b.header_id AND a.order_no = i_ord_no;
BEGIN
FOR c1_rec IN c1 LOOP
FOR c2_rec IN c2 (c1_rec.end_ord_no) LOOP
MERGE INTO FINAL_TAB ft
USING (SELECT c2_rec.order_no AS ORDER_NO,
c2_rec.order_item AS ORDER_ITEM,
c2_rec.line_id AS LINE_ID
FROM DUAL) d
ON (ft.ORDER_NO = d.ORDER_NO AND
ft.ORDER_ITEM = d.ORDER_ITEM AND
ft.LINE_ID = d.LINE_ID)
WHEN NOT MATCHED THEN
INSERT (order_no, order_item, line_id)
VALUES (d.order_no, d.order_item, d.line_id);
END LOOP; -- c2_rec
END LOOP; -- c1_rec
COMMIT;
END;