Other Oracle database topics - Import from different systems

Author

Import from different systems

tombeck1973@gmail.com

2005-09-16, 7:23 am

Hi

We need to import data from different database systems into an Oracle
database. The other systems are: MS SQL, Firebird and MS Access. The
data structure is not identical - in one source system, the data might
be in a flat table, in the target system it needs to be distributed
into several tables (1:n relationships, etc.) All this needs to happen
regularly and automatically, e.g. every night.

I don't know Oracle or Firebird, but I've been asked to figure out a
way how to do it. Does Oracle have tools for this kind of import? Or
are there any other commercial tools that do this kind of data
synchronisation / transformation between different database systems?

(The problem is: we have a commercial application based on an Oracle
database. It needs user data & hardware data. We already have that data
in different databases so we regularly need to import it.)

I'd appreciate if anyone could point me in the right direction.

sybrandb@yahoo.com

2005-09-16, 7:23 am

If you want to build only one interface, dumping the data to a flat
ASCII-file is probably the best bet. Oracle has (from 9i onwards) an
external table facility, which allows you to treat an Ascii file as an
ordinary table.
One other option is to use the Heterogeneous Gateway, which allows
Oracle to connect to Sqlserver, and other databases. HG comes in an OLE
DB flavour and in an ODBC flavour.
Finally you could use the Oracle ODBC driver to connect your source
database to Oracle.

--
Sybrand Bakker
Senior Oracle DBA

HansF

2005-09-16, 9:23 am

Oracle has a powerful (but complex to learn initially) bulk loader called
SQL Loader. It is described in considerable detail, with many case
studies, in the Utilities manual found at http://docs.oracle.com in the
database section.

Since SQL Loader is a command line utility that uses a control file to
provide directives on how to map and load the data file(s), it can be
scheduled to run on a regular basis. IMO, this is best accomplished using
the Scheduler included with the latest versions of the database (10g),
although any task manager or cron utility can be used. (Personally I
would not immediately reject the idea of loading the data into a 10g
version of the database and using database links to migrate it back to a
lower version of the database until such a solution has been proven
incorrect.)

The above is quite generic - OS and database version would help further
discussion.

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***

sim

2005-09-16, 9:23 am

This is exactly what ETL tools are for. There are a lot of different
tools on the market like Informatica PowerMart/PowerCenter, IBM
DataStage or Oracle Warehouse Builder (not recommended).

sim

HansF

2005-09-16, 9:23 am

I should have mentioned - SQL Loader is designed to load data from a
mappable data source (such as a file or input stream). Two ways to use
SQL Loader: bulk load into a 'regular' table; source for table defined
with 'ORGANIZATION EXTERNAL'.

It complements other data access methods such as DBLinks and Heterogeneous
services described by Sybrand, and is part of the larger ETL strategy
identified by 'sim'

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***

Ruud de Koter

2005-09-16, 9:23 am

sim wrote:
> This is exactly what ETL tools are for. There are a lot of different
> tools on the market like Informatica PowerMart/PowerCenter, IBM
> DataStage or Oracle Warehouse Builder (not recommended).
>
> sim
>

Would you mind commenting on the 'not recommended' remark? We are
currently looking into using the Warehouse Builder, so I am rather
interested in this quick remark.

TIA,

Ruud de Koter.

Sponsored Links