2 Replies Latest reply: May 8, 2014 7:12 AM by kdonato RSS

    Compare Collection with Table using Minus

    kdonato

      Hey there!

       

      my plan is to upload an excel file and save it into an apex_collection, compare the results with those already existing in a specific table and write it into another temporary table.

      The excel import into the collection is working flawlessly but i'm struggling with the comparation. I'm selecting the entries form the collection and using the MINUS-function to compare the rows with those from another SELECT which gets the rows out of the existing table.

       

      DECLARE

      BEGIN

      INSERT INTO UPLOADED_DID_LIST_COMP(BAC_CODE,UPSC)

      (

        SELECT             c001 BAC_CODE

                          ,c002 UPSC

                    FROM   apex_collections ca

                   WHERE collection_name = 'UPLOADED_EXCEL'

                    MINUS

                    SELECT

                          BAC_CODE

                          ,UPSC

                        FROM UPLOADED_DID_LIST upld, apex_collections c WHERE

                                                  collection_name = 'UPLOADED_EXCEL'

                                                  AND   upld.BAC_CODE = c.C001

                                                  AND   upld.UPSC = c.C002

      );

      End;

       

       

      This isn't working because of this errors:

       

      ORA-00933: SQL command not properly ended ORA-06550

      PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quot