Hi guys,
I'm running on Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
I started getting very strange results when doing collection data comparison within PLSQL.
I've managed to narrow down the issue to a test case below.
Can you please point me to where I'm doing things wrong?
It seems very dirty, that I can get expected results in some case and i cannot in another.
Here is the test case I'm executing:
set serveroutput on
create or replace type an_array as table of varchar2(32767);
/
declare
expected an_array;
actual an_array;
procedure print_element(a_element_id integer, a_array an_array, a_array_name varchar2) is
begin
if a_array.exists(a_element_id) then
dbms_output.put(a_array_name||'('||a_element_id||')=|'||a_array(a_element_id)||'| ; ');
else
dbms_output.put(a_array_name||'('||a_element_id||') does not exist ; ');
end if;
end;
begin
expected := an_array('','a','','c','d','');
if expected = expected then
dbms_output.put_Line('OK, can compare collections build in PLSQL and they are equal');
end if;
select * bulk collect into actual from table(expected);
if expected = actual then
dbms_output.put_Line('OK, can compare collections build in PLSQL and SQL and they are equal');
else
dbms_output.put_Line('ERROR, collections build in SQL and PLSQL cannot be compared in PLSQL');
for i in 1 .. greatest(actual.count, expected.count) loop
print_element(i, expected, 'expected');
print_element(i, actual, 'actual');
dbms_output.put_line(null);
end loop;
end if;
if actual = actual then
dbms_output.put_Line('OK, can compare collections build in SQL and they are equal');
else
dbms_output.put_Line('ERROR, collections build in SQL cannot be compared in PLSQL successfully');
end if;
end;
/
And here are the results I'm getting
Type AN_ARRAY compiled
PL/SQL procedure successfully completed.
OK, can compare collections build in PLSQL and they are equal
ERROR, collections build in SQL and PLSQL cannot be compared in PLSQL
expected(1)=|| ; actual(1)=|| ;
expected(2)=|a| ; actual(2)=|a| ;
expected(3)=|| ; actual(3)=|| ;
expected(4)=|c| ; actual(4)=|c| ;
expected(5)=|d| ; actual(5)=|d| ;
expected(6)=|| ; actual(6)=|| ;
ERROR, collections build in SQL cannot be compared in PLSQL successfully
I would expect to get a positive result for comparison regardless of where the data is coming from.
Thanks for your help
Jacek
Yeah, there's a few inconsistencies with empty string vs null in PLSQL. That dates back a while, eg check out this thread:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5984520277372 and similarly, out of that conversation came
Bug 13855494 : '' IS NOT CONSIDERED NULL INCONSISTENTLY IN PL/SQL
Here's some more examples based on yours showing some on the "interesting" results
SQL> set serveroutput on
SQL>
SQL> create or replace type an_array as table of varchar2(100);
2 /
Type created.
--
-- all non-null
--
SQL>
SQL> declare
2 expected an_array;
3 actual1 an_array;
4 actual2 an_array;
5
6 begin
7
8 expected := an_array('b','a','f','c','d','e');
9 actual1 := expected;
10
11 if actual1 = expected then
12 dbms_output.put_Line('1 compare OK');
13 else
14 dbms_output.put_Line('1 compare NOT OK');
15 end if;
16
17 select * bulk collect into actual2 from table(expected);
18 if actual2 = expected then
19 dbms_output.put_Line('2 compare OK');
20 else
21 dbms_output.put_Line('2 compare NOT OK');
22 end if;
23
24 dbms_output.put_Line('expected.count = '||expected.count);
25 dbms_output.put_Line('actual2.count = '||actual2.count);
26 end;
27 /
1 compare OK
2 compare OK
expected.count = 6
actual2.count = 6
PL/SQL procedure successfully completed.
--
-- some explicitly null
--
SQL>
SQL> declare
2 expected an_array;
3 actual1 an_array;
4 actual2 an_array;
5
6 begin
7
8 expected := an_array(null,'a',null,'c','d',null);
9 actual1 := expected;
10
11 if actual1 = expected then
12 dbms_output.put_Line('1 compare OK');
13 else
14 dbms_output.put_Line('1 compare NOT OK');
15 end if;
16
17 select * bulk collect into actual2 from table(expected);
18 if actual2 = expected then
19 dbms_output.put_Line('2 compare OK');
20 else
21 dbms_output.put_Line('2 compare NOT OK');
22 end if;
23
24 end;
25 /
1 compare NOT OK
2 compare NOT OK
PL/SQL procedure successfully completed.
--
-- some "null" via empty string
--
SQL>
SQL>
SQL> declare
2 expected an_array;
3 actual1 an_array;
4 actual2 an_array;
5
6 begin
7
8 expected := an_array('','a','','c','d','');
9 actual1 := expected;
10
11 if actual1 = expected then
12 dbms_output.put_Line('1 compare OK');
13 else
14 dbms_output.put_Line('1 compare NOT OK');
15 end if;
16
17 select * bulk collect into actual2 from table(expected);
18 if actual2 = expected then
19 dbms_output.put_Line('2 compare OK');
20 else
21 dbms_output.put_Line('2 compare NOT OK');
22 end if;
23
24 end;
25 /
1 compare OK
2 compare NOT OK
PL/SQL procedure successfully completed.
--
-- some empty string via variable
--
SQL>
SQL> declare
2 expected an_array;
3 actual1 an_array;
4 actual2 an_array;
5
6 elem varchar2(100) := '';
7 begin
8
9 expected := an_array(elem,'a',elem,'c',elem,'');
10 actual1 := expected;
11
12 if actual1 = expected then
13 dbms_output.put_Line('1 compare OK');
14 else
15 dbms_output.put_Line('1 compare NOT OK');
16 end if;
17
18 select * bulk collect into actual2 from table(expected);
19 if actual2 = expected then
20 dbms_output.put_Line('2 compare OK');
21 else
22 dbms_output.put_Line('2 compare NOT OK');
23 end if;
24
25 end;
26 /
1 compare OK
2 compare NOT OK
PL/SQL procedure successfully completed.
--
-- some null via variable
--
SQL>
SQL> declare
2 expected an_array;
3 actual1 an_array;
4 actual2 an_array;
5
6 elem varchar2(100);
7 begin
8
9 expected := an_array(elem,'a',elem,'c',elem,'');
10 actual1 := expected;
11
12 if actual1 = expected then
13 dbms_output.put_Line('1 compare OK');
14 else
15 dbms_output.put_Line('1 compare NOT OK');
16 end if;
17
18 select * bulk collect into actual2 from table(expected);
19 if actual2 = expected then
20 dbms_output.put_Line('2 compare OK');
21 else
22 dbms_output.put_Line('2 compare NOT OK');
23 end if;
24
25 end;
26 /
1 compare NOT OK
2 compare NOT OK
PL/SQL procedure successfully completed.
SQL>
SQL>