Re: Loading data - SQL*LOADER or ???

Jared Still wrote:
>
> ebeth_at_universe.digex.net (Elizabeth Jones) wrote:
>
> >General Oracle question - do many Oracle users use sql*loader, and
> >if not, what other loading techniques are preferred? Is there an
> >advantage to using sql*loader over writing a sql script that contains
> >all your data and loading it thataways?
> >--
> >Elizabeth Jones .......................... ebeth_at_universe.digex.net
 

> >"Elves seldom give unguarded advice, for advice is a dangerous gift,
> >even from the wise to the wise, and all courses may run ill."
>
> A SQL script cannot read your files on your OS system. To do that
> you must use either SQL Loader ( which is very fast ) or write PROC*C
> programs to read the file, parse and load.
>
> Another option you may have is to use DBperl. I use SQL*LOADER and/or
> DBperl where appropriate. C takes too long to develop.

You can also do this:

Let's say you have an ASCII text file with the data to be loaded. Something like:

Smith	Bob	12345
Jones	Bill	90210
Niven	Larry	61616

If you're Unix, you can write a perl or awk script that converts it to this:

insert into <table> values (Smith, Bob, 12345);
insert into <table> values (Jones, Bill, 90210);
insert into <table> values (Niven, Larry, 61616);

Then, run the new file as a SQL sript from SQL*Plus. Make sure it does commits every so many records so your rollback segments don't explode. :)

-- 
==============================================================================
Jason Wells                 Tapestry Computing, Inc.       Voice: 314.344.0066
Senior Software Developer   Email:jason.wells_at_tapestry.com   Fax: 314.344.0990
==============================================================================