Re: data transformation services

The closest Oracle equivalent to a DTS package is, I think, SQL Loader (specifically, it's control file). But SQL Loader only works with text files, not source databases.

The tools export and import work with databases, but what you export is what is imported. There's no facility for transforming the data in between those two steps.

A new 9i feature is 'external tables', where you effectively (ie, logically) plug a CSV (or some other textfile format) into the database, whilst leaving it physically as a text file, outside of the database. You then treat it as a read-only table, using bog-standard select statements. This starts getting close to DTS, because you could write standard PL/SQL packages and procedures to manipulate the data in a specified sequence of ways, with various checks on the data made along the way. You are still limited to transforming text files, though, since external tables really only use the SQL Loader API.

However, provision has been made in the syntax for third parties to supply their own loader drivers, so it should be theoretically possible for someone to come up with a SQL Server driver, or an Access driver, at which point you'd be able to extract from other databases, yet leave the data in those other databases, whilst building a modified recordset you wished to store in Oracle. But so far as I know, that's not possible yet using this technique.

Regards
HJR

--
------------------------------------------
Resources for Oracle : www.hjrdba.com
============================

"Sameer Bhardwaj" <Sameer.Bhardwaj_at_india.techspan.com> wrote in message
news:39d276dc.0203250111.49df8cca_at_posting.google.com...


> Do we have an equivalent of Data Transformation Services of MS-SQL

> Server in Oracle ?