Importing large CSV to Oracle via TOAD

Question

I have a large CSV file (around 620,000,000 records) which I need to import into Oracle via Toad. It gives out of memory.

I have been able to split them into .xls files with single sheet in each. How can I upload data into a table from multiple xls files, or is there a better alternative?

Answer 1

The way is to use SQL*LOADER, and in TOAD there is an option Database->Import->SQL*LOADER wizard. One can directly specify the input file and the table in which records are to be inserted/appended.

A problem that may arise is the availability of sqlldr.exe when working on Oracle 10g Instant Client. For this, the user may download the Oracle Universal Installer, OUI, (the one for 11g will work too), and this provides sqlldr.exe. It needs to be added to the SQL*LOADER path in TOAD (View->Toad Options->Executables).

Finally, in case after installing the OUI there are problems starting TOAD (error related to NLS environment), create a system variable NLS_LANG with value AMERICAN_AMERICA.WE8ISO8859P1. This should fix TOAD connectivity issues.