Re: a row-level operator for copying?

On Jul 25, 9:49 am, "Chris L." <diver...@xxxxxxxxxx> wrote:
On Jul 25, 2:26 am, Mark Harrison <m...@xxxxxxxxx> wrote:





I've got two identical tables -- one has current data, and one
has historical data. I've got a nightly job which moves older
data from the current table to the history table. In the
code sample below, these tables are atest and atest_history).

Three questions:

1. The code works, but I don't like having to list out all of
the columns in the insert statement -- is there some way
to insert the data in one go, something like:
insert into atest_history values atest_rec;

2. Are there any other features or extensions that make
this easier? It would be great if there were some
kind of statement like:
move rows from table1 to table2 where ...;

3. I'm a newbie at pl/sql, feel free to suggest where
the code below is lousy!

Many TIA!
Mark

--------------------------------------------------------

-- archive old rows from table "atest" to "atest_history"
create or replace procedure ARCHIVE_ATEST
as
begin
declare
atest_rec atest%rowtype;

cursor getfinished
is
select a, seq, c
from atest
where c = 1;
begin
open getfinished;

loop
fetch getfinished
into atest_rec;

exit when getfinished%notfound;
dbms_output.PUT_LINE('v_atest = ' || atest_rec.a);

insert into atest_history
(a, seq, c)
values (atest_rec.a, atest_rec.seq, atest_rec.c);

delete from atest
where seq = atest_rec.seq;
end loop;

dbms_output.PUT_LINE('done,rowcount=' || getfinished%rowcount);

close getfinished;
end;
end ARCHIVE_ATEST;

If you can do without the first dbms_output.PUT_LINE then you'd have
to run these two commands:

insert into atest_history
select * from atest
where c = 1;

delete from atest
where c = 1;

You can have the total number of rows inserted/deleted by using
implicit cursor variables.

If you gotta have the first PUT_LINE you can use the loop for that,
but you should move the actual insert/delete after the loop. ie. loop
to PUT_LINE, insert all, delete all. Otherwise the loop is
unnecessary.

Regards
C.- Hide quoted text -

- Show quoted text -

I must add that I assumed that you're purging the table in a time
window where there's no activity on table atest, otherwise someone
could be modifying the table (adding records with c=1 or updating them
to c=1) and the delete statement could theoretically wipe records that
weren't there when the insert statement ran.

I don't know what the "correct" way would be to circumvent this
problem (I'd somehow mark the rows to be moved/deleted and then move/
delete based on that mark but that'd require an extra column)

Regards

.

Relevant Pages

  • Re: a row-level operator for copying?
    ... code sample below, these tables are atest and atest_history). ... but you should move the actual insert/delete after the loop. ... and BULK INSERT them into the history table and BULK ...
    (comp.databases.oracle.misc)
  • Re: a row-level operator for copying?
    ... has historical data. ... these tables are atest and atest_history). ... select a, seq, c ... end loop; ...
    (comp.databases.oracle.misc)
  • a row-level operator for copying?
    ... has historical data. ... these tables are atest and atest_history). ... select a, seq, c ... end loop; ...
    (comp.databases.oracle.misc)
  • RE: Revision Object
    ... The code sample runs fine on my samples (but that’s not saying much since ... either entering a endless loop as it points back to itself, ... If oRev.Type = wdRevisionDelete Or oRev.Type = ... ElseIf Not = UCase) Then ...
    (microsoft.public.word.vba.general)
  • Re: whats use of ":" in java in the following code ?
    ... here is the code sample which is using: ... for (classname obj:somemethod()) // here is the problem in ... That's the syntax for the 'foreach' style for loop. ... somemethodreturns a Collection or an array. ...
    (comp.lang.java.programmer)