Posted on May 3, 2013 by Carla Steinmetz
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
NOTE: A few things to beware of:
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.
Comments