SQL*Loader and LONG fields - Part 2 - Help

Well, I'd posted previously about needing to load large character data into Oracle using SQL*Loader and trying to maintain my carriage returns. I was able to export my data with tags to specifiy where the carriage returns were suppose to go and then wrote a PL/SQL statement to go back and replace these tags with carriage returns once the data was in the tables. Works great when I can get the data into the table, but unfortunately I've still got the problem following problem with some tables:

SQL*Loader log file:
Field in data file exceeds maximum length

I've seen some other posts on this same problem, but I haven't seen an answer to them. I've tried playing with BINDSIZE and READSIZE with no success, so I'll try to provide as much info as I can and hope someone can point out where I'm going wrong:

Database: Oracle8i

Platform: Sun

Table:
create table TBLCOMPANY (

    COMPANYID INTEGER,
    COMPANYNAME VARCHAR2(75),

    ADDRESS                 VARCHAR2(100),
    CITY                         VARCHAR2(50),
    STATE                      VARCHAR2(50),
    ZIP                            VARCHAR2(50),
    EMAIL                      VARCHAR2(50),
    PHONE                    VARCHAR2(50),
    FAX                          VARCHAR2(50),
    DATECREATED      DATE,
    DATEUPDATED      DATE,
    WEBPAGE               VARCHAR2(120),
    PORTFOLIO           VARCHAR2(50),
    ZIP3                         VARCHAR2(3),

Control File:
OPTIONS (READSIZE=1000000, BINDSIZE=1000000, ROWS=1) LOAD DATA
INFILE 'E:\DBConvert\Load\Data\tblTopStories.dat' BADFILE 'E:\DBConvert\Load\Bad\tblTopStories.bad' CONTINUEIF LAST = '#'
INTO TABLE tblTopStories
FIELDS TERMINATED BY '**FLD**'
(

    STORYID,
    DATECREATED date "MM/DD/YYYY hh24:mi:ss",     DATEMODIFIED date "MM/DD/YYYY hh24:mi:ss",     TITLE,
    SOURCE,
    ISONLINE,
    ARCHIVED,
    STORYORDER,
    BODY
)

My datafile looks something like this:
19**FLD**12/10/1999 14:28:30**FLD**12/10/1999 15:11:03**FLD**My Title**FLD**My Source**FLD**-1**FLD**-1**FLD****FLD**And here the text begins.....to##LF##
the line-feed tag and continues on this next line##LF## and keeps going for a lot of line##LF## ...
...
Until finally we reach the end of the story**FLD**

Now, I admit, I don't fully understand how to use READSIZE and BINDSIZE, those are just the values I ended up with after hours of frustration. I do know that my record with the longest BODY is only around 8000 characters, so I thought that a READSIZE and BINDSIZE of 1000000 would be enough (obviously not).

As I said, the above works when the data in the BODY field is not very long, but not when it grows large.

I've loaded this table previously with ODBC, but to do so for the eight or so tables that have LONG fields took just too long (about 24 hours).

So:
Is there a change I can make to my existing approach that will successfully load my data?

Or

Is there another approach I should use instead with SQL*Loader for this type of a load?

Appreciate any suggestions as I'm out of ideas.

Thanks,
Michael Received on Sat Mar 04 2000 - 01:59:02 CST