Exceptions σε bulkoμπατσιές (Oracle)

Σκεφτόμουν ένα ενδιαφέρον αρθράκι για προγραμματισμό σε Oracle.
Τι καλύτερο για αρχή από βελτίωση του performance των batch διαδικασιών στα insert/update/delete και ταυτόχρονα έλεγχος του αποτελέσματος?
Με τα Pl/Sql tables τα πάμε καλά? Αν όχι πρέπει. Τα χρησιμοποιώ καθημερινά και μπορώ να πω ότι είναι πολύ βολικά. Είτε τα χρησιμοποιούμε με το OCI για να περάσουμε μαζικά δεδομένα από άλλα συστήματα, είτε καθαρά μέσα σε oracle stored procedures για επεξεργασία δεδομένων είναι τόσο εύκολα στη χρήση τους που πραγματικά λύνουν τα χέρια.

Η πιο σύνηθης χρήση τους είναι στις "μαζικές" batchιές, να γραφτούν πολλά rows "με τη μια" στη βάση. Το κάθε πεδίο του πίνακα στον οποίο θα κάνουμε για παράδειγμα insert θα έχει και ένα array. Δέκα πεδία έχει ο πίνακας? Δέκα arrays θα φτιάξουμε, αυτό βέβαια αν το απαιτεί και η σχεδίαση μας. Αν ένα πεδίο έχει σταθερά μια τιμή σε όλα τα records που θα κάνουμε insert (π.χ timestamp) ε εκεί δε χρειάζεται array.

To array insert είναι απλό. Αφού γεμίσουμε τα arrays χρησιμοποιούμε την forall δηλώνοντας πάντα τα bounds του array και τελειώσαμε. Το θέμα είναι τι γίνεται αν κάποιο από αυτά τα records που κάνουμε insert δημιουργήσει exception για παράδειγμα κάποιο duplicate.

Πως ερευνούμε πιο από τα 50000 records που μόλις προσπαθήσαμε να κάνουμε insert έσκασε? Εδώ έρχεται να σώσει την κατάσταση το SQL%BULK_EXCEPTIONS και το save exceptions του forall.

To SQL%BULK_EXCEPTIONS περιλαμβάνει ένα array το οποίο έχει indexes στα rows που αποτύχανε. Αν για παράδειγμα κάνουμε τρία inserts και αποτύχει ΜΟΝΟ το δεύτερο, τότε το SQL%BULK_EXCEPTIONS(1).error_index θα έχει τον αριθμό 2.
Φυσικά δεν θα υπάρχει SQL%BULK_EXCEPTIONS(2).error_index :)

Ένα παράδειγμα χρήσης arrays και saved exceptions είναι το παρακάτω:

Για αρχή πρέπει να φτιάξουμε μερικά types

type typ_dup_record_sns    is table of test_table._sn%type index by binary_integer;
type tableOfNumbers    is table of number index by binary_integer;
v_dup_recs_arr    typ_dup_record_sns;

procedure theFastBatch(    in_sn    in    tableOfNumbers
                  ,    in_name    in    tableOfNumbers
                  ,    in_surname    in    tableOfNumbers
                      ) is

forall_error EXCEPTION;
PRAGMA     exception_init(forall_error, -24381);
v_dup_recs_arr  
begin
  forall i in in_name.first..in_name.last save exceptions
      insert into test_table
      (     sn
      ,    name
      ,    surname
      )    values (
          in_sn(i)
      ,    in_name(i)                                                          
      ,    in_surname(i)                                                          
      );
  logger.info('End batch step 1');
exception
  when forall_error then
      logger.info('forall exception raised');
      --insert into v_dup_recs_arr array all problematic record_sns
      for i in 1..SQL%BULK_EXCEPTIONS.count    loop
          v_dup_recs_arr(i)    :=    SQL%BULK_EXCEPTIONS(i).error_index ;
          logger.info ('Record #'||SQL%BULK_EXCEPTIONS(i).error_index ||' Error code=['||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)||']');
      end loop;
      --Do what ever you want with the duplicates now. For example we can print them to the console
      logger.info('Duplicates found!:');
      for v_dup_recs_idx in v_dup_recs_arr.first.. v_dup_recs_arr.last loop
          logger.info('['||in_sn(v_dup_recs_idx)||'] '||in_surname(v_dup_recs_idx)||' '||in_name(v_dup_recs_idx));
      end loop;
      logger.info('.-');
end;