Such a simple question, but nothing I am reading makes me feel confident I
have this right.

A single plsql procedure needs to select from one table, and then insert
and update other tables. The inserts and updates have to be in sync with
the selected table, i.e. the table being read mustn't change in ways that
would alter what my routine decides to put into the other tables.

In theory, another process might update all the tables as a single
transaciton, and what I don't want to be able to happen is that table 1 is
updated after I read it, and before I get to the point of updating or
inserting the other tables.

What IS the correct way to prevent this? Will savepoint do this, do I even
need to do anything? The oracle manual has lots of examples of multiple
updates , or read-only consistancy, but I don't see this.



(not correct plsql, just outline)

procedure maybe_update()

cursur c1 select the_number from table1;
cursor c2 select * from table 2 for update of some_thing;

begin

open c1
loop until certain record found
end loop

open c2
loop
if interesting record is found
then update it; end if;
end loop

insert into table3 values based on table 1 and table 2;

end

Feedback welcome, thanks.

yf110 (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones) wrote:

Oracle will handle this by default..Once the cursor is opened the data is 'frozen' in time..The data read will not be
affected by any changes that take place while the cursor is open ( Read consistency is an Oracle guarantee ).
NOTE: The cursor must remain open to be sure of this..closing and re-opening may 'void' that guarantee..

The target tables would only be 'frozen' if you use the 'select for update' type cursor as you have done.

On 14 Dec 2004 10:46:51 -0800, yf110 (AT) vtn1 (DOT) victoria.tc.ca (Malcolm
Dew-Jones) wrote:


alter session set isolation_level = serializable
(default : read_comitted)

Will savepoint do this, do I even
Even as outline horrible code. Never do anything procedurally, if it
can be accomplished by SQL.


--
Sybrand Bakker, Senior Oracle DBA