asked on

how to capture different error messages in multiple SQL cursors

How to capture the error messages in multiple cursors.I am getting compile time error at line:75[EXCEPTION WHEN no_CATEGORY_exp THEN ];

in the FIRST for loop if the query doesnt fetch any rows then want to throw NO_DATA_FOUND ERROR for product.
in the second for loop if the query doesnt fetch any rows then want to throw NO_DATA_FOUND ERROR for product..
how to show differnt error message for first and second queries;

please note that it should iterate alll records from global table even of any error thrown by the respective queries.

DECLARE

--V_PRODUCTISBN VARCHAR2(100);
V_NODE_ID VARCHAR2(20);
V_NODEID VARCHAR2(20);
V_FILENAME VARCHAR2(100);
isbn VARCHAR2(100);
V_CATEGORY_COUNT INTEGER :=0;
no_CATEGORY_exp EXCEPTION;

CURSOR C1 IS
SELECT ISBN FROM GT_ADD_ISBNS;

CURSOR C2(isbn in varchar2) IS
SELECT ANP.NODE_ID NODE_ID
            FROM
            ALF_NODE_PROPERTIES ANP,
ALF_NODE_PROPERTIES ANPP,
            ALF_NODE AN
            WHERE
      ANP.NODE_ID=AN.ID AND
ANPP.NODE_ID=ANP.NODE_ID AND
AN.TYPE_QNAME_ID IN(SELECT ID FROM ALF_QNAME WHERE LOCAL_NAME='asset') AND
ANP.QNAME_ID=52 AND
           ANP.NODE_ID IN(SELECT CHILD_NODE_ID
FROM ALF_CHILD_ASSOC START WITH PARENT_NODE_ID IN(
SELECT CHILD_NODE_ID FROM ALF_CHILD_ASSOC WHERE QNAME_LOCALNAME in(isbn) )
CONNECT BY PRIOR CHILD_NODE_ID = PARENT_NODE_ID);

BEGIN

FOR R1 IN C1
LOOP

BEGIN

SELECT AN.ID INTO V_NODE_ID
FROM ALF_NODE AN,
ALF_NODE_PROPERTIES ANP
WHERE
an.id=anp.NODE_ID AND
ANP.QNAME_ID=639 and
ANP.STRING_VALUE in(R1.ISBN);

DBMS_OUTPUT.PUT_LINE('.R1.ISBN........' || R1.ISBN);

FOR R2 IN C2(R1.ISBN)
LOOP

V_CATEGORY_COUNT := C2%ROWCOUNT;

DBMS_OUTPUT.PUT_LINE('R1.ISBN:'|| R1.ISBN || ':R1.NODE_ID:' || R2.NODE_ID); --|| ':FILENAME:'|| R2.FILENAME);

DBMS_OUTPUT.PUT_LINE('V_CATEGORY_COUNT...'|| V_CATEGORY_COUNT);
CASE V_CATEGORY_COUNT
WHEN 0 THEN
RAISE no_CATEGORY_exp;
ELSE
NULL;
END CASE;

EXCEPTION WHEN no_CATEGORY_exp THEN
DBMS_OUTPUT.PUT_LINE('Assets not Found for the Category-11...'|| R1.ISBN);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 100));

END LOOP;

EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('PRODUCT NOT FOUND..AT LINE 78...'|| R1.ISBN);
END;

end loop;

END;

flow01

You coded the exception not at the end of a pl/sql-block.
I don't know the logic you want but for syntax correction

FOR R2 IN C2(R1.ISBN)
LOOP
BEGIN -- start the pl/sql block
..
EXCEPTION
...
END; -- end the pl/sql block
END LOOP;

chaituu

ASKER

This is the final pseudo code that I have come up with.its working as expected.it's throwing errors when no_data_found occurs.I will be happy if anybody throw any suggestions to improve this code mainly when no_data_found errors.

DECLARE


CURSOR C1 IS
SELECT isbn FROM GT_ADD_ISBNS;

CURSOR C2(v_isbn in varchar2) IS
SELECT ANP.NODE_ID NODE_ID
		FROM 
		TABLE1 ANP,
		TABLE2 AN
		WHERE 
	   ANP.NODE_ID=AN.ID AND
		ANP.STRING_VALUE=v_isbn;


BEGIN

--Iterating all Products
FOR R1 IN C1 
LOOP

BEGIN


--To check whether Product is exists or not
SELECT AN.ID INTO V_NODE_ID 
FROM TABLE1 ANP
WHERE
ANP.STRING_VALUE in(R1.ISBN);


V_CATEGORY_COUNT :=0;

FOR R2 IN C2(R1.ISBN) 
LOOP

