Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Nov 2010
    Posts
    30

    Unanswered: PL/SQL 'FOR' Loop Exception handling

    Hello there,

    I'm trying to put an exception handling code in the following loop but I really don't know how to.
    Code:
    BEGIN
    	FOR i IN(SELECT * FROM emp WHERE ename LIKE UPPER('&input%'))	LOOP
    
    		IF i.commission IS NULL OR i.commission = 0 THEN
    			DBMS_OUTPUT.PUT_LINE(i.ename||' does not earn commission.'); 		
    		ELSE
    			DBMS_OUTPUT.PUT_LINE(i.ename||' earns commission.');
    		END IF;
    	END LOOP;
    END;
    /

    I've been fiddling around with it but can't seem to get it working

    Code:
    BEGIN
    	FOR i IN(SELECT * FROM emp WHERE ename LIKE UPPER('&input%'))	LOOP
    
    		IF i.commission IS NULL OR i.commission = 0 THEN
    			DBMS_OUTPUT.PUT_LINE(i.ename||' does not earn commission.');
    EXCEPTION
    		ELSIF WHEN NO_DATA_FOUND THEN
    			DBMS_OUTPUT.PUT_LINE('No data round. Please try again'); 		
    		ELSE
    			DBMS_OUTPUT.PUT_LINE(i.ename||' earns commission.');
    		END IF;
    	END LOOP;
    END;
    /
    Can anybody please help me out?

    Thank you very much.

    Regards,
    db_newbie

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    when all else fails, Read The Fine Manual

    Overview of PL/SQL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by anacedent View Post
    when all else fails, Read The Fine Manual

    Overview of PL/SQL
    That's a pretty info-rich manuel, I have to say.

    I've read it briefly and found out some good info on PL/SQL Functions, but I still couldn't find the EXCEPTION HANDLING within the FOR loop.

    So, anybody else has a clue???

    Thank you very much.

    Regards,
    db_newbie

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but I still couldn't find the EXCEPTION HANDLING within the FOR loop.
    EXCEPTION is done on basis of BEGIN END blocks.

    You can't include EXCEPTION in the middle of other statements.

    Ask Tom Home contains many fine coding examples
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by anacedent View Post
    >but I still couldn't find the EXCEPTION HANDLING within the FOR loop.
    EXCEPTION is done on basis of BEGIN END blocks.

    You can't include EXCEPTION in the middle of other statements.

    Ask Tom Home contains many fine coding examples
    Says he's busy that I'll have to ask later
    I guess its understandable especially when he's dealing with thousands of questions.

    You're right, I can't put do EXCEPTION HANDLING when it's looping, which is why I posted here in the first place.
    I don't know how to handle errors with FOR loop.

    I have also searched quite a lot on Google but nothing came up.

    It would great if somebody could explain this to me....

    Anyway, thanks for your effort

    Regards,
    db_newbie

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like this:
    Code:
    for cur_r in (select * from emp) loop
      begin
        do something here
    
      exception
        when no_data_found then
          do something when data is not found
        when too_many_rows then
          do something else
      end;
    end loop;

  7. #7
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by Littlefoot View Post
    Something like this:
    It didn't work. Seems like it just couldn't accept the word EXCEPTION, and it has to start with the keyword BEGIN.

    Another thing I recognised is,...it didn't work because the IF and LOOP are already ended.

    But it wouldn't work if I end them after EXCEPTION either.

    So, that got me nowhere

    Thanks for your suggestion, though.
    (I remember your contribution from my last thread as well

    Regards,
    db_newbie

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I believe that you should share code you wrote. It is difficult to debug something you can't see (because, the principle I suggested DOES work if properly used).

  9. #9
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by Littlefoot View Post
    I believe that you should share code you wrote. It is difficult to debug something you can't see (because, the principle I suggested DOES work if properly used).
    You're right. Sorry about that. This is what I did

    Code:
    FOR i IN(SELECT * FROM emp WHERE ename LIKE UPPER('&input%'))	LOOP
    
    BEGIN
    
    	IF i.commission IS NULL OR i.commission = 0 THEN
    		DBMS_OUTPUT.PUT_LINE(i.ename||' does not earn commission.');		
    	ELSE
    		DBMS_OUTPUT.PUT_LINE(i.ename||' earns commission.');
    	END IF;
    
    EXCEPTION
    
    	WHEN NO_DATA_FOUND THEN
    		DBMS_OUTPUT.PUT_LINE("No matching result. Please try again.");
    	END LOOP;
    
    END;
    /
    This is what I get for error msg
    Code:
    SP2-0851: Command beginning "FOR i IN(S..." is not available in iSQL*Plus
    
    		DBMS_OUTPUT.PUT_LINE("No matching result. Please try again.");
    		                     *
    ERROR at line 12:
    ORA-06550: line 12, column 24:
    PLS-00114: identifier 'No matching result. Please try' too long
    ORA-06550: line 15, column 1:
    PLS-00103: Encountered the symbol "END"
    Just in case you're wondering why I'm using iSQL*Plus,...I have to because my Uni only provides this.

    Thank you.

    Regards,
    db_newbie

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It is a SINGLE QUOTE you should use.

    P.S. Of course, FOR can't stand alone - it is part of PL/SQL, so you have to enclose it into BEGIN-END so that it is a valid anonymous PL/SQL block.

  11. #11
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by Littlefoot View Post
    It is a SINGLE QUOTE you should use.

    P.S. Of course, FOR can't stand alone - it is part of PL/SQL, so you have to enclose it into BEGIN-END so that it is a valid anonymous PL/SQL block.
    DOUBLT QUOTES was a dumb mistake

    This is how I tried, originally. It works for the LOOP but it doesn't for the EXCEPTION.
    When I entered incorrect data, the message I want it to generate, didn't come up.
    Code:
    BEGIN
    	FOR i IN(SELECT * FROM emp WHERE ename LIKE UPPER('&input%'))	LOOP
    
    		IF i.commission IS NULL OR i.commission = 0 THEN
    			DBMS_OUTPUT.PUT_LINE(i.ename||' does not earn commission.');		
    		ELSE
    			DBMS_OUTPUT.PUT_LINE(i.ename||' earns commission.');
    		END IF;
    	END LOOP;
    
    EXCEPTION
    
    	WHEN NO_DATA_FOUND THEN
    		DBMS_OUTPUT.PUT_LINE('No matching result. Please try again.');
    END;
    /

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Such a code will never produce NO-DATA-FOUND. Cursor loop won't execute at all (if no records satisfy the WHERE condition), or one (or many) times.

    Catching NO-DATA-FOUND here is useless.

  13. #13
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by Littlefoot View Post
    Such a code will never produce NO-DATA-FOUND. Cursor loop won't execute at all (if no records satisfy the WHERE condition), or one (or many) times.

    Catching NO-DATA-FOUND here is useless.
    Well, you suggested WHEN DO_DATA_FOUND code in one of your previous posts. That's why I used it.

    But to be fair, I don't know any other way either.

    And I'm presuming, you't dont too??

    I guess I've hit the rock bottom, again...

  14. #14
    Join Date
    Feb 2005
    Posts
    57
    In this scenario I would initialise a count before the loop, increment it within the loop and test for zero after the loop.

    hth

  15. #15
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by outrider View Post
    In this scenario I would initialise a count before the loop, increment it within the loop and test for zero after the loop.

    hth
    Thank you for the reply and forgive me for my lack of knowledge, but if I initiate a COUNT before LOOP, how will I be able to continue with what I'm carrying out now?
    That is, finding out if an employee earns commission and displaying appropriate messages.

    May I please ask you to be a bit more specific?

    Many thanks and regards,
    db_newbie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •