OraFAQ Forum: SQL & PL/SQL » ETL in Oracle 10g (merged 4)

Home » SQL & PL/SQL » SQL & PL/SQL » ETL in Oracle 10g (merged 4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ETL in Oracle 10g (merged 4) [message #386825]

Tue, 17 February 2009 02:28

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

Report message to a moderator

Re: ETL in oracle 10g [message #386827 is a reply to message #386825]

Tue, 17 February 2009 02:32

ThomasG
Messages: 3212
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.

Report message to a moderator

Re: ETL in oracle 10g [message #386829 is a reply to message #386825]

Tue, 17 February 2009 02:33

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: ETL in oracle 10g [message #386840 is a reply to message #386825]

Tue, 17 February 2009 02:42

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

Report message to a moderator

Re: ETL in oracle 10g [message #386842 is a reply to message #386825]

Tue, 17 February 2009 02:43

Maaher
Messages: 7065
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

Report message to a moderator

Re: ETL in oracle 10g [message #386852 is a reply to message #386842]

Tue, 17 February 2009 03:02

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.

Report message to a moderator

Re: ETL in oracle 10g [message #386868 is a reply to message #386852]

Tue, 17 February 2009 03:24

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Have you checked data pump?

MHE

Report message to a moderator

Re: ETL in Oracle 10g (merged 4) [message #386872 is a reply to message #386825]

Tue, 17 February 2009 03:28

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?

Report message to a moderator

Re: ETL in Oracle 10g (merged 4) [message #386885 is a reply to message #386872]

Tue, 17 February 2009 03:44

Maaher
Messages: 7065
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

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?

Report message to a moderator

Re: ETL in Oracle 10g (merged 4) [message #386897 is a reply to message #386889]

Tue, 17 February 2009 04:02

Maaher
Messages: 7065
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

Report message to a moderator

Re: ETL in Oracle 10g (merged 4) [message #386901 is a reply to message #386897]

Tue, 17 February 2009 04:08

trivendra
Messages: 211
Registered: October 2007
Location: Phoenix

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

swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar

Senior Member

thanks all for your suggestion.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 13:04:17 CST 2024