Re: How to Load Mass Data into Oracle

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/05/07
Message-ID: <3553bd54.3060010@192.86.155.100>#1/1

A copy of this was sent to dolans_at_stripe.Colorado.EDU (Sean Dolan) (if that email address didn't require changing) On 7 May 98 13:26:18 GMT, you wrote:

>I have a large set of data in delimitted format that needs to be transferred to an Oracle table. I tried using SQL Loader but it doesn't support a field larger than 255 characters. Well, some of my data in the records go up to 2000 (ie memo field). How do I automate this transfer of data from this flat delimitted text file to the Oracle table.
>
>Much appreciated,
>Sean Dolan
>
>PS If you could email me to the address above, I would even more appreciate it.

If you have a table like:

SQL> create table large ( x varchar2(500), y varchar2(500) );

Table created.

And you use a control file such as:

LOAD DATA
INFILE *
REPLACE
INTO TABLE LARGE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(x      char(500),
 y      char(500)

)
BEGINDATA
01234567890123456789012345678901234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789012345678901234567890123456789 01234567890123456789,01234567890123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789

(notice the char(500), chars DEFAULT to 255 but can go much larger), you'll have no problems loading longer strings.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu May 07 1998 - 00:00:00 CDT