HELP....importing large text file

Toad for Oracle
1

I am running TOAD 10.6

I am trying to load a text file to populate a table I have just created. the
Size of the file is 309,930 KB.

I tried to import data and got an out of memory error.
Is there another way to populate the table?

Sparkie

And always remember…when life hands you Lemons, ask for tequila and salt and
call me over

2

Use SQL Loader for large files. It may take a little longer to set up, but that
time will be saved (and then some) when the process runs.

3

Do try the SQLLDR wizard. May take you a bit longer at first to get set up
right, but the loader FLIES compared to the import process. Import process is
absolutely lovely for smaller files.

Dan

Daniel B Madvig
Computer Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323
image001.jpeg

4

For those who want to get the benefits of sql loader without the syntax issues
– consider external tables. You could create an external table to point to
the data file and then simply issue a insert as select with parallel hints to
make it fast J
image001.jpeg

5

SEE ATTACHMENT
image001.jpeg

6

SEE ATTACHMENT
image001.jpeg

7

I can’t assist from the Toad side… I still use the command interface for
this task. Perhaps if I outline what I do, that might help you resolve what
you’re running in to.

I tend to use a separate control file and run file as follows.

Run file contents:

Sqlldr control=$HOME/.ctl, log=$HOME/.log, bad=$HOME/.log, \

Discard=$HOME/.dis, data=$HOME/.dat;

The is a “your choice” option. For example, I use a generic
load_file.ctl, load_file.dat, etc for my one-off kind of things (that way I only
need modify the control file and ensure the data file is in place).

Control file contents:

LOAD DATA TRUNCATE

INTO TABLE

FIELDS TERMINATED BY ‘!’
quotes

TRAILING NULLCOLS

(,

,

sequence)

Meanings:

Termination value can be literally any character in the character set you use.
In the above example, I wanted to load each line into a single column so I
picked a character that did not show up anywhere in the file.

Trailing allows you to populate a field value that does not otherwise appear in
your data file. In the above example, it increments an index value so you can
order the data in the exact order it appeared in the data file if that is a
requirement.

To be able to specify a fixed file, you’d have the following changes in the
control file:

Remove termination line. Replace the column definitions with:

( position (001:004),

position (005:063))

Hope this helps!

Roger S.
image001.jpeg