CODEWITHSUNDEEP
oracle-databaseplsql
user4602735
user4602735

Reputation:

array comparision in oracle to get the difference between two arrays

I have 2 arrays with 10 values in each array. i want to compare the two arrays for finding the difference between column values and print difference columns with old value and new value and store the results in third array. How i can do that in oracle.

DECLARE**strong text**
TYPE array_t is v_array(20) of table%rowtype;
array_diff1 array_t := array_t();
array_diff2 array_t := array_t();
array_diff3 array_t := array_t();

BEGIN
    select * into array_diff1 from table where id =20;
    select * into array_diff2 from table where id =30;
    for i in  array_diff1 
        loop
        for j in array_diff2
        loop
            if array_diff1(i) != array_diif2(j) then
                DBMS_OUPUT.PUT_LINE('are not equal');
                **##get the column with the value in array_diff3.**
            else
                DBMS_OUPUT.PUT_LINE('are equal');
            END IF;
        END LOOP;
        END LOOP;
END;

Upvotes: 0

Views: 6504

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59523

You don't have to use any loops, you can do it directly:

if array_diff1 <> array_diff2 then
    array_diff3 := array_diff1 MULTISET EXCEPT array_diff2;
else
    DBMS_OUPUT.PUT_LINE('are equal');
end if;

Check Multiset Operators for more details.

Note, elements in nested tables do not have a particular order. For example array_t(1,2,3) is equal to array_t(3,2,1) when you compare with = or <>, resp. != (unlike your loop does, unless you order the elements)

Apart from that your code same several syntax errors. Try it like this:

DECLARE
TYPE array_t is TABLE of table%rowtype;
array_diff1 array_t := array_t();
array_diff2 array_t := array_t();
array_diff3 array_t := array_t();

BEGIN
    select * BULK COLLECT into array_diff1 from table where id =20;
    select * BULK COLLECT into array_diff2 from table where id =30;

    if array_diff1 <> array_diff2 then
        array_diff3 := array_diff1 MULTISET EXCEPT array_diff2;
    else
        DBMS_OUPUT.PUT_LINE('are equal');
    end if;

END;

Upvotes: 3

Related Questions