Q
Problem solve Get help with specific problems with your technologies, process and projects.

Background shell script to update remote databases

I have a table, WORK_ORDER, in my local database (Oacle 8.0) with fields ex_code, telno, name etc. The value in ex_code refers to remote database name, i.e. exc_code '001' -> remote database name RDB1 and so on. The system is connected through five remote databases. On the basis of ex_code field, whenever record is inserted in WORK_ORDER table, immediately, the relevant records should be inserted into remote database in table SLIM. After the record is inserted in table SLIM in a remote database, the relevent record should be deleted from WORK_ORDER table in the local database.

I am planning to write a shell script in the background that will access the WORK_ORDER table from a local database every five minutes. Then, it will call a procedure to perform insertion in relevant database. Is it ok? Or is there any other method to do it properly?

Your method will work fine. You can also use a trigger. In either case, you'll want to use PL/SQL to perform dynamic SQL statements. For instance, if you want to write a script to schedule every five minutes, then the script might look something like the following:

DECLARE
   -- fields from table
   ex_code VARCHAR2(3);
   telno   NUMBER;
   name    VARCHAR2(50);
   -- Cursor to hold all records from table
   c1 CURSOR IS SELECT * FROM work_order;
   -- remote database link name
   link    VARCHAR2(5);
BEGIN
   -- open cursor
   OPEN c1;
   -- loop through cursor to process the results
   LOOP
      FETCH c1 INTO ex_code,telno,name;
      -- exit when no more records to process
      EXIT WHEN c1%NOTFOUND;
      -- Determine the link name based on 
      -- the EX_CODE value.
      SELECT
DECODE(ex_code,'001','RDB1','002','RDB2','003','RDB3')
      INTO link FROM dual; 
      -- Insert data into remote table
      EXEC IMMEDIATE 'INSERT INTO slim@'||link||
          '
VALUES('||ex_code||','||telno||','||name||')';
   END LOOP;
   -- Delete records from WORK_ORDER
   DELETE FROM work_order;
END;
/
COMMIT;

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close