Re: PL/SQL and rollback errors

"Vincento Harris" <wumutek_at_yahoo.com> wrote in message news:2fa13ee7.0308021447.14012a4_at_posting.google.com...
> HP Unix Oracle 8.0 Enterprise Edition
>
> I am working with 30 million rows and each time I run into ORA-1555
>
> Oracle commits and set transaction use rollback segments have been
> inserted into within the cursor to no avail
> but ORA-1555 still shows up
>
> I created two rollback segments minextents 20 initial next 100M
> and the problem still persists.
>
> Apart from trimming the size of the files what could be done on the
> SQL below to avoid ORA-1555?Thanks in advance for your ideas
>
>
>
>
>
>
>
>
> DECLARE
> CURSOR speculate IS
> SELECT numbers, keynumber, details, rowid
> FROM user1.details
> WHERE status is null;
> speculate_rec speculate%ROWTYPE;
> BEGIN
> OPEN speculate;
> LOOP
> FETCH speculate INTO speculate_rec;
> EXIT WHEN speculate%NOTFOUND;
> UPDATE user2.details
> SET details = speculate_rec.details
> WHERE (numbers = speculate_rec.numbers
> AND keynumber = speculate_rec.keynumber);
> IF SQL%NOTFOUND THEN
> INSERT INTO user2.details
> (numbers, keynumber, details)
> VALUES
> (speculate_rec.numbers, speculate_rec.keynumber,
> speculate_rec.details);
> UPDATE user1.details
> SET status = 'INSERT'
> WHERE rowid = speculate_rec.rowid;
> ELSE
> UPDATE user1.details SET status = 'UPDATE'
> WHERE rowid = speculate_rec.rowid;
> END IF;
> COMMIT;
> END LOOP;
> CLOSE speculate;
> END;
> /

because you are fetching across commit . Should never do a commit in an open cursor unless you are using autonomous transactions so get rid of the commit or the 155 will never go Received on Sat Aug 02 2003 - 18:05:55 CDT