Home » SQL & PL/SQL » SQL & PL/SQL » MULTISET EXCEPT - COLLECTION (12C )
MULTISET EXCEPT - COLLECTION [message #674417] Sat, 26 January 2019 11:56 Go to next message
shawaj
Messages: 85
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 #674419 is a reply to message #674417] Sat, 26 January 2019 12:12 Go to previous messageGo to next message
BlackSwan
Messages: 26720
Registered: January 2009
Location: SoCal
Senior Member
Which is "first" table?
Which is "second" table?
Do both tables have Primary Key (on which columns)
What identifies when data does not exist?

Do NOT do in PL/SQL that which can be done in plain SQL; as using plain MERGE statement


>WHEN OTHER NULL is a bug that should be eliminated & NEVER used again.
http://www.orafaq.com/wiki/WHEN_OTHERS
Re: MULTISET EXCEPT - COLLECTION [message #674422 is a reply to message #674417] Sat, 26 January 2019 13:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2963
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 #674423 is a reply to message #674417] Sat, 26 January 2019 17:13 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2963
Registered: January 2010
Location: Connecticut, USA
Senior Member
Another possibility would be bulk collect into multiple collections - one for each column.

SY.
Re: MULTISET EXCEPT - COLLECTION [message #674424 is a reply to message #674423] Sat, 26 January 2019 18:15 Go to previous messageGo to next message
BlackSwan
Messages: 26720
Registered: January 2009
Location: SoCal
Senior Member
What is the advantage & purpose to bring all the desired rows into RAM if all you are going to push them out to disk & into a different table?
It is inefficient & wasteful to use BULK collect when it can be avoided by using single MERGE statement.
Re: MULTISET EXCEPT - COLLECTION [message #674425 is a reply to message #674424] Sat, 26 January 2019 23:42 Go to previous messageGo to next message
shawaj
Messages: 85
Registered: January 2016
Member
Hi BlackSwan,

In my project there is an external process that uploads data in bulk into FIRST_TABLE. Now i want to check that if newly uploaded data exists or not in SECOND_TABLE and will insert into SECOND_TABLE if not exists.
This task can be done using merge statement but my tables has 300 columns and i have to compare all 300 columns so this is not easy by merge statement.

Let's say FIRST_TABLE is ALL_OBJECTS and SECOND_TABLE is T11
Re: MULTISET EXCEPT - COLLECTION [message #674426 is a reply to message #674425] Sun, 27 January 2019 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67227
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
This task can be done using merge statement but my tables has 300 columns and i have to compare all 300 columns so this is not easy by merge statement.
Not easy? It takes less than 1 minute to write the query against user_tab_columns to generate the MERGE statement.
You want to waste time, performances, resources for years just because you don't want to take time to write a single statement?

In addition, are there no PK on your tables? What if in your stage table there are rows with same PK than in the target table but not same other values?

Re: MULTISET EXCEPT - COLLECTION [message #674430 is a reply to message #674425] Sun, 27 January 2019 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 26720
Registered: January 2009
Location: SoCal
Senior Member
shawaj wrote on Sat, 26 January 2019 21:42
Hi BlackSwan,

In my project there is an external process that uploads data in bulk into FIRST_TABLE. Now i want to check that if newly uploaded data exists or not in SECOND_TABLE and will insert into SECOND_TABLE if not exists.
This task can be done using merge statement but my tables has 300 columns and i have to compare all 300 columns so this is not easy by merge statement.

Let's say FIRST_TABLE is ALL_OBJECTS and SECOND_TABLE is T11

Without a Primary Key, you are forced to compare every column in every row in FIRST_TABLE to every row & every column in SECOND_TABLE to determine if a match exists.

I hope that you have sufficient time & system resources to complete such a task.
Re: MULTISET EXCEPT - COLLECTION [message #674432 is a reply to message #674430] Sun, 27 January 2019 09:37 Go to previous messageGo to next message
shawaj
Messages: 85
Registered: January 2016
Member
Ok thanks for your suggestion.
Re: MULTISET EXCEPT - COLLECTION [message #674445 is a reply to message #674432] Mon, 28 January 2019 00:46 Go to previous messageGo to next message
_jum
Messages: 571
Registered: February 2008
Senior Member
Would use plain SQL, something like:

insert into t11 
( 
  select * from all_objects
    minus 
  select * from t11);

[Updated on: Mon, 28 January 2019 00:48]

Report message to a moderator

Re: MULTISET EXCEPT - COLLECTION [message #674490 is a reply to message #674445] Tue, 29 January 2019 13:05 Go to previous messageGo to next message
Bill B
Messages: 1966
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 #674491 is a reply to message #674490] Tue, 29 January 2019 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67227
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and if there are no primary key and unique constraints, otherwise it should a MERGE as already suggested.

Re: MULTISET EXCEPT - COLLECTION [message #674492 is a reply to message #674491] Tue, 29 January 2019 13:14 Go to previous messageGo to next message
Bill B
Messages: 1966
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.
Re: MULTISET EXCEPT - COLLECTION [message #674505 is a reply to message #674490] Wed, 30 January 2019 05:13 Go to previous message
_jum
Messages: 571
Registered: February 2008
Senior Member
Thanks Bill, absolutely agree for production!
Previous Topic: Identify data based on data in other tables
Next Topic: Do I have to use ID as a PK?
Goto Forum:
  


Current Time: Sun Jul 05 03:31:21 CDT 2020