HI, declare cursor c1 is select sid,scity from supplier; wrong_city exception; begin for s_rec in c1 loop if s_rec.scity = 'bangalore' then raise wrong_city; end if; end loop; exception when wrong_city then dbms_output.put_line('wrong city'); end; result: SQL> / wrong city PL/SQL procedure successfully completed.
Here in my table 'SUPPLIER' i've more than 1 record which has the value 'BANGALORE'. But this code ends as sson as it got one record with the value 'BANGALORE'. I want continue the loop until all the records are checking. How can i go back to that FOR ..LOOP after fetching and displaying the exception?
When it equals Bangalore don't raise an expection. This will automatically send you to the Exception section. Now if you must raise this then just put a BEGIN/EXCEPTION/END around that part of the code. This way it will catch the exception, then continue after the END, which will be your remaining code, and stays inside the LOOP. It looks like all you want to do is send the DBMS output. Why not just put that one line in the THEN of the IF statement. putting the output in the If statement This is the one I prefer.
When you post a reply in any thread here at JavaRanch there are buttons under the Add Reply button. Buttons like URL, EMAIL, BOLD, ITALICS, QUOTE, CODE, LIST-START, etc... These buttons are to the right of the Smiley's. When you want to post code in this forum. Click the CODE button and it will put tags in the Reply section that you can paste your code in between and they will look nice. Just like my reply above with the code. This tag is only for the forums here, it has nothing to do with Oracle or Stored Procedures. Mark