I'm still new to oracle pl/sql so please bear with me. How do I put a cursor with 2 tables inside a variable rowtype? Is it possible to put 2 table rowtypes inside a single variable?
PROCEDURE testproc IS
l_var table1%ROWTYPE;
CURSOR cur1 IS
Select *
From table1, table2
where table1.id = table2.id;
BEGIN
OPEN cur1;
LOOP
FETCH cur1 INTO l_var;
PROCEDURE testproc IS
CURSOR cur1 IS
SELECT table1.field1, table1.field2, table2.field1, table2.field2
FROM table1
INNER JOIN table2 on table1.id = table2.id;
l_var cur1%ROWTYPE;
BEGIN
OPEN cur1
WHILE cur1%ISOPEN LOOP
FETCH cur1 INTO l_var
etc.
Your l_var should be from the cur1 row type:
PROCEDURE testproc IS
CURSOR cur1 IS
select *
from table1, table2
where table1.id = table2.id;
l_var cur1%ROWTYPE;
BEGIN
OPEN cur1;
LOOP
FETCH cur1 INTO l_var;
select * be the issue since the id for example is ambiguous?Because there are two ID columns (one from TABLE1, one from TABLE2) you can't use SELECT *... in this cursor or you'll get something like
ORA-06550: line 7, column 9: PLS-00402: alias required in SELECT list of
cursor to avoid duplicate column names
(at least in Oracle 11.2).
Try the following:
PROCEDURE testproc IS
CURSOR cur1 IS
Select t1.ID, t1.COLx, t2.COLy
From table1 t1,
table2 t2
where table1.id = table2.id;
l_var cur1%ROWTYPE;
BEGIN
OPEN cur1;
LOOP
FETCH cur1 INTO l_var;
EXIT WHEN cur1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID=' || l_var.ID ||
'COLx=' || l_var.COLx ||
'COLy=' || l_var.COLy);
END LOOP;
CLOSE cur1;
END testproc;
I included columns COLx and COLy in the cursor SELECT list as I don't know what what columns you actually have on your tables - replace them with whatever makes sense.
Share and enjoy.
