PLSQL / cursors handle the exception and return to the thread of execution

I am trying to execute a cursor and want it to complete a loop, even if there is some kind of exception.

What I'm trying to do is to "catch" all the exceptions and probably log somthing or do nothing, and then return to the stream. This is what the code looks like:

FOR line IN my_cursor LOOP begin if<condition> then GOTO pass; else <<do_something>> exception when others then sys.dbms_output.put_line('say something'); end if; <<pass>> null; end END LOOP; 

the script does not compile. There is probably a syntax error with the exception, but I am also not very good at semantics. For example, I'm not sure if you can return to the thread of execution after handling the exception.

ps: DB is 10g and it has no CONTINUE. Hence using GOTO.

+7
source share
3 answers

Put the code you want to execute in the loop in its own block, and then you can use this block exception block to handle any problems during the loop iteration.

As soon as the exception for this iteration is processed, the next iteration of the loop will start

eg:.

 for line in my_cursor loop begin <<do_something>> exception <<do_exception_processing>> end; end loop; 

To illustrate this further, in the example below, I declared a local type exception variable. I iterate over numbers from 1 to 10, during the second iteration of the loop, the if statement is true, and the processing goes to the exception handler. When an exception is thrown, the next iteration of the loop begins.

 begin for i in 1 .. 10 loop declare my_exception exception; begin if i = 2 then -- if you need to do some processing then you would enter it -- here and then when you want to enter the exception section -- you would add the line below raise my_exception; end if; exception when my_exception then dbms_output.put_line('in exception section'); end; end loop; end; 
+16
source
 FOR line IN my_cursor LOOP if not some_condition then begin do_something; exception when others then log_my_error(); -- this should be something that uses -- an autonomous transaction end; end if; END LOOP; 
+7
source
  BEGIN FOR Line in My_Cursor LOOP IF condition THEN BEGIN do something... END; ELSE BEGIN do something... END; END IF; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('say something'); END; 
0
source

All Articles