Home » SQL & PL/SQL » SQL & PL/SQL » ETL in Oracle 10g (merged 4)
ETL in Oracle 10g (merged 4) [message #386825] Tue, 17 February 2009 02:28 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi

We know that in sql loader we can load data from flat file into a table.

Is there any better way to do the same process in oracle

Thanks
Re: ETL in oracle 10g [message #386827 is a reply to message #386825] Tue, 17 February 2009 02:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Depends on how you define "better".

Another way of doing it would be External Tables.

But whether that is "better" depends on your actual needs.

Re: ETL in oracle 10g [message #386829 is a reply to message #386825] Tue, 17 February 2009 02:33 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
utl_file
external tables
use java
pre-parse the flat file to convert contents into sql insert.

Every tool / approach has its own usage.
Re: ETL in oracle 10g [message #386840 is a reply to message #386825] Tue, 17 February 2009 02:42 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
yeah, you can check the following two

1) external table
2) write a pl/sql package to read/write flat file using file_utl package
Re: ETL in oracle 10g [message #386842 is a reply to message #386825] Tue, 17 February 2009 02:43 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
What do you call 'better'? AFAIK, SQL*Loader is very very hard to beat when it comes to data validation and speed.

There are other ways of loading flat files, but I wonder if they are 'better', whatever that may be. Perhaps you can start by providing a little more detail: what are you looking for and why?

MHE
Re: ETL in oracle 10g [message #386852 is a reply to message #386842] Tue, 17 February 2009 03:02 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi

Well i know sqlldr is excellent in this respect.

I just thought that there might have been some other new options available.

as far as the requirenet is concerned it is simple.

there are 3 flat files and they are required to be loaded into 3 tables.

Re: ETL in oracle 10g [message #386868 is a reply to message #386852] Tue, 17 February 2009 03:24 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Have you checked data pump?

MHE
Re: ETL in Oracle 10g (merged 4) [message #386872 is a reply to message #386825] Tue, 17 February 2009 03:28 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
no i havent.But will data dump load data from flat files to table in a much efficient way?
Re: ETL in Oracle 10g (merged 4) [message #386885 is a reply to message #386872] Tue, 17 February 2009 03:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That depends. What do the flat files look like? Do you make them yourself? If it is a simple exp/impt thingie, you could use data pump. We can't tell.

edit: you make it sound like you aren't quite happy with the performance of sqlldr. Is that correct?

MHE

[Updated on: Tue, 17 February 2009 03:45]

Report message to a moderator

Re: ETL in Oracle 10g (merged 4) [message #386889 is a reply to message #386885] Tue, 17 February 2009 03:48 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
no these are .dat files which are landed in our unix box from a eternal source.

the values in the files are separated by comma.

it is required to do a ETL on the files and load it in tables.

in such a case will data dump be helpful?

Re: ETL in Oracle 10g (merged 4) [message #386897 is a reply to message #386889] Tue, 17 February 2009 04:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
In that case, I believe that sqlldr is the fastest option, although external tables might be a viable option too. I'd say: check it out.

MHE
Re: ETL in Oracle 10g (merged 4) [message #386901 is a reply to message #386897] Tue, 17 February 2009 04:08 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
swas_fly,

In this case , as suggested by Maaher, sqlldr is the best option as you can run this on your client machine also.

Once your *.dat file is loaded in the table you can transform the data into DWH.

--
Is it a case where DB2(replicator) data files are loaded in Oracle tables
--

Thanks
Trivendra

[Updated on: Tue, 17 February 2009 04:10]

Report message to a moderator

Re: ETL in Oracle 10g (merged 4) [message #386905 is a reply to message #386825] Tue, 17 February 2009 04:11 Go to previous message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
thanks all for your suggestion.

Previous Topic: I need to sort the record omiting negative sign.
Next Topic: UTL_FILE & DBMS_LOB
Goto Forum:
  


Current Time: Wed Dec 07 20:28:49 CST 2016

Total time taken to generate the page: 0.12492 seconds