Re: Help: PL/SQL insert and delete

xxx wrote:
>
> We need to develop a PL/SQL of aging an table.
> What I do is
>
> cursor x is select * from table A where polltime < sysdate-1;
> Begin
> FOR y in x LOOP
> insert into table B values (col1 y.col1,col2 y.col2);
> delete from table A where col1=y.col1;
> END LOOP;
> END;
>
> The problem is, sometimes, insert did not finish, while delete still can go
> on, cause loss some data, how can we improve the PL/SQL?
> Thank you for your help.

where are your commits? Do you have any exception handlers in your code? Are you sure you saw data deleted when the insert did not? and the real code has no other code (like IF THEN statements)?

try putting in a COMMIT just after the insert.

An alternative is two loops. first pass does all the inserts. second pass deletes what was copied. Received on Fri May 25 2001 - 23:56:49 CDT