when do you use save points?
A reader, February 18, 2004 - 9:17 pm UTC
Tom
Can you list out some common scenarios where save points
are useful? (I have not had a scenario where I needed
to use it so far.) I know that they get implicitly used
by Oracle but when do you think we should use them?
thanx for a great site!:)
February 19, 2004 - 8:37 am UTC
begin
insert into log_table ( .... ) values
( .... 'starting process at ' || systimestamp );
savepoint foo;
begin
p1;
p2;
p3;
exception
when others then
rollback to foo;
insert into log_table ( .... ) values
( .... 'failed with ' || sqlerrm || ' at ' || systimestamp );
commit;
RAISE;
end;
insert into log_table ( .... ) values
( ...., 'finished successfully at ' || systimestamp );
commit;
end;
is sort of a "made up example" -- here, you have an insert that should "stick" (the first one). The three procedures should either all suceed or all fail. So, we savepoint before the procedures -- if any fail for any reason -- we undo their work -- log the reason and commit (and then re-raise the exception) -- else we just do the insert of success and done.
Personally, I cannot remember ever using savepoint "for real". The implicit ones Oracle does for us are generally more than sufficient.
thanx!
A reader, February 19, 2004 - 12:52 pm UTC
Even in your example. you could use autonomous
transactions, correct?
So in general, looks like savepoints are of not much use..
February 19, 2004 - 2:32 pm UTC
there was no "pragma autonomous_transaction" there.
i did not use them.
what i meant was
A reader, February 19, 2004 - 4:51 pm UTC
that you could use them thus avoiding savepoints.
February 19, 2004 - 7:23 pm UTC
it would not be the same.
with an atrans -- the first insert would be "committed". If the system crashed -- it would still be commited. You would have "an infinite" transaction (no "closing" record).
I'm not a fan of atrans very much.
A use for savepoints
eric givler, February 20, 2004 - 5:43 am UTC
We had a use for them.
Scenario: Our whitewater boating module allows either 30 or 60 people in a time slot. There are 8 slots each day. The original designers thought that each slot should be represented by a row.
So:
7:30am (60 people) = 60 rows * 200 day season = whoa!
8:30am (60 people) = 60 rows ...
Anyway, they were having myriads of problems overbooking these slots as there were blocking updates which were later overwritten. If a customer was changing their party size, it would "deallocate" rows by setting a used flag. This would leave gaps.
The new code to "grab" the slots went like this:
savepoint sp1;
for slot_rec in (select * from boat_availability where site_id=p_site and site_date=p_request_date order by slot_number ) loop
begin
attempt select for update on this row
i := i + 1;
slots_tab(i) := slot_rec.rowid;
exception
null;
end;
end loop;
if slots_grabbed < slots_needed then
-- couldn't get enough slots for this site
rollback to savepoint sp1;
else
for i in 1..slots_grabbed loop
update boat_availability
set slot_used='Y'
where rowid=slots_tab(i);
end loop;
end if;
remotely executed procedure
Ivan, April 14, 2004 - 1:43 pm UTC
Hi Tom,
I have a scenerio where a savepoint would be ideal but unfortunately it does not work because it is in a remotely executed procedure. Can you think of a work around as I really do not want to use the rollback for the complete transaction.
DECLARE
recursive_loop EXCEPTION;
PRAGMA EXCEPTION_INIT(recursive_loop,-1436);
BEGIN
-- SAVEPOINT would-be-nice-here;
UPDATE companies co
SET co.parent_id = i_parent_company_id,
co.updater = USER,
co.last_update_date = SYSDATE,
co.parent_id_upd = USER,
co.parent_id_lupd = SYSDATE
WHERE co.company_id = i_child_company_id;
-- test to see if the parent is successful
SELECT COUNT(ROWID)
INTO v_row_count
FROM companies co
CONNECT BY PRIOR co.company_id = co.parent_id
START WITH co.company_id = i_parent_company_id;
ROLLBACK; -- would like to remove somehow
EXCEPTION
WHEN recursive_loop THEN
mev_common.perform_rollback;
RAISE indirect_parent;
END;
Thanks as always,
Ivan
April 14, 2004 - 3:16 pm UTC
the remote procedure call is already protected by a save point? statements are atomic in Oracle and a stored procedure "is a statement".
for example, on "remote" i have installed:
scott@ORA920> create table t ( x int check ( x > 0 ) );
Table created.
scott@ORA920>
scott@ORA920> create or replace procedure p( p_x in number )
2 as
3 begin
4 insert into t values ( 10000 );
5 insert into t values ( p_x );
6 end;
7 /
Procedure created.
that first insert always "works" (never fails). the second might fail with bad inputs. I call it remotely:
scott@ORA9IR2> exec p@ora920(1);
PL/SQL procedure successfully completed.
scott@ORA9IR2> select * from t@ora920;
X
----------
10000
1
and every thing is fine. I call it again:
scott@ORA9IR2> exec p@ora920(-1);
BEGIN p@ora920(-1); END;
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C004839) violated
ORA-06512: at "SCOTT.P", line 5
ORA-06512: at line 1
scott@ORA9IR2> select * from t@ora920;
X
----------
10000
1
and it failed -- but it did not rollback my current transaction -- just the work IT performed.
It is the very nature of the stored procedure.
the same will be true in a stored procedure that calls lots of remote stored procedures:
scott@ORA9IR2> delete from t@ora920;
2 rows deleted.
scott@ORA9IR2> commit;
Commit complete.
scott@ORA9IR2> select * from t@ora920;
no rows selected
scott@ORA9IR2>
scott@ORA9IR2> begin
2 begin
3 p@ora920(1);
4 exception
5 when others then
6 dbms_output.put_line( 'p(1) failed ' || sqlerrm );
7 end;
8 begin
9 p@ora920(-1);
10 exception
11 when others then
12 dbms_output.put_line( 'p(-1) failed ' || sqlerrm );
13 end;
14 end;
15 /
p(-1) failed ORA-02290: check constraint (SCOTT.SYS_C004839)
violated
ORA-06512: at "SCOTT.P", line 5
PL/SQL procedure successfully completed.
scott@ORA9IR2> select * from t@ora920;
X
----------
10000
1