en vrac collecte à l'aide de “mises à jour”

Je tombe sur un intéressant et inattendu problème lors du traitement des dossiers dans Oracle (11g) à l'aide de BULK COLLECT.

Le code suivant a été fonctionne à merveille, le traitement par le biais de tous les plus d'un million d'enregistrements avec une question:

-- Define cursor
cursor My_Data_Cur Is
Select col1
      ,col2
from My_Table_1;
…

-- Open the cursor
open My_Data_Cur;

-- Loop through all the records in the cursor
loop

  -- Read the first group of records
  fetch My_Data_Cur
  bulk collect into My_Data_Rec
  limit 100;

  -- Exit when there are no more records to process
  Exit when My_Data_Rec.count = 0;

  -- Loop through the records in the group
  for idx in 1 .. My_Data_Rec.count
  loop
    … do work here to populate a records to be inserted into My_Table_2 …
  end loop;

  -- Insert the records into the second table
  forall idx in 1 .. My_Data_Rec.count
  insert into My_Table_2…;

  -- Delete the records just processed from the source table
  forall idx in 1 .. My_Data_Rec.count
  delete from My_Table_1 …;

  commit;
end loop;

Car, à la fin du traitement de chaque groupe de 100 dossiers (maximum 100) nous sommes supprimer les enregistrements viens de lire et de traiter, j'ai pensé que ce serait une bonne idée d'ajouter les “mises à jour” de la syntaxe de la définition de curseur de sorte qu'un autre processus ne pouvais pas mettre à jour tous les enregistrements entre le moment où les données ont été lues et le moment où l'enregistrement est supprimé.

Alors, la seule chose dans le code que j'ai modifié a été...

cursor My_Data_Cur
is
  select col1
        ,col2
from My_Table_1
for update;

Quand j'ai couru le PL/SQL package d'après ce changement, le travail ne traite 100 enregistrements et puis s'arrête. J'ai confirmé ce changement a été à l'origine du problème, en supprimant les “mises à jour” à partir du curseur et une fois de plus le paquet traité tous les enregistrements de la table source.

Des idées pourquoi l'ajout de la rubrique “mise à jour” clause serait la cause de ce changement de comportement? Des suggestions sur la façon de contourner ce problème? Je vais essayer de lancer une exclusivité de la transaction sur la table au début du processus, mais ce n'est pas une idée de solution parce que je ne veux vraiment pas à verrouiller la totalité de la table de la procédure de traitement des données.

Merci d'avance pour votre aide,

Subvention

OriginalL'auteur Grant | 2014-02-07

  1. 1

    Le problème, c'est que vous essayez de faire une extraction à travers un commit.

    Lorsque vous ouvrez My_Data_Cur avec le for update clause, Oracle a pour verrouiller toutes les lignes de la My_Data_1 table avant de pouvoir renvoyer toutes les lignes. Lorsque vous commit, Oracle a de libérer toutes les personnes de serrures (serrures Oracle crée ne couvrent pas les transactions). Depuis le curseur n'a plus les verrous que vous avez demandé, Oracle a pour fermer le curseur depuis il ne peut plus satisfaire la for update clause. La seconde extraction, par conséquent, doit retourner 0 lignes.

    L'approche la plus logique serait presque toujours à supprimer la commit et de faire la chose entière dans une seule transaction. Si vous avez vraiment, vraiment, vraiment besoin de séparer les transactions, vous devez ouvrir et fermer le curseur pour chaque itération de la boucle. Très probablement, vous voulez faire quelque chose pour limiter le curseur afin de retourner uniquement 100 lignes à chaque fois qu'il est ouvert (c'est à dire un rownum <= 100 clause), de sorte que vous ne seriez pas à supporter les frais de la visite de chaque ligne à la place de la serrure, puis chaque ligne autre que la 100 que vous avez traité et supprimés pour libérer le verrouillage de tous les temps à travers la boucle.

    Justin - je vous Remercie pour la réponse rapide et j'apprécie le fait que vous avez énuméré un couple d'options!

    OriginalL'auteur Justin Cave

  2. 1

    L'ajout de Justin Explication.

    Vous devriez avoir vu le message d'erreur ci-dessous.Pas sûr, si votre Exception gestionnaire supprimé ce.

    Et le message lui-même explique Beaucoup de choses!

    Pour ce genre de Mises à jour, il est préférable de créer un cliché instantané de la table principale, et de laisser le public synonyme de point. Alors que certains l'id du lot, crée un synonyme privé à notre table principale et d'effectuer des opérations de traitement par lots, pour plus de simplicité pour l'entretien.

    Error report -
ORA-01002: fetch out of sequence
ORA-06512: at line 7
01002. 00000 -  "fetch out of sequence"
*Cause:    This error means that a fetch has been attempted from a cursor
           which is no longer valid.  Note that a PL/SQL cursor loop
           implicitly does fetches, and thus may also cause this error.
           There are a number of possible causes for this error, including:
           1) Fetching from a cursor after the last row has been retrieved
           and the ORA-1403 error returned.
           2) If the cursor has been opened with the FOR UPDATE clause,
           fetching after a COMMIT has been issued will return the error.
           3) Rebinding any placeholders in the SQL statement, then issuing
           a fetch before reexecuting the statement.
*Action:   1) Do not issue a fetch statement after the last row has been
           retrieved - there are no more rows to fetch.
           2) Do not issue a COMMIT inside a fetch loop for a cursor
           that has been opened FOR UPDATE.
           3) Reexecute the statement after rebinding, then attempt to
           fetch again.

    Aussi, vous pouvez vous changer de Logique en Utilisant rowid

    Un Exemple pour Docs:

    DECLARE
-- if "FOR UPDATE OF salary" is included on following line, an error is raised
   CURSOR c1 IS SELECT e.*,rowid FROM employees e;
   emp_rec  employees%ROWTYPE;
BEGIN
   OPEN c1;
   LOOP
     FETCH c1 INTO emp_rec; -- FETCH fails on the second iteration with FOR UPDATE
     EXIT WHEN c1%NOTFOUND;
     IF emp_rec.employee_id = 105 THEN
       UPDATE employees SET salary = salary * 1.05 WHERE rowid = emp_rec.rowid;
         -- this mimics WHERE CURRENT OF c1
     END IF;
     COMMIT;  -- releases locks
   END LOOP;
END;
/

    Vous avez à aller chercher une ligne d'enregistrement par ligne!! mise à jour à l'aide de la ROWID ET de s'ENGAGER immédiatement
    . Puis passez à la ligne suivante!

    Mais par ce, vous devez donner le Bulk Binding option.

    Ce code est le code SQL dynamique et je n'ai pas recouvrement de l'exception correctement et c'est pourquoi je n'ai pas le message d'erreur. L'ancien code ne l'ai pas fait en vrac recueillir et était en cours d'exécution d'environ 3 jours. Je l'ai réécrit en utilisant la densité de collecter et c'est maintenant en cours d'exécution en 6 heures environ. J'ai raté le "mises à JOUR" paramètre de mon réécriture et il a été capturé au cours d'une revue de code. J'ai enlevé le COMMIT qui signifie que le processus sera un tout ou rien, de l'emploi, j'ai ajouté de l'engagement dans le code uniquement si l'on pouvait arrêter le travail si nécessaire et de ne pas perdre le travail déjà accompli. Merci pour vos commentaires.
    Merci, C'est juste reconnaître les enjeux de mes travaux avec moi!!

    OriginalL'auteur Maheswaran Ravisankar