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;
chaituu chaituAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
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;
0
chaituu chaituAuthor Commented:
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

0
Geert GOracle dbaCommented:
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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

chaituu chaituAuthor Commented:
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,
0
flow01Commented:
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.
0
chaituu chaituAuthor Commented:
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

0
Geert GOracle dbaCommented:
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
0
chaituu chaituAuthor Commented:
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.
0
Mark GeerlingsDatabase AdministratorCommented:
Another option to consider: use explicit cursors instead of your implicit (select ... into...) cursors.  With explicit cursors you avoid the "NO_DATA_FOUND" and the "TOO_MANY_ROWS" exceptions.  You do have to explicitly set the variable(s) to null before doing the fetch and check the variable(s) after doing the fetch to see if the cursor found a value or not, but this approach may make it easier to do your update, since the update then does not need to be in an exception block.

Explicit cursors (with separate: declare, open, fetch and close commands) are not quite as efficient as your implicit cursors, but because they avoid the two most-common exceptions that cursors can cause, they can make coding easier.  And, the performance difference is very small.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
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
0
chaituu chaituAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.