Re: Problem with TEXT in Datatype LONG

A copy of this was sent to Joerg Reiner <reiner_at_gsf.de> (if that email address didn't require changing) On Thu, 29 Oct 1998 10:07:49 +0100, you wrote:

>Hello,
>
>I think I have a really basic problem about
>the ORACLE Data Types specially LONG.
>
>I want to import ASCII Text into ORACLE LONG-Fields.
>Is there any limitation about the Text Length? As I new there
>can be up to 2^31 Bytes in there.
>But the IMPORT-Tool give an length error message like the following:
>
>--> Record 3: Rejected - Error on table "UMLSADMIN"."MRDEF1", column DEF.
>--> Field in data file exceeded maximum specified length
>

I'm going to assume that you mean the LOADER tool (not import) since that looks more like a loader error message then an import error message...

So, say I have:

SQL> create table test ( a int, b long, c int );

Table created.

and then I create a control file for loader that looks like:

LOAD DATA
INFILE *
INTO TABLE TEST
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (a, b, c)
BEGINDATA
1,123456789012345678901234567890123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678901234567890,2

That will create a log file with an error message:

Record 1: Rejected - Error on table TEST, column B. Field in data file exceeds maximum length

Thats becuase the default datatype in sqlldr is CHAR and the default length for that datatype is 255. If i modify the ctl file to have

LOAD DATA
INFILE *
INTO TABLE TEST
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (a, b char(100000), c)
BEGINDATA
1,123456789012345678901234567890123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678901234567890123456789012345678 90123456789012345678901234567890123456789012345678901234567890,2

• note the char 100000, and then run sqlldr as such:

$ sqlldr user/pass controlfilename.ctl bindsize=110000

it will load the data.....

>The physical line length at this record is just 546 Characters.
>
>So my question: What goes wrong there.
>
>Hope Anybody could help me
>
>Thanks a lot, Joerg
 

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.