Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Problem insert data into new table at the same time delete data from existing table (OS: Windows XP SP3, Database: Oracle 8.1.7)
icon8.gif  Problem insert data into new table at the same time delete data from existing table [message #418012] Wed, 12 August 2009 08:37 Go to next message
oracle_user1
Messages: 45
Registered: May 2008
Member
Hi to all,

I encounter a problem, the scenario as below:

i want to move some records within a date range from table
sm_trx into table trxarchive and at the same time delete
record from table sm_trx.

Before exec my procedure:
sm_trx = 2000 records
trxarchive = no record

Let say my total records within date range (01/01/2000 to
31/12/2001) is 750.
I want to move this 750 records into my trxarchive table
and at the same time delete the 750 record from sm_trx table.
After i exec procedure the result should show me
sm_trx = 1250 record (2000 - 750)
trarchive = 750

Finally what i got is, my sm_trx succefful delete 750 records
and remain 1250 records but my trxachive have more then 750
records.

Here is my coding, i need you all help me which part of logic
i miss.

Thanks

create or replace procedure archivetablevp (frm_date varchar2,tto_date varchar2,p_frtyp varchar2) is
cursor c_vp is
select * from sm_trx
where trunc(credate) between to_date(frm_date,'dd/mm/yyyy')
and to_date(tto_date,'dd/mm/yyyy')
and vp_frtyp =p_frtyp;
v_cnt number := 0;
begin
for vp in c_vp loop
v_cnt := v_cnt +1;
begin
insert into trxarchive select * from sm_trx
where vp_frtyp = vp.vp_frtyp;
exception
when others then
dbms_output.put_line('1 error'||sqlerrm);
end;
if mod(v_cnt,50) = 0 then
delete from sm_trx
where vp_conno = vp.vp_conno;
commit;
end if;
end loop;
exception
when others then
dbms_output.put_line('error'||sqlerrm);
end;

Regards,

oracle user
Re: Problem insert data into new table at the same time delete data from existing table [message #418017 is a reply to message #418012] Wed, 12 August 2009 09:32 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
What I can see is that from your cursor you loop trough all entries where vp_frtyp equals p_frtyp and credate is within the date range.
cursor c_vp is
    select * from sm_trx
    where trunc(credate) between to_date(frm_date,'dd/mm/yyyy')
                         and to_date(tto_date,'dd/mm/yyyy')
      and vp_frtyp =p_frtyp;


But in your insert within the loop you insert all entries from sm_trx where vp_frtyp is equal to vp.vp_frtyp. So for each iteration you're inserting all entries from sm_trx with that given vp_frtyp over and over again.
insert into trxarchive 
    select * from sm_trx
    where vp_frtyp = vp.vp_frtyp;


It would be easier to rather than looping trough these entries, insert and delete them using simple sql
insert into trxarchive 
    select * from sm_trx
    where trunc(credate) between to_date(frm_date,'dd/mm/yyyy')
                         and to_date(tto_date,'dd/mm/yyyy')
      and vp_frtyp =p_frtyp;

delete from sm_trx
    where trunc(credate) between to_date(frm_date,'dd/mm/yyyy')
                         and to_date(tto_date,'dd/mm/yyyy')
      and vp_frtyp =p_frtyp;

(This was the logic you wanted wasn't it?)
Re: Problem insert data into new table at the same time delete data from existing table [message #418478 is a reply to message #418017] Sun, 16 August 2009 03:57 Go to previous messageGo to next message
oracle_user1
Messages: 45
Registered: May 2008
Member
Dear c_stenersen,

Thank you for your solution. It's work.
But i want to put the logic inside 1 procedure instead of run separetely the sql (1. insert 2. delete)

I have my solution now.
Here i want to sharing with you all there:

create table sm_trx
(vp_conno varchar2(3) not null,
vp_docno varchar2(4),
credate date),
vp_frtyp varchar2(1))

create table trxarchive
(vp_conno varchar2(3) not null,
vp_docno varchar2(4),
credate date),
vp_frtyp varchar2(1))


select * from sm_trx
VP_ VP_D CREDATE V
--- ---- --------- -
01 A123 01-JAN-00 F
02 B123 02-JAN-00 F
03 C123 03-JAN-00 F
04 D123 04-JAN-00 F
05 E123 05-JAN-00 F
06 F123 06-FEB-00 M
07 G123 07-FEB-00 M
08 H123 01-DEC-01 M
09 I123 18-DEC-01 M
10 J123 20-DEC-01 M

select * from trxarchive;
no rows selected

my procedure
create or replace procedure sm_trx (frm_date varchar2,tto_date varchar2,p_frtyp varchar2) is
cursor c_vp is
select * from sm_trx
where trunc(credate) between to_date(frm_date,'dd/mm/yyyy')
and to_date(tto_date,'dd/mm/yyyy')
and vp_frtyp =p_frtyp;
v_cnt number := 0;
begin
for vp in c_vp loop
v_cnt := v_cnt +1;
begin
insert into trxarchive select * from sm_trx
where vp_conno =vp.vp_conno;
exception
when others then
dbms_output.put_line('1 error'||sqlerrm);
end;
if mod(v_cnt,2) = 0 then
commit;
end if;

delete from p
where vp_conno = vp.vp_conno;
commit;
end loop;
exception
when others then
dbms_output.put_line('error'||sqlerrm);
end;

exec sm_trx('01/01/2000','04/01/2000','F');

result :
select * from trxarchive
VP_ VP_D CREDATE V
--- ---- --------- -
01 A123 01-JAN-00 F
02 B123 02-JAN-00 F
03 C123 03-JAN-00 F
04 D123 04-JAN-00 F


select * from sm_trx
VP_ VP_D CREDATE V
--- ---- --------- -
05 E123 05-JAN-00 F
06 F123 06-FEB-00 M
07 G123 07-FEB-00 M
08 H123 01-DEC-01 M
09 I123 18-DEC-01 M
10 J123 20-DEC-01 M
1 K123 01-FEB-01 M

Thanks.

Regards,
oracle user

Re: Problem insert data into new table at the same time delete data from existing table [message #418480 is a reply to message #418478] Sun, 16 August 2009 05:03 Go to previous message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
To get it inside a procedure, simply wrap the SQL statements inside a procedure declaration.
create or replace procedure sm_trx (frm_date varchar2,tto_date varchar2,p_frtyp varchar2) is
begin
  insert into trxarchive 
    select * from sm_trx
    where trunc(credate) between to_date(frm_date,'dd/mm/yyyy')
                         and to_date(tto_date,'dd/mm/yyyy')
      and vp_frtyp =p_frtyp;

  delete from sm_trx
    where trunc(credate) between to_date(frm_date,'dd/mm/yyyy')
                         and to_date(tto_date,'dd/mm/yyyy')
      and vp_frtyp =p_frtyp;
end sm_trx;


The gode you have given in here doesn't make sense:
if mod(v_cnt,2) = 0 then
  commit;
end if;

delete from p
where vp_conno = vp.vp_conno;
commit; 
You want to commit for every other iteration, but anyways after the delete you commit in every iteration of the loop. So both your insert and delete is commited in every iteration. Anyways you shouldn't have all these commits inside your loop.
http://www.orafaq.com/wiki/PL/SQL_FAQ#How_often_should_one_COMMIT_in_a_PL.2FSQL_loop.3F_.2F_What_is_the_best_commit_strategy.3F
Previous Topic: Oracle Apex installation error
Next Topic: apex in middle
Goto Forum:
  


Current Time: Sat Dec 10 18:27:06 CST 2016

Total time taken to generate the page: 0.09018 seconds