V_CATEGORY_COUNT := V_CATEGORY_COUNT+1;  
 
BEGIN

--Check if Document is found or not
SELECT ANP.STRING_VALUE  into V_FILENAME
		FROM 
		TABLE3
		WHERE 
       ANP.NODE_ID=R2.NODE_ID; 
   
   EXCEPTION WHEN
     NO_DATA_FOUND THEN
	  DBMS_OUTPUT.PUT_LINE('.Document is not found ');
     END;

END LOOP; //end of C2 Loop

  

 IF(V_CATEGORY_COUNT  = 0) THEN
    DBMS_OUTPUT.PUT_LINE('.Category is not found ');
   END IF;  
 
 
EXCEPTION WHEN
NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE('PRODUCT NOT FOUND:::::::::::::'|| R1.ISBN);
END;
  
COMMIT;
END LOOP;--looping gt_isbns
END;

Open in new window

Geert G

try indenting your code correctly.
it will be way easier to read and you will be able to see what try matches what exception

try not to commit in a procedure or inside a loop

chaituu

ASKER

Its working as expected. Do you think code written for catching NO_DATA_FOUND is correct in all three scenarios?I want to commit the ISBN record after iterating each ISBN.It should not rollback all records if any error is thrown for any of one of the record. I have written pl/sql block,

flow01

I don't see any insert/update/delete that has to be committed. It depends on the number of such statements and the location of that statement(s) within the loops to determine if you need to commit or rollback on row / loop-level or just need 1 commit at the end.

chaituu

ASKER

oops.sorry guys.i have written update statements in the no_data_data statements to capture error messages.

DECLARE


CURSOR C1 IS
SELECT isbn FROM GT_ADD_ISBNS;

CURSOR C2(v_isbn in varchar2) IS
SELECT ANP.NODE_ID NODE_ID
		FROM 
		TABLE1 ANP,
		TABLE2 AN
		WHERE 
	   ANP.NODE_ID=AN.ID AND
		ANP.STRING_VALUE=v_isbn;


BEGIN

--Iterating all Products
FOR R1 IN C1 
LOOP

BEGIN


--To check whether Product is exists or not
SELECT AN.ID INTO V_NODE_ID 
FROM TABLE1 ANP
WHERE
ANP.STRING_VALUE in(R1.ISBN);


V_CATEGORY_COUNT :=0;

FOR R2 IN C2(R1.ISBN) 
LOOP

V_CATEGORY_COUNT := V_CATEGORY_COUNT+1;  
 
BEGIN

--Check if Document is found or not
SELECT ANP.STRING_VALUE  into V_FILENAME
		FROM 
		TABLE3
		WHERE 
       ANP.NODE_ID=R2.NODE_ID; 
   
   EXCEPTION WHEN
     NO_DATA_FOUND THEN
	  DBMS_OUTPUT.PUT_LINE('.Document is not found ');
UPDATE  GT_TABLE SET ERROR_MESSAGE='Document is not found' WHERE ISBN=R1.ISBN;
     END;

END LOOP; //end of C2 Loop

  

 IF(V_CATEGORY_COUNT  = 0) THEN
    DBMS_OUTPUT.PUT_LINE('.Category is not found ');
UPDATE  GT_TABLE SET ERROR_MESSAGE='.Category is not found' WHERE ISBN=R1.ISBN;
   END IF;  
 
 
EXCEPTION WHEN
NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE('PRODUCT NOT FOUND:::::::::::::'|| R1.ISBN);
UPDATE  GT_TABLE SET ERROR_MESSAGE='.PRODUCT NOT FOUND' WHERE ISBN=R1.ISBN;
END;
  
COMMIT;
END LOOP;--looping gt_isbns
END;

Open in new window

Geert G

your processing record per record
try thinking of processing the whole batch at once

writing pl/sql like this is also know as the slow by slow way

chaituu

ASKER

As you said it will take time to run for 16k records.Can you give an idea on how to write this in batch mode?I need to iterate each record and it has to execute 3-steps mentioned above .if any record fails in any step then it has to capture that error and store it in DB.

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geert G

16k records ?

from my perspective, processing that should be finished in a blink of an eye
or .1 sec

at least, that's what the speed i'm used to and go for.
fwiw, that's on a windows 4 core server, with 4Gb ram assigned to the database

chaituu

ASKER

Don't make loose statements.if you are a DBA in real just make that query faster as I am also using same hardware configuration.details were given in this ticket.

https://www.experts-exchange.com/questions/29084928/how-to-run-the-stored-procedure-in-batch-mode-or-in-run-it-in-parallel-processing.html?anchor=a42476130¬ificationFollowed=204236441