The cursor for loop that contains DML statements should be refactored to use

Question

If I compiled my procedure I dont get error but I see this text(how Can I use this advice):

A cursor for loop that contains DML statements should be refactored to use BULK COLLECTION and FORALL

If I compiled this I got always score:

start:2013-11-21 08:16:54
end:2013-11-21 08:16:54

My code:

    CREATE OR REPLACE PROCEDURE PROC_AAA
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   company_              VARCHAR2 (2) := 'IT';
   ok_                   BOOLEAN;
   updated_              BOOLEAN;
   error_msg_            VARCHAR2 (4000);
   category_             VARCHAR2 (50);
   exist_atrribute_id_   VARCHAR2 (50);
   exist_value_          NUMBER;
   objid_                ROWID;
   var_rows              NUMBER (5);

   CURSOR cur_dane_wej
   IS
      SELECT *
        FROM dane_wejsciowe_cns
       WHERE status = 'NEW'
      FOR UPDATE;
BEGIN
   DBMS_OUTPUT.put_line (
      'start:' || TO_CHAR (SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));

   FOR cur_dane_wej_read IN cur_dane_wej
   LOOP
      BEGIN
         SAVEPOINT cur_dane_wej_svp;
         updated_ := FALSE;
         error_msg_ := NULL;

         category_ := TERMINAL_API.Get_Category_Id (cur_dane_wej_read.IMEI_NO);

         exist_atrribute_id_ :=
            IFSAPP.SER_NUM_ATTRIBUTE_API.CHECK_EXIST (
               COMPANY_,
               category_,
               cur_dane_wej_read.IMEI_NO,
               cur_dane_wej_read.KOD_CECHY);

         SELECT COUNT (*)
           INTO exist_value_
           FROM IFSAPP.SER_NUM_ATTRIBUTE
          WHERE     SERIAL_NO = cur_dane_wej_read.IMEI_NO
                AND COMPANY = company_
                AND CATEGORY_ID = category_
                AND ATTRIBUTE_ID = cur_dane_wej_read.WARTOSC_CECHY
                AND NVL (VALUE, '_dummy_') =
                       NVL (cur_dane_wej_read.KOD_CECHY, '_dummy_');



         DBMS_OUTPUT.put_line (
               cur_dane_wej_read.KOD_CECHY
            || '  '
            || cur_dane_wej_read.WARTOSC_CECHY);
         DBMS_OUTPUT.put_line (
               'kategoria: '
            || category_
            || '  imei:  '
            || cur_dane_wej_read.IMEI_NO);


         IF exist_atrribute_id_ = 'TRUE'
         THEN
            SELECT objid
              INTO objid_
              FROM SER_NUM_ATTRIBUTE
             WHERE     COMPANY = company_
                   AND CATEGORY_ID = category_
                   AND SERIAL_NO = cur_dane_wej_read.IMEI_NO
                   AND ATTRIBUTE_ID = cur_dane_wej_read.KOD_CECHY;

            DBMS_OUTPUT.put_line ('objid:  ' || objid_);

            IF exist_value_ > 0
            THEN
               DBMS_OUTPUT.put_line ('--> wartosc cechy istnieje ');
               NULL;
            ELSE
               DBMS_OUTPUT.put_line ('zmodyfikowano wartosc cechy');
               updated_ := TRUE;
            END IF;
         ELSE
            DBMS_OUTPUT.put_line (
               'kod cechy nie istnieje.. trwa dodanie nowego kodu cechy');
            --                     IFSAPP.SER_NUM_ATTRIBUTE_API.SET_VALUE (company_, category_id_,cur_dane_wej_read.IMEI_NO, cur_dane_wej_read.KOD_CECHY, cur_dane_wej_read.WARTOSC_CECHY, 'DODANO NOWY KOD CECHY' );
            updated_ := TRUE;
         END IF;

         IF updated_ = TRUE
         THEN
            DBMS_OUTPUT.put_line ('aktualizacja danych w tabeli wejsciowej');

            UPDATE dane_wejsciowe_cns
               SET status = 'DONE',
                   MESSAGE = ' ',
                   DATA_PRZETWORZENIA = SYSDATE
             WHERE CURRENT OF cur_dane_wej;
         ELSE
            UPDATE dane_wejsciowe_cns
               SET status = 'SKIPPED',
                   MESSAGE = ' ',
                   DATA_PRZETWORZENIA = SYSDATE
             WHERE CURRENT OF cur_dane_wej;
         END IF;

         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            ROLLBACK TO cur_dane_wej_svp;
            error_msg_ := 'Błąd:  - ' || SQLCODE || ' -ERROR- ' || SQLERRM;

            UPDATE dane_wejsciowe_cns
               SET status = 'ERROR',
                   MESSAGE = error_msg_,
                   DATA_PRZETWORZENIA = SYSDATE
             WHERE CURRENT OF cur_dane_wej;

            COMMIT;
      END;
   END LOOP;

   COMMIT;
   DBMS_OUTPUT.put_line (
      'end:' || TO_CHAR (SYSDATE, 'YYYY-MM-DD hh24:mi:ss'));
END PROC_AAA;

score 0 · Answer 1 · answered Nov 21 '13 at 07:50

0

Learn to use bulk collections, take a look at this article.

answered Nov 21 '13 at 07:50

Alen Oblak

3,285
13
27

OK but I must use this bulk collection ? maybe Can I use execute immediate ? – Przemek Nov 21 '13 at 08:11
Bulk collect and execute immediate are two totally different things. With bulk collect, you fetch more rows at once into a collection (array). Execute immediate is used for executing dynamic SQL as opposed to static SQL. – Alen Oblak Nov 21 '13 at 08:20

The cursor for loop that contains DML statements should be refactored to use

1 Answers1