Re: SQLLOADER - wierd result

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Wed, 14 Sep 2005 16:58:06 +0200
Message-ID: <43283a91$0$27547$9b4e6d93_at_newsread4.arcor-online.net>


Volker Pohlers schrieb:
> Maxim Demenko schrieb:
>
> I found your answer tody, so I must reply:
>
>

>> Volker Pohlers schrieb:
>>
>>> with simple csv files, sql loader supports only fields with a maximum 
>>> length of 255 bytes.
>>>
>>> You must transfer your file into fixed length format. Here you can 
>>> use full 4000 characters.
>>>
>>> Volker
>>
>>
>>
>> That is not correct.
>> 255 is the default length for CHAR columns, which can of course be 
>> overwritten by specifying the desired column width in the control file 
>> ( with respect for both, delimited or fixed length, data).
>>

>
> That is not correct.
> Even if you declare a column as varchar2(4000), the sqlloader internally
> uses in case of delimited data only 255 chars. You will get an
> unassigned error message as "string too long" without an oracle error
> number. Please, try it out.
>
> Volker
>

Not sure what do you mean with "declare a column as varchar2(4000)" - it is surely the requirement ( if you want to load a rows in that length ), but is not sufficient, you must also specify the correct length in the control file as i stated before.
Here is a simple testcase.

%cat long.pl
#!/usr/bin/perl
[Quoted] $str="A very long string " x 200;
$len=length($str);
for ($i=1;$i<=10;$i++){

   print ($i, ",",$str,",",$len ,"\n");
}

%chmod u+x long.pl

%./long.pl >long.dat

%cat long.ctl
load data
into table long_table
fields terminated by ","
(id,
long_test char(4000),
long_length terminated by whitespace)

%cat long.sql

CREATE TABLE long_table(ID NUMBER,long_test VARCHAR2(4000),long_length NUMBER);
exit

%sqlplus -s scott/tiger _at_long

Table created.

%sqlldr userid=scott/tiger data=long.dat control=long.ctl

SQL*Loader: Release 9.2.0.6.0 - Production on Wed Sep 14 16:47:09 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 10

%sqlplus scott/tiger

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Sep 14 16:48:42 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production

scott_at_ORA92> select id,length(long_test) from long_table;

         ID LENGTH(LONG_TEST)

---------- -----------------
          4              4000
          5              4000
          6              4000
          7              4000
          8              4000
          9              4000
         10              4000
          1              4000
          2              4000
          3              4000

10 rows selected.

scott_at_ORA92>

Best regards

Maxim Received on Wed Sep 14 2005 - 16:58:06 CEST

Original text of this message