Retrieve 20000 records from text file

[Cap2010]

hi,

How much time will it take to import 20000 or more records from a text file to Oracle database (Client to server).

Want to retrieve fast and than check all the 20000 or more records is correct.

How to import such large records in Oracle at a one go.

Captain

 

[SantaMufasa]

Captain,

There are several ways to import 20,000 records of ASCII data into your table. You said, however, "...from a text file to Oracle database (Client to server)." If you are looking for speed, it will be much faster to ftp your text file to the server machine rather than read your data from client to server.

Regardless of where you begin, I recommend you read up on SQL*Loader, Oracle's utility built expressly for flat-file loading. You flat ASCII data must either be delimited or fixed-length fields.

If you need help, update your specific request here. Let us know your results,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 04:07 (05Dec04) UTC (aka "GMT" and "Zulu"),
@ 21:07 (04Dec04) Mountain Time

 

[Cap2010]

Mufasa,

File is fixed-length fields, process followed want to follow is
1) Upload all records in Oracle
2) Check whether records id exist with main id
3) .... rest other checking after uploading file.


Can we directly ftp to oracle database ?

Captain

 

[SantaMufasa]

Captain,

No, you cannot ftp directly into Oracle. What I was saying was in response to your suggestion that you planned to conduct this "import" from your client machine to the server machine. I suggested that you ftp the file from your client machine to the server machine, then, once the fixed-length-field flat file is on the server, you use SQL*Loader to read in the contents of the flat file into your Oracle table. SQL*Loader has specific support for reading flat ASCII files, having fixed-length columns, into an Oracle table.

SQL*Loader is the utility you want to use.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 08:23 (05Dec04) UTC (aka "GMT" and "Zulu"),
@ 01:23 (05Dec04) Mountain Time

 

[Cap2010]

Hi,

Currently using SQL loader on server. Problem is each user logs into the server and loads, requirement is each user from their own PC logs in thru the application and loads. Can't load SQL loader in all the PC as it is chargeable for installing SQL loader in each PC.

Captain