Oracle 10g: Pl/SQL to copy data from one db to another

HI,
The scenario goes like this:
I have a script(A) that returns some data(B) in one db. I need to write a pl/sql that helps to copy/move/transfer (B) to another db(C), which already has data(D) related to the one returned by my script. I need to facilitate comparision among fields of (B) with fields of (D).

Any help/suggestions/guidance is highly apprciated.

Thanks.
sspb485Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Are the databases linked (dblink)?

What are you thinking here?

Without a lot of information and you can, I would look at extracting the data in some delimited format (CSV) using sql*plus, move the file ofer to tthe database server (C) and create an external table then run a quick script to merge/insert the data.
0
sdstuberCommented:
create a db link between the two db's


select * from table1
minus
select * from table2@db2

and compare in the other direction too

select * from table2@db2
minus
select * from table1
0
ajexpertCommented:
You can create a DB_LINK, so that two db's can communicate and write a logic in PL/SQL to compare / insert as needed.
 
HTH
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sspb485Author Commented:
Hi guys,

Thanks for your commments/suggestions.

The scenario would not let me use DBLINK so we will put that off.
I think slightwv's idea is plausible in my case.

Without a lot of information and you can, I would look at extracting the data in some delimited format (CSV) using sql*plus, move the file ofer to tthe database server (C) and create an external table then run a quick script to merge/insert the data.

Since I am a newbie to Oracle, can you please provide me details of how do i do it?

Thanks again.
0
slightwv (䄆 Netminder) Commented:
Here's the basics.  You should be able to run this example from sqlplus.  I tested this with 10.2.0.3.

You'll probably need to change the directory names around.  Also, I just spooled my output to where I needed it.  You'll need to ftp/rcp/??? the file from one server to the other.

You will likely need to tweak the merge for your specific requirements and probably put something in place to check the 'bad' and 'discard' file just in case.
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));

drop table tab2 purge;
create table tab2(col1 char(1), col2 char(1));


insert into tab1 values('a','c');
insert into tab1 values('c','d');

insert into tab2 values('a','b');
commit;


--create a CSV
set pages 0
set timing off
set feedback off
spool c:\tab1.txt
select col1 || ',' || col2 from tab1;
spool off
set feedback on


--prep database server for external table
drop directory file_dir;
create directory file_dir as 'C:\';


--create external table
drop table test_external;
create table test_external ( col1 char(1), col2 char(1))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
 DEFAULT DIRECTORY file_dir
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY newline
  BADFILE 'tab1.bad'
  DISCARDFILE 'tab1.dis'
  LOGFILE 'tab1.log'
  FIELDS TERMINATED BY ','
  MISSING FIELD VALUES ARE NULL
  REJECT ROWS WITH ALL NULL
  FIELDS
  (
   col1,
   col2
  )
 )
 LOCATION ('tab1.txt')
)
REJECT LIMIT UNLIMITED;



--before merge
select * from tab2;

--merge external table into tab2
merge into tab2 t2
using (
	select * from test_external
) t_ext
on ( t2.col1 = t_ext.col1 )
when matched then
    update set t2.col2 = t_ext.col2
when not matched then
	insert (t2.col1, t2.col2) values (t_ext.col1, t_ext.col2)
;

--after merge
select * from tab2;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
I have to object to this delete request.  

>>I think slightwv's idea is plausible in my case.

I provided the requested information and never received a response back.
0
slightwv (䄆 Netminder) Commented:
I suggest PAQ accept: http:#a30020370
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.