I have a use case where I need to keep watch on table A in the oracle database XYZ by running a select statement with a where clause. If this select statement fetches any rows, then instantly I need to insert those rows into another table B which is another Oracle database MNO.

Also, suppose when I am in watch and the second time the same select statement retrieved a row which was already inserted in above step but now it has a change of values for few columns then, instantly the other table B should also get updated with this updated column value from table A.

Can I achieve this using shell scripting? Could anyone kindly let me know how to achieve this?

Many thanks for your suggestions in advance.

CodePudding user response：

I'd say that merge is what you need:

merge into mno.table_2 b  -- or, possibly, table_2@mno
  using table_1 a
  on (a.id = b.id)
  when matched then update set
    (b.col1 = a.col1,
     b.col2 = a.col2
    )
  when not matched then insert (col1, col2)
    values (a.col1, a.col2);

Note line #1:

• if mno really is a "database" (and not just another user in the same database), then you'd have to use a database link (commented part of line #1)
• if mno is just another user, then it should grant select, insert, update on its table to user xyz; otherwise, that operation won't work

Merge also means that you don't have to separately check whether that row exists in another table or not.

Shell scripting? Why? Keep everything within the database.

"Keep watch" can be done by

• creating a database trigger on xyz.table_1 so that as soon as something changes, it fires and runs merge
• or, schedule a job (using dbms_scheduler) which will periodically (for example, every hour, twice a day, ...) run merge