MULTISET EXCEPT - COLLECTION [message #674417] |
Sat, 26 January 2019 11:56 |
|
shawaj
Messages: 89 Registered: January 2016
|
Member |
|
|
Hi all,
I have two table with same structure. One process load a set of data into first table and i need to check the same data is exists in second table, if newly inserted data into first table does not exists in second table then i want to add it into second table.
CREATE TABLE T11 AS SELECT * FROM ALL_OBJECTS;
SQL> DECLARE
2 TYPE ALL_OBJECTS_NT IS TABLE OF ALL_OBJECTS%ROWTYPE;
3 V_T11_TAB ALL_OBJECTS_NT;
4 V_ALL_TAB ALL_OBJECTS_NT;
5 V_MISMATCH ALL_OBJECTS_NT:=ALL_OBJECTS_NT();
6
7 BEGIN
8
9 SELECT * BULK COLLECT INTO V_ALL_TAB FROM ALL_OBJECTS;
10 SELECT * BULK COLLECT INTO V_T11_TAB FROM T11;
11
12 V_MISMATCH := V_ALL_TAB MULTISET EXCEPT V_T11_TAB;
13
14 FOR I IN V_MISMATCH.FIRST..V_MISMATCH.LAST LOOP
15 DBMS_OUTPUT.PUT_LINE(V_MISMATCH(I).OBJECT_NAME);
16 END LOOP;
17
18 EXCEPTION WHEN OTHERS THEN
19 NULL;
20 END;
21 /
V_MISMATCH := V_ALL_TAB MULTISET EXCEPT V_T11_TAB;
*
ERROR at line 12:
ORA-06550: line 12, column 16:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'
ORA-06550: line 12, column 2:
PL/SQL: Statement ignored
|
|
|
|
Re: MULTISET EXCEPT - COLLECTION [message #674422 is a reply to message #674417] |
Sat, 26 January 2019 13:51 |
Solomon Yakobson
Messages: 3284 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Obviously it will not work. You declared table of records. In order to do MULTISET EXCEPT Oracle needs to compare nested table elements, so it has to compare elements of RECORD type while PL/SQL doesn't support comparing RECORDs:
SQL> DECLARE
2 V_ALL_OBJECTS_RECORD1 ALL_OBJECTS%ROWTYPE;
3 V_ALL_OBJECTS_RECORD2 ALL_OBJECTS%ROWTYPE;
4 BEGIN
5 IF V_ALL_OBJECTS_RECORD1 = V_ALL_OBJECTS_RECORD2
6 THEN
7 DBMS_OUTPUT.PUT_LINE('Equal');
8 END IF;
9 END;
10 /
IF V_ALL_OBJECTS_RECORD1 = V_ALL_OBJECTS_RECORD2
*
ERROR at line 5:
ORA-06550: line 5, column 30:
PLS-00306: wrong number or types of arguments in call to '='
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored
SQL>
You would have to declare SQL object type with MAP method to compare objects, then you will be able to use intersect.
SY.
|
|
|
|
|
|
|
|
|
|
Re: MULTISET EXCEPT - COLLECTION [message #674490 is a reply to message #674445] |
Tue, 29 January 2019 13:05 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
I agree with jum with one exception. I hate using an insert statement without having a column list. If this is production code and either table is altered the insert with the minus will fail. I would write it as
insert into t11(col1,col2,col3,col4...col300)
(
select col1,col2,col3,col4...col300 from all_objects
minus
select col1,col2,col3,col4...col300 from t11);
[Updated on: Tue, 29 January 2019 13:06] Report message to a moderator
|
|
|
|
Re: MULTISET EXCEPT - COLLECTION [message #674492 is a reply to message #674491] |
Tue, 29 January 2019 13:14 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
absolutely a merge is the way to go if there are unique indexes to test against. The OP should attempt to find unique information so it can be indexed and a merge used. Baring that a minus query will be the fastest method to use.
Thanks Michel.
|
|
|
|