Home » SQL & PL/SQL » SQL & PL/SQL » How to load the data from flat file? -- urgent please (Oracle 9i, 10g, 11g, unix, windows nt)
How to load the data from flat file? -- urgent please [message #437252] Sat, 02 January 2010 13:20 Go to next message
arunn
Messages: 52
Registered: September 2008
Location: usa
Member
Friends,

I have 1 million data in the flat files. i want the technique to load the data in oracle database tables....
which one is best in the below?
a. bulk collect
b. external table
c. sql loader
d. split the data using unix scripting.
e. split the data using pl/sql( when i use the commit? like for every 25000 thousand rows )

or else other technique?

thanks in advance friends...
Re: How to load the data from flat file? -- urgent please [message #437254 is a reply to message #437252] Sat, 02 January 2010 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define "best".
Is this just load or is there any transformation?
Does the file have a clear format?

Regards
Michel
Re: How to load the data from flat file? -- urgent please [message #437255 is a reply to message #437254] Sat, 02 January 2010 13:29 Go to previous messageGo to next message
arunn
Messages: 52
Registered: September 2008
Location: usa
Member
Michel Cadot wrote on Sat, 02 January 2010 13:25
Define "best".
Is this just load or is there any transformation?
Does the file have a clear format?

Regards
Michel


thanks for interest....

load, validate, finally insert or update the data in the oracle db table.
Re: How to load the data from flat file? -- urgent please [message #437257 is a reply to message #437252] Sat, 02 January 2010 13:43 Go to previous messageGo to next message
cookiemonster
Messages: 13951
Registered: September 2008
Location: Rainy Manchester
Senior Member
An external table will cover load and validate. Then simple merge command should do the rest.
Re: How to load the data from flat file? -- urgent please [message #437258 is a reply to message #437257] Sat, 02 January 2010 13:44 Go to previous messageGo to next message
arunn
Messages: 52
Registered: September 2008
Location: usa
Member
cookiemonster wrote on Sat, 02 January 2010 13:43
An external table will cover load and validate. Then simple merge command should do the rest.



which is more faster than bulk collect?
Re: How to load the data from flat file? -- urgent please [message #437260 is a reply to message #437258] Sat, 02 January 2010 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
External table is far faster than PL/SQL including bulk one.
Besides how do you want to use bulk collect?

Regards
Michel

[Updated on: Sat, 02 January 2010 14:00]

Report message to a moderator

Re: How to load the data from flat file? -- urgent please [message #437269 is a reply to message #437260] Sat, 02 January 2010 18:17 Go to previous messageGo to next message
arunn
Messages: 52
Registered: September 2008
Location: usa
Member
Michel Cadot wrote on Sat, 02 January 2010 13:56
External table is far faster than PL/SQL including bulk one.
Besides how do you want to use bulk collect?

Regards
Michel

thanks mr. michel cadot, using the bulk collect load the data from external table to normal table.
Re: How to load the data from flat file? -- urgent please [message #437270 is a reply to message #437252] Sat, 02 January 2010 18:17 Go to previous messageGo to next message
arunn
Messages: 52
Registered: September 2008
Location: usa
Member
is there any other technique to load the data from flat file to db?
Re: How to load the data from flat file? -- urgent please [message #437271 is a reply to message #437270] Sat, 02 January 2010 18:55 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
arunn wrote on Sun, 03 January 2010 01:17
is there any other technique to load the data from flat file to db?


Millions of them. Depends on what your exact problem is with the ones already mentioned.
Re: How to load the data from flat file? -- urgent please [message #437272 is a reply to message #437252] Sat, 02 January 2010 19:08 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
arunn wrote on Sat, 02 January 2010 20:20
I have 1 million data in the flat files.
A megabyte of data? million of files? million of directories with billions of huge files in every of them?
arunn wrote on Sat, 02 January 2010 20:20
which one is best in the below?
Like Michel wrote - define "best". If there was simply "the best" then there would be only a single method available because there is no point in keeping "not the best".
arunn wrote on Sat, 02 January 2010 20:20
a. bulk collect
b. external table
c. sql loader
d. split the data using unix scripting.
e. split the data using pl/sql( when i use the commit? like for every 25000 thousand rows )

sqlloader is above-average. In direct mode it is very fast. You might think that some of the options are exactly the same as sqlloader (because this is the same mechanism internally).
If you want to use unix scripts to split the data then it is a good solution for common repeated task (which is repeated often). But there is a price - this is not really that fast.
pl/sql is programming language. You usually use it to do something on the server because there is no need to download whole database to your application. This is why it was created and why it is called programming language sql.
As for the commit - it is just the finalization of your transaction. Best is to use rarely - but it might be risky and you might run out of resources in which case it might be tragic.arunn wrote on Sat, 02 January 2010 20:20
or else other technique?
There are many ways. No one can help you without proper information.
arunn wrote on Sat, 02 January 2010 20:20
-- urgent please
I hope that "--" was just a pl/sql comment... if not then you should start with reading the posting rules. Usually I simply ignore posts with "urgent" (or similar) word in their abstract.
Re: How to load the data from flat file? -- urgent please [message #437280 is a reply to message #437269] Sun, 03 January 2010 01:34 Go to previous message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
thanks mr. michel cadot, using the bulk collect load the data from external table to normal table.

insert select is surely faster.

Quote:
is there any other technique to load the data from flat file to db?

Sure, here's one: use sed or awk to transform each line of your file to an insert statement and then use sqlplus to execute it, now a SQL script.
Is this fast, surely not, the fastest way has already been given.

Regards
Michel
Previous Topic: dbms_jobs
Next Topic: inserting date
Goto Forum:
  


Current Time: Mon Nov 04 12:31:25 CST 2024