Re: sqlldr: maximum length
Date: Sat, 21 Jul 2001 21:53:33 GMT
Message-ID: <9gfnje02ioj_at_drn.newsguy.com>
In article <3B2AB577.1A4EC2E2_at_erols.com>, Joseph says...
>
>
>Hello.
> I have a problem with sqlldr that I can't figure out. I use sqlldr to
>load data from variable length text files I receive from a web site into
>several oracle tables existing on a Sun E10K, (Oracle version 8.0.4).
>The fields are delimited by '|'. In one of the tables there is a field
>called PURPOSE that I've defined as VARCHAR2(512). Up until today the
>largest entry for this field has been 234 characters.
>
>This morning the sqlldr routine did not load a record that contained a
>391 character field for PURPOSE. The error I received was:
>
> Record 4: Rejected - Error on table FORM_8871_TABLE, column
>PURPOSE.
> Field in data file exceeds maximum length
>
>The reference to "maximum length" has me confused. The PURPOSE field
>was defined as having up to 512 characters. I double-checked the
>table definition, I counted the characters in the field in the data file
>(391 characters), I checked for special characters in the file, I
>checked the data in the other fields in the data file. I can't see any
>problems.
the default type of a sqlldr input type is CHAR the default length is 255
if the type or length is different, you need to specify that in the control file. for example:
LOAD DATA
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,
DNAME,
LOC char(1000)
)
that permits deptno's and dnames upto 255 characters in length and a loc of 1000.
>
> I copied this record to another file and tried sqlldr again. The
>entire record length was 782 characters. It errored out. I started
>deleting characters from the PURPOSE field in the file to determine if I
>could load the record with a shorter value. When I had a maximum field
>length of 255 characters I was able to load the entire record.
>
> Just for the sake of trying I converted this record into an INSERT
>statement and it worked. Though this has given me a workaround for that
>table, (using 'awk' to create the INSERT statements from the data
>file), I'd really like to find out what is going on. I load a number
>of other tables using sqlldr and I'd rather not have to do something
>different for this one table and I'd rather not have to depend on a
>series of individual INSERT statements for future files.
>
> Is there a field length specification for sqlldr? I do not have any
>recent updated documentation. I've referenced an Oracle 7 Utilities
>User's Guide for SQLLOAD but other than BINDSIZE I cannot see anything I
>can try to change.
>
>I'm missing something here, hopefully something simple. Does anyone
>have any ideas?
>
>--
>
>+----------------------+
>| dabrdbaker_at_erols.com |
+----------------------+----------------------------------------+Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle Corp Received on Sat Jul 21 2001 - 23:53:33 CEST
>|"No one can go back and make a brand new start, my friend, but |
>| anyone can start from here and make a brand new end." |
>| - Anonymous |
>+---------------------------------------------------------------+
-- Thomas Kyte (tkyte_at_us.oracle.com) http://asktom.oracle.com/