Oracle 11g
Oracle lets you compare PL/SQL nested table collections, but there are several rules to keep in mind. These rules are evaluated in the order given.
Create these types and this helper procedure to test these rules.
create type type1_nt is table of varchar2(100)
/
CREATE PROCEDURE p_comp_coll(p_col1 IN type1_nt,
p_col2 IN type1_nt) IS
BEGIN
IF p_col1 = p_col2 THEN
dbms_output.put_line('EQUAL');
ELSIF p_col1 <> p_col2 THEN
dbms_output.put_line('NOT EQUAL');
ELSE
dbms_output.put_line('NULL');
END IF;
END;
/
Rule 1. Nested table collections of objects or records cannot be compared.
SQL> DECLARE
2 TYPE varrec_t IS RECORD(
3 val1 VARCHAR2(100));
4 TYPE mycol_t IS TABLE OF varrec_t;
5 l_coll1 mycol_t;
6 l_coll2 mycol_t;
7 BEGIN
8 IF l_coll1 = l_coll2 THEN -- will not compile
9 NULL;
10 END IF;
11 END;
12 /
DECLARE
TYPE varrec_t IS RECORD(
val1 VARCHAR2(100));
TYPE mycol_t IS TABLE OF varrec_t;
l_coll1 mycol_t;
l_coll2 mycol_t;
BEGIN
IF l_coll1 = l_coll2 THEN -- will not compile
NULL;
END IF;
END;
ORA-06550: line 9, column 14:
PLS-00306: wrong number or types of arguments in call to '='
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored
Rule 2: Collections must be of EXACTLY the same type. Being based on the same element type is not sufficient.
This will not compile:
SQL> create type type2_nt is table of varchar2(100) -- same as type1_nt
2 /
Type created
SQL> declare
2 l_col1 type1_nt := type1_nt('data');
3 l_col2 type2_nt := type2_nt('data');
4 begin
5 if l_col1 = l_col2 then -- not allowed
6 null;
7 end if;
8 end;
9 /
declare
l_col1 type1_nt := type1_nt('data');
l_col2 type2_nt := type2_nt('data');
begin
if l_col1 = l_col2 then
null;
end if;
end;
ORA-06550: line 6, column 11:
PLS-00306: wrong number or types of arguments in call to '='
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored
Rule 3: If either of the nested tables is unitialized, the result is NULL.
SQL> DECLARE
2 l_col1 type1_nt := type1_nt('data');
3 l_col2 type1_nt;
4 BEGIN
5 p_comp_coll(l_col1, l_col2);
6 END;
7 /
NULL
PL/SQL procedure successfully completed
SQL> DECLARE
2 l_col1 type1_nt;
3 l_col2 type1_nt;
4 BEGIN
5 p_comp_coll(l_col1, l_col2);
6 END;
7 /
NULL
PL/SQL procedure successfully completed
Rule 4: If the collections have different numbers of elements, they are not equal.
SQL> DECLARE
2 l_col1 type1_nt := type1_nt('data', NULL);
3 l_col2 type1_nt := type1_nt('data');
4 BEGIN
5 p_comp_coll(l_col1, l_col2);
6 END;
7 /
NOT EQUAL
PL/SQL procedure successfully completed
SQL> DECLARE
2 l_col1 type1_nt := type1_nt('data');
3 l_col2 type1_nt := type1_nt('data');
4 BEGIN
5 l_col2.extend();
6 p_comp_coll(l_col1, l_col2);
7 END;
8 /
NOT EQUAL
PL/SQL procedure successfully completed
Rule 5: If the collections have the same number of elements but any of those elements are NULL, the collections are not equal. Note that '' is NOT treated as NULL when comparing collections!
SQL> DECLARE
2 l_col1 type1_nt := type1_nt('data',
3 NULL);
4 l_col2 type1_nt := type1_nt('data',
5 NULL);
6 BEGIN
7 p_comp_coll(l_col1,
8 l_col2);
9 END;
10 /
NULL
PL/SQL procedure successfully completed
SQL> DECLARE
2 l_col1 type1_nt := type1_nt('data1',
3 'data2');
4 l_col2 type1_nt := type1_nt('',
5 'data1');
6 BEGIN
7 p_comp_coll(l_col1,
8 l_col2);
9
10 dbms_output.put_line('Coll1 size:' || l_col1.count);
11 dbms_output.put_line('Coll2 size:' || l_col2.count);
12 IF '' IS NULL THEN
13 dbms_output.put_line('yes, '''' is generally considered to be null!');
14 END IF;
15 END;
16 /
NOT EQUAL
Coll1 size:2
Coll2 size:2
yes, '' is generally considered to be null!
PL/SQL procedure successfully completed
Rule 6: If the collections have the same number of elements, none of the elements are NULL, and all of the values are the same regardless of order, the collections are equal.
SQL> DECLARE
2 l_col1 type1_nt := type1_nt('data1',
3 'data2');
4 l_col2 type1_nt := type1_nt('data1',
5 'data2');
6 BEGIN
7 p_comp_coll(l_col1,
8 l_col2);
9 END;
10 /
EQUAL
PL/SQL procedure successfully completed
SQL> DECLARE
2 l_col1 type1_nt := type1_nt('data1',
3 'data2');
4 l_col2 type1_nt := type1_nt('data2',
5 'data1');
6 BEGIN
7 p_comp_coll(l_col1,
8 l_col2);
9 END;
10 /
EQUAL
PL/SQL procedure successfully completed
