This discussion is archived
11 Replies Latest reply: Jul 3, 2013 3:09 AM by Varun-Oracle RSS

Comparing nested tables for equality

9423755 Explorer
Currently Being Moderated

Hi,

SF at oracle.com/technetwork/issue-archive/o53plsql-083350.html states that you can compare two database tables (of the same structure) by defining a nested table type (using %ROWTYPE) and two NT variables of that type, and loading the contents of each table into its respective NT variable, before comparing them using the = operator.

Having read the Oracle documentation which states that you can only compare NTs for equality if they don't contain record types, I was surprised to read this, but figured I would try it because I must be misunderstanding SF, but it didn't work.

 


SCOTT@ORCL> create table empcopy3 as select * from emp;

 

 

Table created.


declare

    type emp_ntt is table of emp%rowtype;

    emp_nt1 emp_ntt;

    emp_nt2 emp_ntt;

begin

    select * bulk collect into emp_nt1 from emp;

    select * bulk collect into emp_nt2 from empcopy3;

    if(emp_nt1=emp_nt2) then dbms_output.put_line('tables are eq');

    else  dbms_output.put_line('tables are not eq');

    end if;

end;

/

 

SCOTT@ORCL> @C:\Users\J\Documents\SQL\test23.sql

    if(emp_nt1=emp_nt2) then dbms_output.put_line('tables are eq');

              *

ERROR at line 8:

ORA-06550: line 8, column 15:

PLS-00306: wrong number or types of arguments in call to '='

ORA-06550: line 8, column 5:

PL/SQL: Statement ignored

 

 

If I instead do this, as expected, it works:

 

declare

    type emp_ntt is table of emp.empno%type;

    emp_nt1 emp_ntt;

    emp_nt2 emp_ntt;

begin

    select empno bulk collect into emp_nt1 from emp;

    select empno bulk collect into emp_nt2 from empcopy3;

    if emp_nt1 = emp_nt2 then dbms_output.put_line('tables are eq');

    else  dbms_output.put_line('tables are not eq');

    end if;

end;

/

 

SCOTT@ORCL> @C:\Users\J\Documents\SQL\test23.sql

tables are eq

 

 

PL/SQL procedure successfully completed.

 

 

 

But SF goes on to say he timed the execution of his NT equality method, comparing it with a SQL-only equivalent, and so I must be missing something. My understanding is that using %ROWTYPE declares a record type.

Any pointers would be appreciated, thanks,

