Re: Help: PL/SQL insert and delete

A copy of this was sent to "xxx" <dbadba62_at_hotmail.com> (if that email address didn't require changing) On Fri, 25 May 2001 01:53:07 GMT, you 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.
>

that is impossible. If the insert fails, given the code above, it will raise an exception. Since the exception is not handled, the delete that follows will NEVER happen either. In fact, the loop will exit -- the routine will exit.

Something else is at work -- your plsql routine must be more complex then the above.

I assure you -- what you say, given the above code, is 100% impossible. You have a logic error somewhere in your code. Oracle is very transactional, it is something we pride ourselves on.

I can say that the above routine would be infinitely more efficient as:

...

   l_sysdate date default sysdate;
begin
  insert into b
  select * from a where polltime < l_sysdate-1;

  delete from b
   where polltime < l_sysdate-1;
end;

Don't use a cursor loop where simple, straight SQL will do. It'll almost certainly be slower.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp