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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
How to concatenate rows into a single CLOB in PL/SQL
IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 6
IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 3
IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 2