1

I have a task to create a stored procedure in Oracle DB. Given two different databases DB1 with student_lookup table and DB2 with student_master table . The SP needs to check if DB2.student_master's record exists in DB1.student_lookup table.

  1. If the record exists in DB1 then don't anything
  2. If the record doesn't exists in DB1 then add from DB2
  3. If the record is in DB1 but not DB2 then update that record and set partition_key column to 1.

Any help will be appreciated. I am completely new to Oracle DBA.

jakonda
  • 57
  • 1
  • 6
  • [How to create a DB link between two oracle instances](https://stackoverflow.com/questions/13267218/how-to-create-a-db-link-between-two-oracle-instances) – Abra Nov 07 '19 at 02:29
  • [Oracle “Partition By” Keyword](https://stackoverflow.com/questions/561836/oracle-partition-by-keyword) – Abra Nov 07 '19 at 02:34

1 Answers1

1

If it's two users using:

MERGE INTO db1.student_lookup a 
USING 
(select * from db2.student_mater) b 
ON (
a.id = b.id 
AND <others join column>
)
WHEN MATCHED THEN UPDATE SET a.partition_key = 1
WHEN NOT MATCHED THEN INSERT (<a.column>)
VALUES (<b.column>)

If it's two db:

CREATE DATABASE LINK DBLINK_DB1_DB2
  CONNECT TO DB2 IDENTIFIED BY <ENTER USER PASSWORD HERE>
  USING '<FROM tnsnames>'


MERGE INTO db1.student_lookup a 
USING 
(select * from "student_mater"@"DBLINK_DB1_DB2") b 
ON (
a.id = b.id 
AND <others join column>
)
WHEN MATCHED THEN UPDATE SET a.partition_key = 1
WHEN NOT MATCHED THEN INSERT (<a.column>)
VALUES (<b.column>)

If you neen SP simple megre into your SP.

Rustam Pulatov
  • 625
  • 1
  • 9
  • 20