5 Replies Latest reply on May 22, 2013 8:30 AM by Kgronau-Oracle

    Data Migration through Script

      how I can transfer the table data from access to oracle 11g through PL/SQL Script, I have time machine which generate the data in MS-Access.

      Edited by: user12879396 on May 12, 2013 7:42 AM
        • 1. Re: Data Migration through Script
          You could use the Database Gateway for ODBC installed on the Windows machine where your MS Access file resides (or any other Windows based OS machine) and connect from your Oracle database through DG4ODBC to the MS Access database using the MS Access ODBC driver.

          In general have a look at this PDF: www.oracle.com/technetwork/database/gateways/gateways-fov-133149.pdf
          and for a detailed description of DG4ODBC please check out www.oracle.com/pls/db112/to_pdf?pathname=gateways.112/e12070.pdf

          When you need details about setting up DG4ODBC on MS Windows, please log into "My Oracle Support" portal and look for "how to setup DG4ODBC on Windows".

          - Klaus
          • 2. Re: Data Migration through Script
            very nice information
            kgronau actually I want that to run concurrent program from oracle application server on monthly bases the data on machine will transfer to oracle custom table this concurrent program use pl/sql procedure,
            oracle application server and database on same network while machine database(Ms-Access) on another network both can connect thorough Internet
            • 3. Re: Data Migration through Script
              You need to read somehow the information from the MS Access file and you want to do it using PL/SQL so a very common way is DG4ODBC. It doesn't really matter where the file is located. So when the admins open the Oracle port on the Firewall you can cnnect from your Oracle database to DG4ODBC running on the MS Access machine and read the file information. Another approach would be to ftp the file from the foreign location to your environment using a cron job and to then read from the MDB file using a local DG4ODBC installation.
              Not sure if there's a Database Adapter for the web server which allows you to read MDB files, but you might ask this in the correct forum.
              • 4. Re: Data Migration through Script
                Please may you explain about this
                • 5. Re: Data Migration through Script
                  Not sure what to explain more detailed ....

                  As I said earlier Oracle offers a product called Database Gateway for ODBC which allows yo to read any information stored in a foreign data store as long as you have a suitable ODBC driver. For MS Access for example a suitable ODBC driver is the ODBC driver Microsoft ships with the office package.
                  So once you configured an ODBC driver connection that reads the information from MDB file you can also configure DG4ODBC on this machine to use the configured ODBC driver.

                  And DG4ODBC allows you now to connect from your Oracle database using a database link to read from the MDB file. Within PL/SQl you can execute any statement using the database link to access the MDB file.

                  Best would be to check out the Gateway Master Note on My Oracle Support portal "Master Note for Oracle Gateway Products [ID 1083703.1]" which is a good starting point to get familiar with the Database Gateway for ODBC.