Re: SQLLOADER - wierd result
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