Home » SQL & PL/SQL » SQL & PL/SQL » compare 2 collections data (Oracle 12c)
compare 2 collections data [message #665763] Wed, 20 September 2017 04:24 Go to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member


Hi All,

We want to compare two collections by using limit clause with preserving all the data. Please suggest.

DECLARE
    TYPE t_emp_tab IS
        TABLE OF emp%rowtype;
    l_emp_tab    t_emp_tab;
    TYPE t_dept_tab IS
        TABLE OF dept%rowtype;
    l_dept_tab   t_dept_tab;
    CURSOR c1 IS
        SELECT
            *
        FROM
            emp;

    CURSOR c2 IS
        SELECT
            *
        FROM
            dept;

BEGIN
    OPEN c1;
    LOOP
        FETCH c1 BULK COLLECT INTO l_emp_tab LIMIT 5;
        EXIT WHEN c1%notfound;
    END LOOP;

    dbms_output.put_line(l_emp_tab.count);
    CLOSE c1;
	
	--we have to preserve the all values of l_emp_tab
    OPEN c2;
    LOOP
        FETCH c2 BULK COLLECT INTO l_dept_tab LIMIT 3;
        dbms_output.put_line('ff' || l_dept_tab.count);
        EXIT WHEN c2%notfound;
    END LOOP;

    dbms_output.put_line('gg' || l_dept_tab.count);
    CLOSE c2;
    --we have to preserve the all values of l_dept_tab
	
   -- comparision can not be possible because l_emp_tab has 4 rows and l_dept_tab has only 1 rows at the end
   -- how can all the rows could be preserved so that compare could be possible.
    FOR i IN l_dept_tab.first..l_dept_tab.last LOOP
        FOR j IN l_emp_tab.first..l_emp_tab.last LOOP
            IF
                l_dept_tab(i).deptno = l_emp_tab(j).deptno
            THEN
                dbms_output.put_line(l_emp_tab(i).ename);
            END IF;
        END LOOP;
    END LOOP;

END;


Regards,
Nathan
Re: compare 2 collections data [message #665768 is a reply to message #665763] Wed, 20 September 2017 05:05 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've got two tables with different structures and different row counts.
What, exactly, are you trying to compare?
Re: compare 2 collections data [message #665772 is a reply to message #665768] Wed, 20 September 2017 06:30 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Thank you ,

I want to compare two collections if deptno exists in both the table then do some processing logic. But actually I want to preserve the records in the collection tables. Please help.

Regards,
Nathan
Re: compare 2 collections data [message #665773 is a reply to message #665763] Wed, 20 September 2017 06:36 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
You completely misunderstand BULK COLLECT, LIMIT and %NOTFOUND

    LOOP
        FETCH c1 BULK COLLECT INTO l_emp_tab LIMIT 5;
        EXIT WHEN c1%notfound;
    END LOOP;

Loop iteration 1 will fetch first 5 rows into l_emp_tab.
Loop iteration 2 will fetch next 5 rows into l_emp_tab thus overwriting first 5 rows.
Loop iteration 3 will fetch remaining 4 rows into l_emp_tab thus overwriting second set of 5 rows.
As a result l_emp_tab will have last 4 rows only. But imagine table EMP has 15 row. Then there would be iteration 4 and therefore l_emp_tab would end up empty after the loop.
Now about comparing in chunks. Then you'll end up selecting chunks of one of the tables as many times as there are chunks in another. It is simpler and more efficient (unless either of two collections is to large to keep in memory) to chunk largest table only. Something like:

DECLARE
    TYPE t_emp_tab IS
        TABLE OF emp%rowtype;
    l_emp_tab    t_emp_tab;
    TYPE t_dept_tab IS
        TABLE OF dept%rowtype;
    l_dept_tab   t_dept_tab;
    CURSOR c1 IS
        SELECT
            *
        FROM
            emp;

    CURSOR c2 IS
        SELECT
            *
        FROM
            dept;

BEGIN
    OPEN c2;
    FETCH c2 BULK COLLECT INTO l_dept_tab;
    CLOSE c2;
    OPEN c1;
    LOOP
      FETCH c1 BULK COLLECT INTO l_emp_tab LIMIT 5;
      EXIT WHEN l_emp_tab.count = 0;
      FOR i IN 1..l_emp_tab.count LOOP
        FOR j IN 1..l_dept_tab.count LOOP
          IF l_dept_tab(j).deptno = l_emp_tab(i).deptno
            THEN
              dbms_output.put_line(l_emp_tab(i).ename);
          END IF;
        END LOOP;
      END LOOP;
    END LOOP;
    CLOSE c1;
END;
/
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

SQL> 

SY.
Re: compare 2 collections data [message #665774 is a reply to message #665773] Wed, 20 September 2017 07:05 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Thank you SY for detailed explanation. If the two collections are very large and we have to compare both then it is not possible. If we use table instead to compare the data then will it be good approach.Please suggest.



Regards,
Nathan
Re: compare 2 collections data [message #665775 is a reply to message #665774] Wed, 20 September 2017 07:27 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
It is possible but you'll have to read one of the tables multiple times:

DECLARE
    TYPE t_emp_tab IS
        TABLE OF emp%rowtype;
    l_emp_tab    t_emp_tab;
    TYPE t_dept_tab IS
        TABLE OF dept%rowtype;
    l_dept_tab   t_dept_tab;
    CURSOR c1 IS
        SELECT
            *
        FROM
            emp;

    CURSOR c2 IS
        SELECT
            *
        FROM
            dept;

BEGIN
    OPEN c1;
    LOOP
      FETCH c1 BULK COLLECT INTO l_emp_tab LIMIT 5;
      EXIT WHEN l_emp_tab.count = 0;
      FOR i IN 1..l_emp_tab.count LOOP
        OPEN c2;
        LOOP
          FETCH c2 BULK COLLECT INTO l_dept_tab LIMIT 3;
          EXIT WHEN l_dept_tab.count = 0;
          FOR j IN 1..l_dept_tab.count LOOP
            IF l_dept_tab(j).deptno = l_emp_tab(i).deptno
              THEN
                 dbms_output.put_line(l_emp_tab(i).ename);
            END IF;
          END LOOP;
        END LOOP;
        CLOSE c2;
      END LOOP;
    END LOOP;
    CLOSE c1;
END;
/
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

SQL> 

SY.
Previous Topic: extract numbers from a varchar2 upto non-numeric
Next Topic: ORA-06530: Reference to uninitialized composite
Goto Forum:
  


Current Time: Fri Mar 29 01:24:34 CDT 2024