using an ETL tool vs an store proc to export/import data

[yehong]

I have a requirement to move data from schema1 to schema2 on same oracle 9 server. The tables in schema2 have less number of columns as in schema1. For example schema1.table1 has 10 columns and shema2.table1 has 7 columns. I am debating whether I should use an ETL tool to do that or write a store proc. I want a cost effective, less maintenance and fast implementation solution. I am looking for your ideas.
Thanks.

 

[Thargy]

yehong,

why not use sql loader to do the job.

You can save all the data from schema 1 as a text file, and then specify which columns to load into schema 2.

The sql loader control file will do the job for you nicely. Also, sql loader produces a log file of and bad records which failed to load, so you can also do some data cleansing at the same time.

Regards

Tharg

Grinding away at things Oracular

 

[yehong]

Thanks thargtheslayr. Do we need a license to use sql loader or its a free and license free utility? Our requirement is to use open source/license free utilities as much as possible and also we need to be compatible with Oracle, SQL Server, MySQL and other RDBMS databases. Will sql loader be compatible with other dbs as mentioned above?

 

[williey]

You should have sqlldr installed. check out your bin directory under Oracle Home.

Definitely go with the sqlldr. My experience with ETL tools is they are made coding of transformation easy at the expense of performance. Especially loading into tables. Sqlldr is still ultimately used to load the table after ETL completes with the transformation.

Birth of new OSs is the natural evolution of Unix.

 

[Thargy]

Yehong,

sql loader is a standard oracle utility. If you've paid for Oracle, you've paid for sql loader. Williey's advice above is purely where to find the executable file.

Note that SQL Loader can also do transformation and data manipulation during the load - it gives some transformation functions for little or no extra effort.

SQL Loader is purely for oracle. You should investigate other free tools for other RDBMS. Look at DTS for SQL Server, as it's part of the standard tools (I believe).

Regards

Tharg

Grinding away at things Oracular