how to improve performance using BULK COLLECT and also catch if record NOT_DATA_FOUND

We have products(100k) received in the form of excel doc.Initially ,dumped those product numbers into the normal temp table(GT_ADD_ISBNS);

From this temp table ,we are checking againt main tables.So initially in the cursor (products_cur),checking whether the product exists or not.
as we are using BULK COLLECT to iterate the cursor so not able to store 'product not found' if it not found in the main table(TEMP_NODE_PROPERTIES) .

How to catch the error and update it in GT_ADD_ISBNS table against product no?

I don't want to execute this program sequentially as this will take lot of time ,So used bulk collect to perform parallel processing.

1)to see whether product is exists or not.
2)to see whether product inside the assets are having the 'category' or not.here we will got multiple nodes ,so used LISTAGG funtiopns and passed those values to the 3rd step and will executed dynamically.
3)to see whether the assets are having file names starts with '%pdf%' or not.

So each record has to process these 3 steps and final document names will be updated in the table for the successful record. If any error comes in any of the steps then error message will be stored for that record.

Please find the attached sample data and DDL.

Below stored procedure is not giving enough performance as I am expecting.Can please review and share your comments.

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;

Select allOpen in new window

add_sample_data.zip