plsql/cursors handle exception and return back to the execution flow
Solution 1
Put the code that you want to execute within the loop in it's own block and then you can use that blocks exception section to handle any problems during the loop iteration.
Once the exception for that iteration is handled, the next loop iteration will start
e.g.:
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 have declared a local variable of type exception. I am looping through numbers 1 to 10, during the second loop iteration, the if statement is true and processing passes to the exception handler. Once the exception is handled, 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;
Solution 2
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;
codeObserver
Updated on December 30, 2021Comments
-
codeObserver over 2 years
I am trying to execute a cursor and want it to complete the loop even if there is some exception.
What I trying to do is "catch" all the exception and probably log something or do nothing and then return back to the flow . Here is how 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 doesn't compile.
There is probably some syntax error with the exception, but I am also not aware of semantics very well. Like I am not sure if you can return back to execution flow after handling an exception.
p.s: The DB is 10g and there is not CONTINUE in it. Hence using GOTO.
-
codeObserver over 12 yearsThnx Ian. I tried that and updated in the question. Still have issues.Btw is there a need for "begin" and "end" in the LOOP ?
-
Ian Carpenter over 12 yearsYes, in order to use the exception section the begin .. end sections are required
-
codeObserver over 12 yearsthnx. I added begin and end , there are still more errors in the structure. Also note that there is also an else statement which initially I thought is irrelevant, but I dont know :). I updated in the questions. Thanks again !
-
Luc M over 12 years
<<do_something>>
has to be executed only whencondition
is false. Not always... -
codeObserver over 12 years@LucM . was that a question? Yes ..I want everything in else [including the exception block] to be executed only when the <<condition>> is false.
-
Luc M over 12 years@codeObserver It was what I meant. Ian Carpenter has edited his answer. With his old answer,
<<do_something>>
was always executed. -
Shannon Severance over 12 yearsAn
exception
block can not sit inside anything except abegin
block. Your question now has it inside theelse
part of anif
statement. If you want the exception there, you will need abegin
after theelse
thenexception
at the point you are done with everthing in theelse
and finally anend;
to close thebegin
before theend if
.