How to copy a single row from 1 table to another in a different schema

Here's the dilemma. I have 2 databases structured exactly the same DB1 AND DB2.
Both databases have a schema called EMPOWER where all the tables are housed. There are more than 500 tables and all tables are linked by their primary key of LNKEY.

Even though these 2 dbs have the same structure, the data they contain is not identical. I have specific records that I need to copy from DB1 to DB2.

Currently I have a stored procedure that copies one loan record to a new loan record in the same database.
i.e. EXECUTE EMPOWER.COPYLOAN(‘111SOURCE’,’222TARGET’);

here’s the sql for the stored procedure

CREATE PROCEDURE EMPOWER.COPYLOAN
(FROMNUM IN VARCHAR2, TONUM IN VARCHAR2) AS
BEGIN
INSERT INTO EMPOWER.ADD_CRED (LNKEY,WHICHBORR,IDX,ADD_CRED_ALT_NAME)
SELECT TONUM,WHICHBORR,IDX,ADD_CRED_ALT_NAME FROM EMPOWER.ADD_CRED WHERE LNKEY = FROMNUM;
INSERT INTO EMPOWER.ALIMONY (LNKEY,ALIMONY_PMT,ALIMONY_NUMPMT,ALIMONY_NAME)
SELECT TONUM,ALIMONY_PMT,ALIMONY_NUMPMT,ALIMONY_NAME FROM EMPOWER.ALIMONY WHERE LNKEY = FROMNUM;
...
did the above for all tables in that schema
...
INSERT INTO EMPOWER.ARMDISC (LNKEY,ARM_PROGRAM,INDEX_INFO)
SELECT TONUM,ARM_PROGRAM,INDEX_INFO FROM EMPOWER.ARMDISC WHERE LNKEY = FROMNUM;
END;

But this time, I want to copy these specific records from DB1 into DB2.
DB2 has also a schema called EMPOWER with all the underlying tables.

These 2 databases are on 2 separate boxes.
Your help will be very much appreciated!

Robel Girma

cant you use db link?

or copy command in sqlplus?

Yes, that's an option. Can anyone point me to some documentations I can refer to for using dblinks ?
Or if you can, some examples on how this works.

Thanks for you help

Robel Girma

Dblinks are a start...
Have you thought about using snapshots (v8) or materialised views (8i,9) to do this as well....depending on whether you want the whole table or even schema replicated.

All that is needed for a database link is to have a valid tnsnames alias to point to either db1 or db2.
The Oracle documentation on links is good and explains the concept well.

Why i suggested replication via snapshots or materialised views is once this is setup Oracle can manage the whole process via job scheduling.
Does the requirement for the application require a loan record in one database (db1) be replicated in database (db2) if so, i suggest reading the chapters in the documentation on basic replication. You already have the building blocks (primary keys on all tables) and the same schema name, so setting this up will be straightforward.

Have Fun

Thanks you all for your input. I'll implement the suggested db link method. I think this sounds like what I'm looking for.

Robel