Re: Pro*C Delete help

In article <3d5s1d$8ks_at_newsbf02.news-fddi.aol.com>, lmayhew_at_aol.com (LMayhew) writes:
|>
|> >I use a cursor to read the rows of a table. After I read a row
|> >I would like to delete it.
|> >If that is not possible I would like to delete all the rows in
|> >the table after I have read them all.
 

|> >Can anyone tell me how to do this?
|>
|> If you add to your select.... the ROWID variable and the FOR UPDATE OF
|> clause like ...
|>
|> select name, addr, ... ROWID
|> from employee
|> for update of name;
|>
|> Then you can delete the row by using the ROWID variable ...
|>
|> delete from EMPLOYEE where rowid = :srowid;
|>
|> Be sure and commit a few records as you go or you will run out of
|> locks or enqueues... this is because the SELECT FOR UPDATE
|> clause uses row locking...
|>
|>
|> Lee Mayhew

You can also use delete WHERE CURRENT OF the cursor. Also, DO NOT COMMIT inside the fetch loop. The select for update does lock the rows. But the commit releases the locks, invalidating the cursor, so the next fetch will then return ora-1002: fetch out of sequence. Received on Tue Dec 20 1994 - 18:24:19 CET