Jason

  • 1. Re: Comparing nested tables for equality
    Solomon Yakobson Guru
    Currently Being Moderated

    And what it has to do with collections? RECORDs can't be compared - period.

     

    SY.

  • 2. Re: Comparing nested tables for equality
    Solomon Yakobson Guru
    Currently Being Moderated

    SF is talking about nested tables:

     

    create or replace

      type NumList

        is table of number

    /

    declare

        v_NumList1 NumList := NumList(1,2,3);

        v_NumList2 NumList := NumList(1,2,3);

        v_NumList3 NumList := NumList(3,1,2);

        v_NumList4 NumList := NumList(4);

    begin

        if v_NumList1 = v_NumList2

          then dbms_output.put_line('v_NumList1 = v_NumList2');

          else dbms_output.put_line('v_NumList1 != v_NumList2');

        end if;

        if v_NumList1 = v_NumList3

          then dbms_output.put_line('v_NumList1 = v_NumList3');

          else dbms_output.put_line('v_NumList1 != v_NumList3');

        end if;

        if v_NumList1 = v_NumList4

          then dbms_output.put_line('v_NumList1 = v_NumList4');

          else dbms_output.put_line('v_NumList1 != v_NumList4');

        end if;

    end;

    /

    v_NumList1 = v_NumList2
    v_NumList1 = v_NumList3
    v_NumList1 != v_NumList4

    PL/SQL procedure successfully completed.

    SQL>

     

    SY.

  • 3. Re: Comparing nested tables for equality
    Solomon Yakobson Guru
    Currently Being Moderated

    And keep in mind, if nested table is table of objects you must define MAP/ORDER method, otherwise:

     

    SQL> create or replace
      2    type emp_obj_type
      3    as object(
      4              ename varchar2(30),
      5              sal number
      6             )
      7  /

    Type created.

    SQL> create or replace
      2    type emp_tbl_type
      3    as table of emp_obj_type
      4  /

    Type created.

    SQL> declare
      2      v_emp_tbl1 emp_tbl_type := emp_tbl_type(emp_obj_type('John',5000),emp_obj_type('Jim',3000),emp_obj_type('Sam',2000));
      3      v_emp_tbl2 emp_tbl_type := emp_tbl_type(emp_obj_type('John',5000),emp_obj_type('Jim',3000),emp_obj_type('Sam',2000));
      4      v_emp_tbl3 emp_tbl_type := emp_tbl_type(emp_obj_type('Sam',2000),emp_obj_type('John',5000),emp_obj_type('Jim',3000));
      5      v_emp_tbl4 emp_tbl_type := emp_tbl_type(emp_obj_type('John',5000));
      6  begin
      7      if v_emp_tbl1 = v_emp_tbl2
      8        then dbms_output.put_line('v_emp_tbl1 = v_emp_tbl2');
      9        else dbms_output.put_line('v_emp_tbl1 != v_emp_tbl2');
    10      end if;
    11      if v_emp_tbl1 = v_emp_tbl3
    12        then dbms_output.put_line('v_emp_tbl1 = v_emp_tbl3');
    13        else dbms_output.put_line('v_emp_tbl1 != v_emp_tbl3');
    14      end if;
    15      if v_emp_tbl1 = v_emp_tbl4
    16        then dbms_output.put_line('v_emp_tbl1 = v_emp_tbl4');
    17        else dbms_output.put_line('v_emp_tbl1 != v_emp_tbl4');
    18      end if;
    19  end;
    20  /
        if v_emp_tbl1 = v_emp_tbl2
                      *
    ERROR at line 7:
    ORA-06550: line 7, column 19:
    PLS-00306: wrong number or types of arguments in call to '='
    ORA-06550: line 7, column 5:
    PL/SQL: Statement ignored
    ORA-06550: line 11, column 19:
    PLS-00306: wrong number or types of arguments in call to '='
    ORA-06550: line 11, column 5:
    PL/SQL: Statement ignored
    ORA-06550: line 15, column 19:
    PLS-00306: wrong number or types of arguments in call to '='
    ORA-06550: line 15, column 5:
    PL/SQL: Statement ignored

     

    And now I'll define MAP method:


    SQL> drop type emp_tbl_type
      2  /

    Type dropped.

    SQL> create or replace
      2    type emp_obj_type
      3    as object(
      4              ename varchar2(30),
      5              sal number,
      6              map member function sort_key
      7                return varchar2
      8             )
      9  /

    Type created.

    SQL> create or replace
      2    type body emp_obj_type
      3    as
      4      map member function sort_key
      5        return varchar2
      6        is
      7        begin
      8            return self.ename || '|' || self.sal;
      9      end;
    10  end;
    11  /

    Type body created.

    SQL> create or replace
      2    type emp_tbl_type
      3    as table of emp_obj_type
      4  /

    Type created.

    SQL> declare
      2      v_emp_tbl1 emp_tbl_type := emp_tbl_type(emp_obj_type('John',5000),emp_obj_type('Jim',3000),emp_obj_type('Sam',2000));
      3      v_emp_tbl2 emp_tbl_type := emp_tbl_type(emp_obj_type('John',5000),emp_obj_type('Jim',3000),emp_obj_type('Sam',2000));
      4      v_emp_tbl3 emp_tbl_type := emp_tbl_type(emp_obj_type('Sam',2000),emp_obj_type('John',5000),emp_obj_type('Jim',3000));
      5      v_emp_tbl4 emp_tbl_type := emp_tbl_type(emp_obj_type('John',5000));
      6  begin
      7      if v_emp_tbl1 = v_emp_tbl2
      8        then dbms_output.put_line('v_emp_tbl1 = v_emp_tbl2');
      9        else dbms_output.put_line('v_emp_tbl1 != v_emp_tbl2');
    10      end if;
    11      if v_emp_tbl1 = v_emp_tbl3
    12        then dbms_output.put_line('v_emp_tbl1 = v_emp_tbl3');
    13        else dbms_output.put_line('v_emp_tbl1 != v_emp_tbl3');
    14      end if;
    15      if v_emp_tbl1 = v_emp_tbl4
    16        then dbms_output.put_line('v_emp_tbl1 = v_emp_tbl4');
    17        else dbms_output.put_line('v_emp_tbl1 != v_emp_tbl4');
    18      end if;
    19  end;
    20  /
    v_emp_tbl1 = v_emp_tbl2
    v_emp_tbl1 = v_emp_tbl3
    v_emp_tbl1 != v_emp_tbl4

    PL/SQL procedure successfully completed.

    SQL>

     

    SY.

  • 4. Re: Comparing nested tables for equality
    9423755 Explorer
    Currently Being Moderated

    SY, I appreciate the reply, and especially the lengthy demonstration of the MAP and ORDER functions necessary to compare object types, but you're not reading what I'm writing.

    Yes, he (SF) is talking about nested tables, and so am I. He has created a nested table type of TABLE%ROWTYPE elements, and compared them using the equality operator, and it worked. As far as I can tell, I've done the same thing, and it hasn't. What I'm asking is, what has he done that I haven't, or what have I done that he hasn't?

     

    SF states in the article the following:

     

    First, declare a nested table TYPE in PL/SQL that mimics the structure of the table:

    DECLARE TYPE table1_tt IS TABLE OF table1%ROWTYPE; nt_copy1 table1_tt; nt_copy2 table1_tt; 

     

    Then use BULK COLLECT to load up these collections with all the data from the table in a most efficient manner:

       PROCEDURE load1 (e IN OUT table1_tt) IS BEGIN SELECT * BULK COLLECT INTO e FROM table1; END; 

     

    And now check for table equality simply with this code:

    BEGIN load1 (nt_copy1); load2 (nt_copy2); IF nt_copy1 = nt_copy2 THEN ...

     

    I believe I have done all the steps that he specifies, and yet it doesn't work for me.

     

    You have created a nested table of scalar SQL datatype (a nested table of number). I know I can compare a NT of number. What I can't figure out is how he is able to compare a NT of %ROWTYPE.

     

    As to "records cannot be compared, period", that's the point: at risk of repeating myself, he's defining a nested table type in which the elements are %ROWTYPE. They are therefore (TTBOMK) RECORD type. And the Oracle documentation says you cannot compare nested tables that have an element of record type.

     

    Cheers,

    Jason

  • 5. Re: Comparing nested tables for equality
    padders Pro
    Currently Being Moderated

    SF's example does appear to do as you say, however since it is not a complete example and the related scripts do not appear to be available it is somewhat hard to be conclusive. Sometimes articles like this are published around beta time and the reference to a 10i script seems to agree with that. Perhaps such functionality was considered and never made it to the production release.

     

    I would agree with Solomon's comments about what is supported. Up until now I don't recall seeing any working evidence of support for comparison of PL/SQL record types (and by extension datatypes that include PL/SQL record types).

  • 6. Re: Comparing nested tables for equality
    Solomon Yakobson Guru
    Currently Being Moderated

    Jason_942375 wrote:

     

    you're not reading what I'm writing.

     

    No, it is you who is not reading the article properly. And, I must agree, SF's article is a bit misleading. He takes, for convenience I assume, an existing package emp_coll_pkg which provides EQUAL function to compare two nested tables or records by comparing each nested elements. And, since each element is a record, to compare elements function EQUAL compares each record attribute. Then he says that there is no need to write EQUALS function in 10G since Oracle 10G offers collection compare but only for nested tables. And that's where article becomes a bit misleading. Why? A lot of readers (including you) assume that in 10G you can scrap emp_coll_pkg and simply use Oracle native equal (=) operator. Not true. Again, all SF is saying is in 10G Oracle suports native equal (=) operator for nested tables. Nested table is a table of elements of some type. If you can't compare scalar variables of that type why do you think you suddenly would be able to compare nested table of that incomparable type? Obviously, you can't. And since records are incomparable, nested tables or records are incomparable too. That's where example in SF's article is a bit misleading for inexperienced readers. Bottom line - in 10G Oracle offers native equal (=) operator for nested tables where nested table element type by itself supports equal (=) operator. Hope it clears your doubts.

     

    SY.

  • 7. Re: Comparing nested tables for equality
    Solomon Yakobson Guru
    Currently Being Moderated

    Jason_942375 wrote:

     

    you're not reading what I'm writing.

     

    No, it is you who is not reading the article properly. And, I must agree, SF's article is a bit misleading. He takes, for convenience I assume, an existing package emp_coll_pkg which provides EQUAL function to compare two nested tables or records by comparing each nested elements. And, since each element is a record, to compare elements function EQUAL compares each record attribute. Then he says that there is no need to write EQUALS function in 10G since Oracle 10G offers collection compare but only for nested tables. And theat's where article becomes a bit misleading. Why? A lot of readers (including you) assume that in 10G you can scrap emp_coll_pkg and simply use Oracle native equal (=) operator. Not true. Again, all SF is saying is in 10G Oracle suports native equal (=) operator for nested tables. Nested table is a table of elements of some type. If you can't compare scalar variables of that type why do you think you suddenly would be able to compare neted table of that incomparable type? Obviously, you can't. And since records are incomparable, nested tables or records are incomparable too. That's where example in SF's article is a bit misleading for inexperienced readers. Bottom line - in 10G Oracle offers native equal (=) operator for nested tables where nested table element type by itself supports equal (=) operator. Hope it clears your doubts.

     

    SY.

  • 8. Re: Comparing nested tables for equality
    Chris Hunt Journeyer
    Currently Being Moderated

    SY, you need to read a bit further down the article.In the section headed Interesting Applications of New Features Feurstein gives the following fragments of code:

     

    First, declare a nested table TYPE in PL/SQL that mimics the structure of the table:

    DECLARE
       TYPE table1_tt IS TABLE OF table1%ROWTYPE;
        nt_copy1 table1_tt;
        nt_copy2 table1_tt; 

     

    Then use BULK COLLECT to load up these collections with all the data from the table in a most efficient manner:

       PROCEDURE load1 (e IN OUT table1_tt)  IS
       BEGIN
          SELECT *  BULK COLLECT INTO e
          FROM table1;
      END; 

     

    And now check for table equality simply with this code:

    BEGIN
       load1 (nt_copy1);
       load2 (nt_copy2);
       IF nt_copy1 = nt_copy2 THEN ... 

     

    Feuerstein claims to have run this program, and found that it produces a result faster than a pure SQL alternative using MINUS and UNION. I find both claims implausible - when I try to copy his code (my amendments/additions in red) I get the same error message:

     

    SQL> select * from v$version

    BANNER                                                                         
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production   
    PL/SQL Release 11.2.0.3.0 - Production                                         
    CORE 11.2.0.3.0 Production                                                     
    TNS for Linux: Version 11.2.0.3.0 - Production                                 
    NLSRTL Version 11.2.0.3.0 - Production                                         

    5 rows selected.
    SQL>
      DECLARE

       TYPE table1_tt IS TABLE OF scott.emp%ROWTYPE;
       nt_copy1 table1_tt;
       nt_copy2 table1_tt;


       PROCEDURE load1 (e IN OUT table1_tt)

       IS
       BEGIN
          SELECT *
          BULK COLLECT INTO e
            FROM scott.emp;
       END;


    BEGIN


       load1 (nt_copy1);

       load1 (nt_copy2);


       IF nt_copy1 = nt_copy2 THEN

          DBMS_OUTPUT('same');
       ELSE
          DBMS_OUTPUT('different');
       END IF;
    END;

    Error at line 6
    ORA-06550: line 19, column 16:
    PLS-00306: wrong number or types of arguments in call to '='
    ORA-06550: line 19, column 4:
    PL/SQL: Statement ignored

    So unless they've remived this particular piece of functionality in the intervening decade, I'm calling shenanigans on this one.

  • 9. Re: Comparing nested tables for equality
    9423755 Explorer
    Currently Being Moderated

    SY

     

    What part of the following text uses a function called equal?

     

    First, declare a nested table TYPE in PL/SQL that mimics the structure of the table:

    DECLARE TYPE table1_tt IS TABLE OF table1%ROWTYPE;

    nt_copy1 table1_tt;

    nt_copy2 table1_tt;

     

    Then use BULK COLLECT to load up these collections with all the data from the table in a most efficient manner:

       PROCEDURE load1 (e IN OUT table1_tt) IS

         BEGIN

              SELECT * BULK COLLECT INTO e FROM table1;

         END;

     

    And now check for table equality simply with this code:

    BEGIN

    load1 (nt_copy1);

    load2 (nt_copy2);

    IF nt_copy1 = nt_copy2 THEN ...

     

    Do you see the first lines?  nt_copy1 and nt_copy2 are nested tables of %ROWTYPE.

    Do you see the last line? "IF nt_copy1 = nt_copy2"

    Let me blow it up for you so you can see it :

     

    "IF nt_copy1 = nt_copy2"

     

    No "equals" function. No "equals" procedure. No package. Just the use of the = operator on two variables of nested table of record type.

  • 10. Re: Comparing nested tables for equality
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Jason_942375 wrote:

    Do you see the first lines?  nt_copy1 and nt_copy2 are nested tables of %ROWTYPE.

     

    No they are not nested tables. They are arrays. Do not let the terminology so casually (and often incorrectly) being thrown around, confuse you.

     

    A nested table is a structure that exists inside a SQL table, as a table. Thus the term nested table.

     

    The correct term in PL/SQL is array or collection. The same terms for the very same data structures in C/C++, Pascal, Ada, and other languages.

     

    So nt_copy1 is an array of a record (Pascal term) or a struct (C term). The %RowType is a compiler macro that creates a record/struct based on the definition of a table. The PL/SQL table clause (a horrible misnomer) defines an array.

     

    So no - there are no nested tables defined with that code

     

    Do you see the last line? "IF nt_copy1 = nt_copy2"

    Let me blow it up for you so you can see it :

     

    "IF nt_copy1 = nt_copy2"

     

    No "equals" function. No "equals" procedure. No package. Just the use of the = operator on two variables of nested table of record type.

     

    And that is what Solomon referred to. You cannot compare arrays of records/structs with one another via the equals operator.

     

    Only arrays of scalar values can be compared using the equals operator.

     

    Example added:

    SQL> declare
      2          type table1_tt is table of emp.ename%Type;
      3  
      4          nt_copy1        table1_tt;
      5          nt_copy2        table1_tt;
      6  
      7          procedure LoadEmp1(e in out nocopy table1_tt) is
      8          begin
      9                  select ename bulk collect into e from emp order by empno;
     10          end;
     11  
     12          procedure LoadEmp2(e in out nocopy table1_tt) is
     13          begin
     14                  select ename bulk collect into e from emp order by 1;
     15          end;
     16  begin
     17          LoadEmp1( nt_copy1 );
     18          LoadEmp2( nt_copy2 );
     19  
     20          if nt_copy1 = nt_copy2 then
     21                  DBMS_OUTPUT.put_line('same');
     22          else
     23                  DBMS_OUTPUT.put_line('different');
     24          end if;
     25  end;
     26  /
    same
    PL/SQL procedure successfully completed.
    SQL>
    
  • 11. Re: Comparing nested tables for equality
    Varun-Oracle Explorer
    Currently Being Moderated

    test to see if it shows up