Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Nested Table having more than 1 column comparison using ORACLE MULTISET

572728Oct 1 2009 — edited Nov 6 2009
Dear ALL,

I am using ORACLE 10gR2 Database.

I am currently trying to compare two sets of data that I get from two queries in PL/SQL. I want to use Oracle MULTISET UNION, MULTISET INTERSECT, MULTISET EXCEP for those operations.
But When I am executing the below given code I am getting the error.
PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'

My Code
********************************************************************************************************
declare

type wh_unique_record is record (cpy_key number(22),cnc_id varchar2(22),cust_type_id number,
device_info_id number,scope_id number,excep_id number,
config_date date);
type wh_previous_data_table is table of wh_unique_record;
wh_previous_data wh_previous_data_table;
wh_current_data wh_previous_data_table;
wh_union_data wh_previous_data_table;
wh_intersect_data wh_previous_data_table;

-- This cursor I am using to populate the data into the nested tables
cursor c_previous_data is select cpy_key,cnc_id,cust_type_id,device_info_id,scope_id,excep_id,config_date
from r_cfg_wh_excep
where cpy_key=17278
and trunc(config_date)=trunc(to_date('06-jul-2009','dd-mon-yyyy'));
cursor c_current_data is select cpy_key,cnc_id,cust_type_id,device_info_id,scope_id,excep_id,config_date
from r_cfg_wh_excep
where cpy_key=17278
and trunc(config_date)=trunc(to_date('16-jul-2009','dd-mon-yyyy'));

begin
open c_previous_data;
fetch c_previous_data bulk collect into wh_previous_data;
close c_previous_data;

open c_current_data;
fetch c_current_data bulk collect into wh_current_data;
close c_current_data;

dbms_output.put_line('Previous count '||wh_previous_data.count);
dbms_output.put_line('Current count '||wh_current_data.count);

--FYI: MULTISET UNION IS WORKING.*
wh_union_data := wh_current_data multiset union wh_previous_data;
dbms_output.put_line('New count '||wh_union_data.count);
wh_intersect_data:=wh_current_data MULTISET EXCEPT wh_previous_data;
dbms_output.put_line('The records that are repeating are '||wh_intersect_data.count);


end;
/

Previous count 74062
Current count 74508
New count 148570

PL/SQL procedure successfully completed.
***************************************************************************************************************

FYI: If I comment the MULTISET EXCEPT conditin then I am getting the output for MULTISET UNION.

IS THERE ANY WAY ORACLE IS PROVIDING A FUNCTIONALITY WHERE WE CAN HAVE A NESTED TABLE WITH MULTIPLE COLUMNS AND THAT CAN BE USED IN PL/SQL AND USE BULK OPERATION TO POPULATE THE NESTED TABLE WITH MULTIPLE COLUMNS AND USE THE SAME IN MULTISET EXCEPT, MULTISET EXCEPT ETC.

Appreciate your help on this.


Thanks,
Madhu K.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 4 2009
Added on Oct 1 2009
2 comments
1,930 views