4 Replies Latest reply on Feb 17, 2013 7:02 AM by Etbin

    count records already exist

    Gor_Mahia
      all,
      Ive a block with insert into table1 ...statement and in the same block ive sub-block to update another table after inserting the first. But i want to keep count of records which cannot insert because they are already in the table1, so if it throw UNIQUE VIOLATION i need to count that, so i did something like ....
      EXCEPTION
               WHEN WHEN DUP_VAL_ON_INDEX THEN
             count1 := count1 +1;
      .....
      but this isnt being computed and i know there exist records already in that table.. where iam going wrong ?

      best regards,
        • 1. Re: count records already exist
          jeneesh
          It is understood that you are doing ROW BY ROW processing in a loop. Is there any valid reason for doing that?

          It is always SLOW compared to a SINGLE (or two) DML statement with which you can do the same thing..

          It is highly unlikely that you will require to do looping.. SQL is very powerful now a days.. In the worst case, you will be able to use BULK COLLECT and FORALL

          Better please explain your actual logic, with your db version, current code, sample data and expected output.. People will be able to help you to achieve the same result in the best possible method..

          And you may look into DML ERROR LOGGING for capturing errors in DML..
          • 2. Re: count records already exist
            Gor_Mahia
            jeneesh      
            right iam looping becz values are fetched then validated record-wise....so i just ive to use a loop but of course a bit slower than other options....thanks.
            • 3. Re: count records already exist
              jeneesh
              Gor_Mahia wrote:
              jeneesh      
              right iam looping becz values are fetched then validated record-wise....so i just ive to use a loop but of course a bit slower than other options....thanks.
              For validation, you can use DML ERROR LOGGING..
              • 4. Re: count records already exist
                Etbin
                You can count "would be" violations separately - not within loops (exists used because it sounds most illustrative)
                select count(*)
                  into violation_count
                  from target_table t
                 where exists(select null
                                from source_table
                               where the_key = t.the_key
                             )
                Regards

                Etbin