ps/sql code for merge/copy/delete

[Edgar_8]

Hi,

We are looking for a pl/sql code which does the following:
a cursor containing the records selected from the temp_table, after fecthing each row, each row must be
inserted into the prod_table after which the procedure must terminate. Any ideas how to PL/SQL code the above?

Thanks in advance!

[Yogeeraj_1]

hi again,

You did not mention your database version.

If you have 9i DB release 2, you can also sync up the flat file with the database table using this single command:

merge into EMP e1
using EXTERNAL_TABLE e2
on ( e2.empno = e1.empno )
when matched then
update set e1.sal = e2.sal
when not matched then
insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
values ( e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm,
e2.deptno )
/
commit;

that'll update the records in the EMP table from the flat file if they exist OR it will insert them.

Doing a direct path load would simply be:
insert /*+ append */ into emp select * from external_table;

Hope this helps!

regards
Yogeeraj

[Sanjay Kumar Suri]

Check the following PL/SQL (Where f1 is a assumed field in both the tables)

declare
i number := 0;
v_row temp_table%ROWTYPE;
cursor C1 is SELECT * FROM temp_table;
begin
OPEN C1;
LOOP
FETCH C1 INTO v_row;
EXIT WHEN C1%NOTFOUND;
insert into prod_table (f1) values (v_row.f1);
i := i + 1;
if i > 10000 then
commit;
i := 0;
end if;
END LOOP;
CLOSE C1;
delete from temp_table;
end;
/

sks

[Edgar_8]

Hi Sanjay,

Thanks for the assistance! If there are 39 columns how would one specify that in your code? And can
your code be included in a procedure?

Thanks in advance!

[Sanjay Kumar Suri]

Multiple fields can be inserted like:

insert into prod_table (f1, f2, ...) values (v_row.f1, v_row.f2, ...);

where .... is for more fields.

I am just checking the other part.

sks

[Edgar_8]

Hi,

The above PL/SQL was saved as a procedure & when executed the estimated duration for a single daily
partition is 40 hrs? Is the code at its optimum?

Thanks in advance!

[Sanjay Kumar Suri]

Hi

I don't think so as rows are inserted into prod_table one by one using cursor. And we are loading lot of rows in the cursor which is in memory.

So the better soultion is what I posted first

begin
insert into prod_table select * from temp_table;
delete from temp_table;
end;
/

Above insert can be changed to following if fields names are different.

insert into prod_table (f1, f2) select f1,f2 from temp_table;

sks

[Sanjay Kumar Suri]

My dear

I am closing for the day. I am off for 3 days.

All the best. I learnt a lot while solving this post.

Regards.

sks

[Yogeeraj_1]

hi,

Please allow me to add the following...

You can optimize on the "best way" suggested by sks above by breaking the statement into several parts and launch them in parallel.

But this would imply some prior knowledge of the data in the temporary table.

i.e.
begin
insert into prod_table select * from temp_table;
delete from temp_table;
end;
/

would be:
Script1
begin
insert into prod_table select * from temp_table where ;
delete from temp_table where ;
end;
/

begin
insert into prod_table select * from temp_table where ;
delete from temp_table where ;
end;
/

....
....

begin
insert into prod_table select * from temp_table where ;
delete from temp_table where ;
end;
/
===========================================

where may be:

between and

hope this helps too!

regards
Yogeeraj

[Brian Crabtree]

Edgar,

I have a thought. It would require some changes to your temp table, and it would make changes to your application more difficult, but...

What about adding an additional field into your temp_table (and prod if need be, it would simplify things) that you could put a distinct identifier that would correspond to a load number.

You could generate a controlfile with a constant load number set and load the tables, and then know exactly what you are moving when you run the procedure or load.

Example:

insert into prod_table as select colB,colB from temp_table where loadnum = '1';

delete from temp_table where loadnum = '1';

This would make it so that you don't need to worry about another load infringing on data that you want to delete.

Thanks,

Brian