I have two cursor c11 and c2 .I have two tables compare1 and compare2.Both having same columns but values are different. I want to pass result of c1 to c2 cursor. I am fetching column names of a table from user_tab_columns. I want to pass the column name to get difference of unique records between compare 1 and compare2 tables. But the column names from c1 is not passed to c2.Please find the code which I have tried.
DECLARE
COL VARCHAR2 (200);
OUTRECORD VARCHAR2 (200);
CURSOR c1 IS
SELECT COLUMN_NAME
FROM all_tab_columns
WHERE table_name = 'COMPARE1';
CURSOR c2( col varchar(200)) IS--col is the column names from c1
(SELECT DISTINCT COL
FROM COMPARE1
MINUS
SELECT DISTINCT COL
FROM COMPARE2);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO COL;
DBMS_OUTPUT.put_line (COL);
OPEN c2(col);--col Is not passing to 2nd cursor
LOOP
FETCH c2 INTO OUTRECORD;--outrecord is empty is col is not passed to 2nd cursor
INSERT INTO RESULT
VALUES ('B001',
'COMPARE',
'2018',
COL,
OUTRECORD,--empy value
'NOT PRESENT IN 2017');
COMMIT;
END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
END;
Help me in passing values to c1.Thanks in advance
The main reason why your code is not working as you expect it to is because you're passing the column name from the first cursor into the second cursor as a literal value.
So, when you do the minus in the second cursor, you're comparing if one string is the same as that string, meaning no rows will be returned. I.e. it is essentially:
select 'x' from some_table
minus
select 'x' from some_other_table;
To get around this, you would need to use dynamic sql - here's an example using a cursor and a ref cursor:
DECLARE
rc SYS_REFCURSOR;
outrecord VARCHAR2(200);
BEGIN
FOR r1 IN (SELECT column_name col
FROM all_tab_columns
WHERE table_name = 'COMPARE1')
LOOP
dbms_output.put_line(r1.col);
OPEN rc FOR 'SELECT '||r1.col||' col'||CHR(10)||
'FROM compare1'||CHR(10)||
'MINUS'||CHR(10)||
'SELECT '||r1.col||' col'||CHR(10)||
'FROM compare2';
LOOP
FETCH rc
INTO outrecord;
EXIT WHEN rc%NOTFOUND;
INSERT INTO RESULT -- you should list the columns being inserted into here
VALUES
('B001',
'COMPARE',
'2018',
r1.col,
outrecord,
'NOT PRESENT IN 2017');
COMMIT;
END LOOP;
END LOOP;
CLOSE rc;
END;
/
You can see that I've converted the looping round the first cursor into a cursor-for-loop, rather than explicitly fetching each row. That way, you don't have to worry about checking you've reached the end of the rows or closing the cursor. The inner loop through the second cursor I've added in the missing exit when rc%notfound clause.
Note also that I have removed the distincts from your minus query - minus already distincts the rows, so there is no need to explicitly state it.
However, what you've got going there is a row-by-row insert, which is not the best approach at all - you should instead be doing all the work in a single insert statement, which you can see below:
BEGIN
FOR r1 IN (SELECT column_name col
FROM all_tab_columns
WHERE table_name = 'COMPARE1')
LOOP
dbms_output.put_line(r1.col);
execute immediate 'INSERT INTO RESULT'||CHR(10)|| -- you should list the columns being inserted into here
'SELECT ''B001'','||CHR(10)||
' ''COMPARE'','||CHR(10)||
' ''2018'','||CHR(10)||
' '''||r1.col||''','||CHR(10)||
' '||r1.col||CHR(10)||
'FROM compare1'||CHR(10)||
'MINUS'||CHR(10)||
'SELECT ''B001'','||CHR(10)||
' ''COMPARE'','||CHR(10)||
' ''2018'','||CHR(10)||
' '''||r1.col||''','||CHR(10)||
' '||r1.col||CHR(10)||
'FROM compare2';
END LOOP; END;
/
N.B. untested.