Tools for Oracle database - SQLLOADER

Author

SQLLOADER - wierd result

blindsey

2005-09-06, 8:23 pm

I'm getting a weird error from SQLLOADER. It's rejecting records due
to a field being too long. The field is VARCHAR2(4000). This is a
comma-delimited file.

I've looked at the .BAD file and the fields in question are nowhere
near 4000 bytes. In fact the whole .BAD file, with four rejected
records, is only 3800 bytes long.

The common denominator is that each rejected field has a long string of
blanks. I thought that maybe a string a CRLFs or TABs might be
interpreted as longer than one charater, but I checked using a hex
editor and these are indeed blanks (hex 20).

Anyone have any ideas?

DA Morgan

2005-09-06, 8:23 pm

blindsey wrote:
> I'm getting a weird error from SQLLOADER. It's rejecting records due
> to a field being too long. The field is VARCHAR2(4000). This is a
> comma-delimited file.
>
> I've looked at the .BAD file and the fields in question are nowhere
> near 4000 bytes. In fact the whole .BAD file, with four rejected
> records, is only 3800 bytes long.
>
> The common denominator is that each rejected field has a long string of
> blanks. I thought that maybe a string a CRLFs or TABs might be
> interpreted as longer than one charater, but I checked using a hex
> editor and these are indeed blanks (hex 20).
>
> Anyone have any ideas?

Have you verified that each line ends with a CR/LF?

Try also using the TRUNC function in your SQL*Loader control file.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Frank van Bortel

2005-09-07, 7:23 am

blindsey wrote:
> I'm getting a weird error from SQLLOADER. It's rejecting records due
> to a field being too long. The field is VARCHAR2(4000). This is a
> comma-delimited file.
>
> I've looked at the .BAD file and the fields in question are nowhere
> near 4000 bytes. In fact the whole .BAD file, with four rejected
> records, is only 3800 bytes long.
>
> The common denominator is that each rejected field has a long string of
> blanks. I thought that maybe a string a CRLFs or TABs might be
> interpreted as longer than one charater, but I checked using a hex
> editor and these are indeed blanks (hex 20).
>
> Anyone have any ideas?
>

Multi character character set in use on the database?

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Volker Pohlers

2005-09-07, 11:23 am

blindsey schrieb:
> I'm getting a weird error from SQLLOADER. It's rejecting records due
> to a field being too long. The field is VARCHAR2(4000). This is a
> comma-delimited file.
>
> I've looked at the .BAD file and the fields in question are nowhere
> near 4000 bytes. In fact the whole .BAD file, with four rejected
> records, is only 3800 bytes long.
>
> The common denominator is that each rejected field has a long string of
> blanks. I thought that maybe a string a CRLFs or TABs might be
> interpreted as longer than one charater, but I checked using a hex
> editor and these are indeed blanks (hex 20).
>
> Anyone have any ideas?
>
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

Maxim Demenko

2005-09-07, 11:23 am

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).

Best regards

Maxim

Volker Pohlers

2005-09-14, 9:23 am

Maxim Demenko schrieb:

I found your answer tody, so I must reply:

> Volker Pohlers schrieb:
>
>
>
> 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

Maxim Demenko

2005-09-14, 11:23 am

Volker Pohlers schrieb:
> Maxim Demenko schrieb:
>
> I found your answer tody, so I must reply:
>
>
>
> 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
$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 @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@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@ORA92>

Best regards

Maxim

Volker Pohlers

2005-09-15, 9:23 am

Hi Maxim,

you simple case works well, but if you use once more longe data field,
f.i. with

#!/usr/bin/perl
$str="A very long string " x 200;
$len=length($str);
for ($i=1;$i<=10;$i++){
print ($i, ",",$str,",",$len , ",",$str,",",$len , "\n");
}

it doesnt work any more.
I'll get folling log:

SQL*Loader: Release 9.2.0.1.0 - Production on Do Sep 15 15:56:17 2005

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

Kontrolldatei: long.ctl
Für gesamte Eingabe angegebener Zeichensatz WE8ISO8859P1.

Datendatei: long.csv
Datei fehlerhafter Sätze:long.bad
Datei für zurückgewiesene Sätze: nichts spezifiziert

(alle Discards zulassen)

Zu ladende Anzahl: ALL
Zu überspringende Anzahl: 1
Zulässige Fehler: 50
Bind-Array: 64 Zeilen, maximal 256000 Bytes
Fortsetzung: nichts spezifiziert
Benutzer Pfad: Konventionell
Silent-Option: DISCARDS

