Как улучшить производительность, используя BULK COLLECT, а также поймать, если запись NOT_DATA_FOUND

Question

У нас есть продукты (100 КБ), полученные в форме Excel doc. Сначала были выгружены эти номера продуктов в обычную временную таблицу (GT_ADD_ISBNS);

Из этой временной таблицы мы снова проверяем основные таблицы. Итак, сначала в курсоре (products_cur), проверяя, существует ли продукт или нет. так как мы используем BULK COLLECT для итерации курсора, мы не можем сохранить "продукт не найден", если он не найден в основной таблице (TEMP_NODE_PROPERTIES) .

Как отловить ошибку и обновить ее в таблице GT_ADD_ISBNS для продукта №?

Я не хочу выполнять эту программу последовательно, так как это займет много времени, поэтому использовал массовый сбор для выполнения параллельной обработки.

1) чтобы увидеть, существует ли продукт или нет. 2) чтобы увидеть, имеет ли продукт внутри активов "категорию" или нет. Здесь мы получим несколько узлов, поэтому использовали funtiopns LISTAGG и передали эти значения на 3-й шаг и будут выполняться динамически. 3) чтобы увидеть, имеют ли активы имена файлов, начинающиеся с "%pdf%" или нет.

Таким образом, каждая запись должна обработать эти 3 шага, и окончательные имена документов будут обновлены в таблице для успешной записи. Если на каком-либо из этапов возникнет какая-либо ошибка, сообщение об ошибке будет сохранено для этой записи.

Приведенная ниже хранимая процедура не дает достаточной производительности, как я ожидаю. Пожалуйста, просмотрите и поделитесь своими комментариями

DECLARE
V_NODE_ID  VARCHAR2(20);
V_FILENAME VARCHAR2(100);
V_FINAL_ERRORMESSAGE VARCHAR2(2000);
sql_stmt  VARCHAR2(2000);
limit_in  PLS_INTEGER :=100;

TYPE products_aat IS TABLE OF TEMP_NODE_PROPERTIES%ROWTYPE;

l_products products_aat;

CURSOR products_cur IS
SELECT ANP.*
FROM TEMP_NODE AN,
      TEMP_NODE_PROPERTIES ANP
WHERE
AN.ID=ANP.NODE_ID AND
ANP.TYPE_QNAME = 'product' AND
ANP.STRING_VALUE in(SELECT isbn FROM GT_ADD_ISBNS GT);

BEGIN
--Iterating all Products
OPEN products_cur;
    LOOP
        FETCH products_cur 
            BULK COLLECT INTO l_products LIMIT limit_in;

DBMS_OUTPUT.PUT_LINE('l_employees.COUNT:'||l_products.COUNT);

        FOR indx IN 1 .. l_products.COUNT 
        LOOP
            DBMS_OUTPUT.PUT_LINE('Product:'|| l_products(indx).STRING_VALUE || ':PRODUCT NODE ID:' ||l_products(indx).NODE_ID);
           V_FINAL_ERRORMESSAGE :='';

--Check Whether Product inside the assets are having the Category assigned or not
SELECT DISTINCT LISTAGG(ANP.NODE_ID, ',') 
WITHIN GROUP (ORDER BY ANP.NODE_ID) INTO V_NODE_ID
        FROM 
        TEMP_NODE_PROPERTIES ANP,
        TEMP_NODE_PROPERTIES ANPP,
        TEMP_NODE AN
        WHERE 
      ANP.NODE_ID=AN.ID AND
      ANPP.NODE_ID=ANP.NODE_ID AND
      AN.TYPE_QNAME ='asset' AND
      --required catgeories
   (ANP.STRING_VALUE ='uPDF' AND
    ANPP.STRING_VALUE ='Entire PDF')  AND
    ANP.TYPE_QNAME='categories'  AND
        ANP.NODE_ID IN(SELECT CHILD_ID 
                  FROM TEMP_CHILD_ASSOC START WITH PARENT_ID IN(
                 SELECT CHILD_ID FROM TEMP_CHILD_ASSOC WHERE NAME in( l_products(indx).STRING_VALUE)) 
                      CONNECT BY PRIOR CHILD_ID = PARENT_ID);


DBMS_OUTPUT.PUT_LINE('products_cur.ISBN:'|| l_products(indx).STRING_VALUE || ':ASSET NODEIDS:' ||V_NODE_ID);

 --Check whether Product inside the assets have applied the 'category' But those assets are having documents LIKE '%pdf%' or not    
  sql_stmt := 'SELECT LISTAGG(ANP.STRING_VALUE, '','') WITHIN GROUP (ORDER BY ANP.STRING_VALUE)         FROM        TEMP_NODE_PROPERTIES ANP
        WHERE   ANP.TYPE_QNAME=''name''  AND  (( ANP.STRING_VALUE LIKE ''%pdf%''  AND  ANP.STRING_VALUE NOT LIKE ''%pod%'') 
       )  AND  ANP.NODE_ID IN (' || V_NODE_ID ||')';


  if(V_NODE_ID  IS NOT NULL)  THEN
  EXECUTE IMMEDIATE sql_stmt into V_FILENAME;
  else
   V_FINAL_ERRORMESSAGE:=V_FINAL_ERRORMESSAGE|| 'Category is not applied to none of the Assets for this Product';
   DBMS_OUTPUT.PUT_LINE('Category is not applied to none of the Assets for this Product:'|| l_products(indx).STRING_VALUE );
   UPDATE GT_ADD_ISBNS SET CATEGORY11_ERROR_MESSAGE=  V_FINAL_ERRORMESSAGE  WHERE ISBN= l_products(indx).STRING_VALUE;
  GOTO CATEGORY_NOT_FOUND;
  END IF;


   IF(V_FILENAME is NULL) THEN
    DBMS_OUTPUT.PUT_LINE(V_FINAL_ERRORMESSAGE|| 'Category is applied for this Product But for the asset:'||  V_NODE_ID|| ':Documents[LIKE %pdf% AND LIKE %pod% ] were not found ;');
    V_FINAL_ERRORMESSAGE:=V_FINAL_ERRORMESSAGE|| 'Category is applied for this Product But for the asset:'||  V_NODE_ID|| ':Documents[LIKE %pdf% AND LIKE %pod% ] were not found ;';
     UPDATE GT_ADD_ISBNS SET CATEGORY11_ERROR_MESSAGE=  V_FINAL_ERRORMESSAGE  WHERE ISBN= l_products(indx).STRING_VALUE;
   END IF;

   DBMS_OUTPUT.PUT_LINE( l_products(indx).STRING_VALUE || '..Final documents for the Product:'||V_FILENAME);
      UPDATE GT_ADD_ISBNS SET CATEGORY11_FILENAME=V_FILENAME WHERE ISBN= l_products(indx).STRING_VALUE;

        <<CATEGORY_NOT_FOUND>> 
        NULL; 
        END LOOP;




        EXIT WHEN l_products.COUNT < limit_in;

   END LOOP;

   CLOSE products_cur;

END;