Discussions
Categories
- 385.5K All Categories
- 4.9K Data
- 2.5K Big Data Appliance
- 2.4K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
FOR Loop Exception Handling

910971
Member Posts: 40
Given the following code:
for x in my_cursor loop begin <<stmt_1>> <<stmt_2>> exception when others then <<log_error>> end; end loop;Say there are 5 x's in my_cursor. On element x3, stmt_1 works fine but stmt_2 throws an exception. I'd like to rollback ONLY the work done on x3 (in this case, stmt_1), then continue the loop from the next element, x4. Can this be achieved with some combination of savepoint/rollback?
Best Answer
-
In this particular case, I would tend to suggest that you follow the third example I posted where you factor out the core of your loop into a procedure such that you only overwrite the values of a and b if your insert succeeds, i.e.
CREATE OR REPLACE PROCEDURE proc_name( p_x IN NUMBER, p_a IN OUT NUMBER, p_b IN OUT NUMBER ) AS l_tmp_a NUMBER; l_tmp_b NUMBER; BEGIN SAVEPOINT s1; l_tmp_a := p_a + p_x; l_tmp_b := 1/(6-l_tmp_a); INSERT INTO test_tbl VALUES( l_tmp_a, l_tmp_b ); p_a := l_tmp_a; p_b := l_tmp_b; EXCEPTION WHEN <<some exception>> THEN ROLLBACK TO SAVEPOINT s1; END;
You could, of course, have the same TMP local variables in your original code. Or you could replicate the computations, i.e.for x in 1..5 loop begin savepoint s; insert into test_tbl values(a+x,1/(6-a)); a := a+x; b := 1/(6-a); exception when others then rollback to savepoint s; end;
Justin
Answers
-
Sure
SQL> create table foo( col1 number ); Table created. SQL> ed Wrote file afiedt.buf 1 declare 2 eww_a_three exception; 3 begin 4 for i in 1..5 5 loop 6 begin 7 savepoint s1; 8 insert into foo values( i ); 9 if( i = 3 ) 10 then 11 raise eww_a_three; 12 end if; 13 exception 14 when eww_a_three then 15 rollback to savepoint s1; 16 end; 17 end loop; 18* end; SQL> / PL/SQL procedure successfully completed. SQL> select * from foo; COL1 ---------- 1 2 4 5
Of course, if your code can be only partially successful, you'd need some way to alert the caller that it was only partially successful. That would normally involve raising an exception since you generally shouldn't depend on the caller to read the error log that you wrote to in order to discover that there was an error. And if the caller can't handle that exception, the entire transaction generally ought to be rolled back.
Justin -
I hope this simple test would help you. It would error out when x=3 ;
create table test_temp(x number,y number); create table error_log (error_msg varchar2(2000)); truncate table test_temp; truncate table error_log; declare y number; begin for x in 1 ..5 loop begin insert into test_temp(x) values(x); y:=3/(x-3); insert into test_temp(x,y) values(x,y); commit; exception when others then rollback; insert into error_log values('failed for x=3; divide by zero'); commit; end; end loop; end; / select * from test_temp; select * from error_log;
-
Thank you for the reply, but it prompted an additional question. Is there any way to rollback to the previously stored value of a PL/SQL variable? For instance:
create table test_tbl(x number); declare cnt number; ex exception; begin cnt := 0; for x in 1..5 loop begin savepoint s; cnt := cnt+1; insert into test_tbl values(x); if x = 3 then raise ex; end if; exception when others then rollback to savepoint s; end; end loop; dbms_output.put_line('cnt='||cnt); end; select * from test_tbl order by x;
In this case, I'd like cnt to return to its previously stored value prior to the savepoint (cnt=2) and then continue incrementing from there, so the final value would be cnt=4. -
No. The values of PL/SQL variables aren't part of the transaction so they're not going to be rolled back.
You could, of course, have a separate variable that you use to capture the value of CNT just before (or just after) declaring the savepoint and then restore that value when you do your rollback to savepoint. In this case, you could also move thecnt := cnt + 1;
call to be after the IF statement so that it is only incremented if the insert completes successfully.
Justin -
In this particular case that would work, but the example is rather contrived, as it's designed to fail specifically on the third iteration. In my actual code, I wouldn't be using an IF statement to deliberately raise an exception, but I'd want to catch any exceptions that might come up, like:
declare cnt number; ex exception; begin cnt := 0; for x in 1..5 loop begin savepoint s; cnt := cnt+1; insert into test_tbl values(1/(3-x)); exception when others then rollback to savepoint s; end; end loop; dbms_output.put_line('cnt='||cnt); end;
So in summation, you're saying that savepoint/rollback/commit have no effect on PL/SQL variables (both local and global?) and I'll need to reset the value of cnt manually:declare cnt number; ex exception; begin cnt := 0; for x in 1..5 loop begin savepoint s; cnt := cnt+1; insert into test_tbl values(1/(3-x)); exception when others then cnt := cnt-1; rollback to savepoint s; end; end loop; dbms_output.put_line('cnt='||cnt); end;
-
As I said, you can put the increment operation at the end of the block when you know no more exceptions could be raised, i.e.
declare cnt number; ex exception; begin cnt := 0; for x in 1..5 loop begin savepoint s; insert into test_tbl values(1/(3-x)); cnt := cnt+1; exception when others then rollback to savepoint s; end; end loop; dbms_output.put_line('cnt='||cnt); end;
You could also increment the counter outside of the PL/SQL block, i.e.declare cnt number; ex exception; num_success number; begin cnt := 0; for x in 1..5 loop begin num_success := 0; savepoint s; insert into test_tbl values(1/(3-x)); num_success := sql%rowcount; exception when others then rollback to savepoint s; end; cnt := cnt + num_success; end loop; dbms_output.put_line('cnt='||cnt); end;
And you can refactor your code so that the components to hide some of this complexity, i.e.SQL> ed Wrote file afiedt.buf 1 create or replace procedure try_insert_test_tbl( 2 p_val in number, 3 p_num_success out number ) 4 as 5 divide_by_zero exception; 6 pragma exception_init( divide_by_zero, -1476 ); 7 begin 8 savepoint s; 9 insert into test_tbl values( 1/(3-p_val ) ); 10 p_num_success := sql%rowcount; 11 exception 12 when divide_by_zero 13 then 14 p_num_success := 0; 15* end; SQL> / Procedure created. SQL> ed Wrote file afiedt.buf 1 declare 2 cnt number; 3 ex exception; 4 num_success number; 5 begin 6 cnt := 0; 7 for x in 1..5 loop 8 try_insert_test_tbl( x, num_success ); 9 cnt := cnt + num_success; 10 end loop; 11 dbms_output.put_line('cnt='||cnt); 12* end; SQL> / cnt=4 PL/SQL procedure successfully completed.
There is no way for DCL statements to affect the state of PL/SQL variables. But you can generally structure your code in such a way that this is unnecessary.
Justin -
Hi,crottyan wrote:Doesn't that approach work, regardless of where and how often the errors occur? Post a specific example where it doesn't do what you want.
In this particular case that would work, but the example is rather contrived, as it's designed to fail specifically on the third iteration. In my actual code, I wouldn't be using an IF statement to deliberately raise an exception, but I'd want to catch any exceptions that might come up ...So in summation, you're saying that savepoint/rollback/commit have no effect on PL/SQL variables (both local and global?)Exactly! ROLLBACK (and COMMIT) only affect pending DML operations.
Say it's 9:00 AM. The sun is shining, and you have a full cup of hot coffee. You're executing a procedure called foo. Variable x is set to 0, and there are 100 rows in the table. At this time, you issue a SAVEPOINT.
Now say it's 9:10. The sky has gotten cloudy, the coffee has cooled down and you've drunk half of it. Procedure foo has called procedure bar, and that's what is now running. Variable x is now set to 10, and you've INSERTed 10 rows, so there are 110 rows in the table (as you see it in your session. You haven't COMMITted the changes yet.)
At this point you issue a ROLLBACK statement. There will now be 100 rows in the table again, just like there were at 9:00. However, the clock will still say 9:10, the sky will still be cloudy, your coffee cup will still be half full, the coffee will still be cool. You will still be executing procedure bar, and x will still be set to 10. Only the pending DML changes (the 10 new rows that were INSERTed) will revert to the state as of 9:00.and I'll need to reset the value of cnt manually:If you only want to count the sucessful INSERTs, then only count the sucessful INSERTs; don't count all of them:declare cnt number; ex exception; begin cnt := 0; for x in 1..5 loop begin savepoint s; insert into test_tbl values(1/(3-x)); -- The following statement is executed only if the INSERT above worked: cnt := cnt+1; exception when others then rollback to savepoint s; end; end loop; dbms_output.put_line('cnt='||cnt); end;
-
Doesn't that approach work, regardless of where and how often the errors occur? Post a specific example where it doesn't do what you want.
create table test_tbl(a number,b number); declare a number; b number; begin a := 0; b := 0; for x in 1..5 loop begin savepoint s; a := a+x; b := 1/(6-a); insert into test_tbl values(a,b); exception when others then rollback to savepoint s; end; end loop; end; select * from test_tbl order by x; Results: a b -- -- 1 .2 3 .33 10 -.25 15 -.11
The values of the last two records are now incorrect. Iteration 3 failed after the value of "a" was already updated, so it retains this incorrect value. I said my previous example was contrived because it is; I realize that, in that particular case, cnt could be placed after the INSERT in order to avoid the problem of an exception, but that didn't address the larger question about resetting a PL/SQL variable to its pre-exception state.Say it's 9:00 AM...I believe your vignette answers the question, but in case there was a miscommunication, I'll rephrase: I'd like any processing (changes to both the database and local variables) that occurs inside the FOR loop to be atomic. I know that changes to the database can be undone with a ROLLBACK, but, from what I understand, I'll need to manually reset the values of PL/SQL variables to their pre-exception state? -
In this particular case, I would tend to suggest that you follow the third example I posted where you factor out the core of your loop into a procedure such that you only overwrite the values of a and b if your insert succeeds, i.e.
CREATE OR REPLACE PROCEDURE proc_name( p_x IN NUMBER, p_a IN OUT NUMBER, p_b IN OUT NUMBER ) AS l_tmp_a NUMBER; l_tmp_b NUMBER; BEGIN SAVEPOINT s1; l_tmp_a := p_a + p_x; l_tmp_b := 1/(6-l_tmp_a); INSERT INTO test_tbl VALUES( l_tmp_a, l_tmp_b ); p_a := l_tmp_a; p_b := l_tmp_b; EXCEPTION WHEN <<some exception>> THEN ROLLBACK TO SAVEPOINT s1; END;
You could, of course, have the same TMP local variables in your original code. Or you could replicate the computations, i.e.for x in 1..5 loop begin savepoint s; insert into test_tbl values(a+x,1/(6-a)); a := a+x; b := 1/(6-a); exception when others then rollback to savepoint s; end;
Justin
This discussion has been closed.