Home » SQL & PL/SQL » SQL & PL/SQL » automate inserts from one schema to another (Oracle 10g)
automate inserts from one schema to another [message #572955] Tue, 18 December 2012 23:15 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

We have two databases one localdb with user rakdb and another one remotely remotedb with user rakdb .We need to be in sync with data in one table called om_item, where the users are inserting data on daily basis and the user sends us the insert script everday to run it on local databse to insert the new records in local database.I managed to create a file which records all the inserts into one text file in one directory.Can we have a scheduler to pick this text file from the specified folder and send mail using utl_mail.



CREATE TABLE ITEM (IT_CODE VARCHAR2(12),IT_NAME VARCHAR2(20));

INSERT INTO ITEM VALUES ('A','AAA');

CREATE OR REPLACE DIRECTORY MY_DIR AS 'C:\TEMP';


CREATE OR REPLACE PROCEDURE it_status
    IS
       v_filehandle   UTL_FILE.FILE_TYPE;
       CURSOR itc
       IS
       SELECT 'INSERT INTO ITEM (IT_CODE, ITEM_NAME) VALUES ('''
              || IT_CODE || ''',''' || IT_NAME || ''');'
              AS insert_statement
       FROM   item
      ORDER  BY it_code;
   BEGIN
     -- v_filehandle := UTL_FILE.FOPEN ('MY_DIR', 'AAAA.TXT', 'W');
     v_filehandle := 
     UTL_FILE.fopen 
       ('MY_DIR', 
        'AAAA_' || TO_CHAR (SYSDATE, 'MMDDYYYY_HHAM') || '.TXT', 
        'w');
      FOR v_it_rec IN itc
      LOOP
         UTL_FILE.PUT_LINE (v_filehandle, v_it_rec.insert_statement);
      END LOOP;
         UTL_FILE.PUT_LINE (v_filehandle, 'COMMIT;');
         UTL_FILE.FCLOSE (v_filehandle);
   END it_status;
   /

Procedure created.

EXEC it_status

HOST TYPE c:\temp\aaaa.txt

INSERT INTO ITEM (IT_CODE, ITEM_NAME) VALUES ('A','AAA');
COMMIT;


Re: automate inserts from one schema to another [message #572968 is a reply to message #572955] Wed, 19 December 2012 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is one of the purpose of materialized views.
Use them, do not reinvent the wheel.

Regards
Michel
Re: automate inserts from one schema to another [message #572972 is a reply to message #572968] Wed, 19 December 2012 01:46 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks Michel,kindly give me more details on how materialized views can be of use in this case and kindly give me the references or link .
Re: automate inserts from one schema to another [message #572975 is a reply to message #572972] Wed, 19 December 2012 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same place as usual: the documentation, just use Ctl-F and "replication" and "OK".

Regards
Michel
Re: automate inserts from one schema to another [message #572977 is a reply to message #572975] Wed, 19 December 2012 02:11 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks a lot michel , i will check using search criteria as replication on this site and in google.
Previous Topic: find the lock on particular date and time
Next Topic: Replace Reapiting the Same Description with -DO-
Goto Forum:
  


Current Time: Sat Sep 20 15:46:27 CDT 2014

Total time taken to generate the page: 0.07035 seconds