Access Your Non-Oracle Data Using the Oracle ODBC Gateway

So, you want to get some data OUT of a non-Oracle system and IN to your Oracle database.  How to do it?

We recently had a customer that wanted to get some employee data out of a SQL Server PeopleSoft system into an Oracle database where they could do some additional reporting.  They were at a loss on an easy, repeatable method for accomplishing this every evening.  The answer is easy with an Oracle Gateway, Oracle Scheduler, and a simple custom procedure.

There are several different types of Oracle Gateways tailored to different systems.  MS SQL Server, Informix, Sybase, etc.  But, one of the easiest and cheapest (free with your database license) is the Oracle Database Gateway for ODBC.  ODBC is generic, so it can connect to most any type of database.  If you use one of the specialized Gateways, the setup will be slightly easier, but definitely more costly.

Oracle ODBC Gateway Installation

These are the main steps for installing and configuring the Oracle ODBC Gateway.  Detailed information on Oracle Database Gateway installation can be found at:  http://docs.oracle.com/cd/E11882_01/gateways.112/e12061.pdf

1. Install the Gateway software using the Oracle Installer onto the box where the non-Oracle database resides.
2. Configure the Gateway Initialization Parameter File on the non-Oracle box.
3. Configure Oracle Net for the Gateway on the non-Oracle box.  The Oracle Net Listener listens for incoming requests from the Oracle database.  Once it's configured, then stop and start the Oracle Net Listener for the Gateway so it picks up the new settings.
4. Configure the Oracle Database for Gateway Access on the Oracle box.  Since you'll be "pulling" the data from the non-Oracle to the Oracle side, you need to configure the TNSNAMES.ORA file to recognize the new Gateway.
5. On the Oracle box, create a new database link using SQL*Plus to the non-Oracle data using the new Gateway SID identified in the TNSNAMES.ORA file.

NOTE: A few things to beware of:

1. During the install, please allow the Oracle Net Configuration Assistant to run and accept the defaults.  You will need to alter the LISTENER.ORA file manually later in the installation.
2. When setting the initialization parameter values, checking on the DSN name in the Windows ODBC Data Source Administrator is key.  The value is case specific, and you need to ensure the correct version for the ODBC drivers (32 vs 64 bit).
3. When creating the database links, it is case specific.  Use quotes, “ ”, to enter any case specific username or password.
4. May need to set global_names to false if you get an error message about the global_names.  SQL> alter database set global_names = false;

Custom Procedure

It is an easy process to set up the custom procedure to "pull" the data over.  It's basically a truncate of the target table to get a fresh dataset and then select from the source SQL Server table into the target Oracle table.  Using SQL*Plus on the Oracle box, issue the following commands:

create or replace procedure move_translation is

begin

  truncate table target_table;

  insert into target_table

    (empid

      , addr1, city, state, zip

      , dayphone, mobile

      , dept, contacttype, effectivedate

      , email, fax

      , fullname, namefirst, namelast, namemiddle

      , hiredate, jobclasscode, rate)

  select emailid

      , address1, city, state, postal

      , work_phone, mobile_phone

      , deptdescr, contact_title, effdt

      , emplid, fax_phone

      , name, first_name, last_name, middle_name

      , hire_dt, jobcode, hrly_rt_maximum

    from source_table@db_link_name;

  commit;

end move_translation;

/

Oracle Scheduler

For the Oracle Scheduler, it's a simple call to DBMS_SCHEDULER.  This package is an update to the older Job Scheduler and allows for a lot more flexibilty.  Detailed information on Oracle Scheduler can be found at http://docs.oracle.com/cd/E11882_01/server.112/e17120/scheduse.htm

Using SQL*Plus on the Oracle box, issue the following commands to create and enable the daily job:

begin

dbms_scheduler.create_job(job_name => 'MOVE_TRANSLATION_JOB'

  , job_type => 'STORED_PROCEDURE'

  , job_action => 'MOVE_TRANSLATION'

  , start_date => trunc(systimestamp) + 1

  , repeat_interval => 'FREQ=DAILY; BYHOUR=1;'

  , auto_drop => FALSE);

dbms_scheduler.enable('MOVE_TRANSLATION_JOB');

end;

/

Now your data should flow from the non-Oracle table to the Oracle table every evening.