| comparing collections [message #617848] |
Fri, 04 July 2014 01:16  |
 |
purnima1
Messages: 79
Registered: June 2014
|
Member |
|
|
declare
cursor c_lri_data is select c1,c2 from tab1 order by c1;
type tab_lri_data is table of c_lri_data%rowtype index by binary_integer;
t_lri_data tab_lri_data;--lri rec
cursor c_ce_data is select c1,c2 from tab1 order by c1 ;
type tab_ce_data is table of c_ce_data%rowtype index by binary_integer;
t_ce_data tab_ce_data;---ce rec
begin
open c_lri_data;
fetch c_lri_data bulk collect into t_lri_data;
close c_lri_data;
open c_ce_data;
fetch c_ce_data bulk collect into t_ce_data;
close c_ce_data;
IF t_lri_data = t_ce_data THEN
DBMS_OUTPUT.PUT_LINE (
'both collections are equal');
END IF;
end;
/
getting and error while compilation
wrong number or types of arguments in call to '='
can't we compare collections like this
[Edit MC: fix title typo]
[Updated on: Fri, 04 July 2014 10:05] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: coparing collections [message #618060 is a reply to message #618050] |
Mon, 07 July 2014 07:27  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
For instance:
SQL> create table emp2 as select * from emp where ename != 'KING';
Table created.
SQL> update emp2 set sal=sal+100 where mod(empno,2)=0;
10 rows updated.
SQL> commit;
Commit complete.
SQL> create or replace type employee is object (
2 ename varchar2(10),
3 sal number(7,2)
4 )
5 /
Type created.
SQL> create or replace type employees is table of employee
2 /
Type created.
SQL> declare
2 empl employees;
3 empl2 employees;
4 nb pls_integer := 0;
5 begin
6 select employee(ename,sal) bulk collect into empl from emp;
7 select employee(ename,sal) bulk collect into empl2 from emp2;
8 dbms_output.new_line;
9 select 1 into nb
10 from ( select * from table(empl)
11 minus
12 select * from table(empl2) )
13 where rownum = 1;
14 dbms_output.put_line ('There are differences');
15 exception
16 when no_data_found then
17 dbms_output.put_line ('No differences');
18 end;
19 /
There are differences
PL/SQL procedure successfully completed.
SQL> truncate table emp2;
Table truncated.
SQL> insert into emp2 select * from emp;
14 rows created.
SQL> declare
2 empl employees;
3 empl2 employees;
4 nb pls_integer := 0;
5 begin
6 select employee(ename,sal) bulk collect into empl from emp;
7 select employee(ename,sal) bulk collect into empl2 from emp2;
8 dbms_output.new_line;
9 select 1 into nb
10 from ( select * from table(empl)
11 minus
12 select * from table(empl2) )
13 where rownum = 1;
14 dbms_output.put_line ('There are differences');
15 exception
16 when no_data_found then
17 dbms_output.put_line ('No differences');
18 end;
19 /
No differences
PL/SQL procedure successfully completed.
Of course you can do it in a single query and have not to use collections to do this:
SQL> declare
2 nb pls_integer := 0;
3 begin
4 dbms_output.new_line;
5 select 1 into nb
6 from ( select * from emp
7 minus
8 select * from emp2)
9 where rownum = 1;
10 dbms_output.put_line ('There are differences');
11 exception
12 when no_data_found then
13 dbms_output.put_line ('No differences');
14 end;
15 /
No differences
PL/SQL procedure successfully completed.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
