Oracle SQL / PLSQL : I need to copy data from one database to another

Question

I have two instances of the same database, but data is only committed to the "original" one. I need to copy inserted data from certain tables and commit them to the same tables in the second DB automatically. How can I do it?

I've already created synonyms for the tables in the second DB on original and within a specially prepared trigger I tried to use INSERT INTO ... statement with :new. but it is causing the data to not be committed anywhere and I receive Oracle Errors like:

ORA-02291: integrity constraint (PRDBSHADOW.FK_ED_PHY_ENT) violated.

Here is my trigger code

create or replace TRIGGER INS_COPY_DATA
AFTER INSERT ON ORIGDB.TABLE_A
REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW
BEGIN

  insert into COPY_TABLE_A(val1,val2,val3,val4) values (:new.val1, :new.val2, :new.val3, :new.val4);

END;

you need to do it only once or you want to copy each time when data is inserted in the original one. — aksr
– aksr, Commented Jan 10, 2020 at 9:22
Please edit your question and add the complete code of your stored procedure — user330315
– user330315, Commented Jan 10, 2020 at 9:53
Its's hard to tell, because the error doesn't relate to the example table names. But it's an FK error, so the most likely reason seems to be that COPY_TABLE_A has a reference to COPY_TABLE_B, but you didn't copy the data from TABLE_B to COPY_TABLE_B, or you did, but with a different primary key. TL;DR: What @a_horse_with_no_name said. :-) — GolezTrol
– GolezTrol, Commented Jan 10, 2020 at 10:16
So it would mean that I need to also prepare data for that table? If you understand what a foreign key constraint is and how it works, it would see that the answer to your question is yes, you first need to make sure the referenced table contains the relevant values. — Abra
– Abra, Commented Jan 10, 2020 at 16:00

ekochergin · Accepted Answer · 2020-01-10 13:46:46Z

I think the entry in parent table is missing here. At least the FK ending of constraint is telling me so.

It means you need to insert first all the data into a "parent" table in order to be able to insert records in a "child".

For example the table auto_maker is having 3 rows only: Audi, Peugeot, and Honda. Another table named "model" has 2 columns "maker" and "model". "maker" is a foreign key referencing to the "auto_maker" table.

It means in the models table are only the records allowed whose "maker" column value exists in "auto_maker" table.

In other words only these are available:

maker model Audi A4 Peugeot 308 Honda Accord

Of course you can enter every model you wish, but "maker" value has to exist in the auto_maker table.

This is what probably happen - the trigger tries to insert a data in a column which is referencing to a "parent" table and the :new value just doesn't exist.

The following script will let you know what table you need to fill first.

select aic.index_owner, aic.table_name, aic.column_name
  from all_constraints uc,
       all_ind_columns aic
 where aic.INDEX_NAME = uc.r_constraint_name
   and uc.table_name = 'TABLE_A'
   and uc.constraint_type = 'R';

If the query returns something just create similar triggers on those tables with similar logic you already have

Collectives™ on Stack Overflow

Oracle SQL / PLSQL : I need to copy data from one database to another

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related