Tabelle EXT_LONG, geladen wenn 1:1 != 0X1a(Zeichen '')
Insert-Option in Kraft für diese Tabelle: REPLACE
Option TRAILING NULLCOLS ist wirksam

Spaltenname Position Läng Term Eing Datentyp
------------------------------ ---------- ----- ---- ----
---------------------
F1 FIRST * , CHARACTER

NULL wenn F1 = BLANKS
SQL-Zeichenfolge für Spalte : "ltrim(rtrim(:F1))"
A_VERY_LONG_STRING_A
_VERY_LONG NEXT * , CHARACTER

NULL wenn A_VERY_LONG_STRING_A
_VERY_LONG = BLANKS
SQL-Zeichenfolge für Spalte :
" ltrim(rtrim(:A_VERY_
LONG_STRING_A_VERY_L
ONG))"
F4000 NEXT * , CHARACTER

NULL wenn F4000 = BLANKS
SQL-Zeichenfolge für Spalte : " ltrim(rtrim(:F4000))
"
A_VERY_LONG_STRING_A
_VERY_LON1 NEXT * , CHARACTER

NULL wenn A_VERY_LONG_STRING_A
_VERY_LON1 = BLANKS
SQL-Zeichenfolge für Spalte :
" ltrim(rtrim(:A_VERY_
LONG_STRING_A_VERY_L
ON1))"
F4001 NEXT * , CHARACTER

NULL wenn F4001 = BLANKS
SQL-Zeichenfolge für Spalte : " ltrim(rtrim(:F4001))
"

Satz 1: Abgelehnt - Fehler in Tabelle EXT_LONG, Spalte
A_VERY_LONG_STRING_A
_VERY_LONG.
Feld in Datendatei überschreitet Höchstlänge
Satz 2: Abgelehnt - Fehler in Tabelle EXT_LONG, Spalte
A_VERY_LONG_STRING_A
_VERY_LONG.
Feld in Datendatei überschreitet Höchstlänge
Satz 3: Abgelehnt - Fehler in Tabelle EXT_LONG, Spalte
A_VERY_LONG_STRING_A
_VERY_LONG.
Feld in Datendatei überschreitet Höchstlänge
Satz 4: Abgelehnt - Fehler in Tabelle EXT_LONG, Spalte
A_VERY_LONG_STRING_A
_VERY_LONG.
Feld in Datendatei überschreitet Höchstlänge
Satz 5: Abgelehnt - Fehler in Tabelle EXT_LONG, Spalte
A_VERY_LONG_STRING_A
_VERY_LONG.
Feld in Datendatei überschreitet Höchstlänge
Satz 6: Abgelehnt - Fehler in Tabelle EXT_LONG, Spalte
A_VERY_LONG_STRING_A
_VERY_LONG.
Feld in Datendatei überschreitet Höchstlänge
Satz 7: Abgelehnt - Fehler in Tabelle EXT_LONG, Spalte
A_VERY_LONG_STRING_A
_VERY_LONG.
Feld in Datendatei überschreitet Höchstlänge
Satz 8: Abgelehnt - Fehler in Tabelle EXT_LONG, Spalte
A_VERY_LONG_STRING_A
_VERY_LONG.
Feld in Datendatei überschreitet Höchstlänge
Satz 9: Abgelehnt - Fehler in Tabelle EXT_LONG, Spalte
A_VERY_LONG_STRING_A
_VERY_LONG.
Feld in Datendatei überschreitet Höchstlänge

Tabelle EXT_LONG:
0 Zeilen erfolgreich geladen.
9 Zeilen aufgrund von Datenfehlern nicht geladen.
0 Zeilen nicht geladen, da alle WHEN-Klauseln fehlerhaft waren.
0 Zeilen nicht geladen, da alle Felder NULL waren.

Zugewiesener Bereich für Bind-Array: 82560 Bytes (64 Zeilen)
Byte in Lese-Puffer: 1048576

Gesamtzahl der übersprungenen logischen Datensätze: 1
Gesamtzahl der gelesenen logischen Datensätze: 9
Gesamtzahl der abgelehnten logischen Datensätze: 9
Gesamtzahl der zurückgewiesenen logischen Datensätze: 0

Lauf begonnen am Do Sep 15 15:56:17 2005
Lauf beendet am Do Sep 15 15:56:18 2005

Abgelaufene Zeit: 00:00:00.39
CPU-Zeit: 00:00:00.11

Volker

Volker Pohlers

2005-09-15, 9:23 am

Hi Maxim,

I have found an error in our tools: we doesnt create the type and length
info in the ctl-files, so sqlldr will try only 255 bytes in case of
delimited data. In fixed length case, sqlloader use the full length
computed by posion.

Volker

Sponsored Links