| Variable length field exceeds maximum length. [message #279010] |
Tue, 06 November 2007 10:35  |
shashi_ys@yahoo.co.in
Messages: 5
Registered: November 2007
|
Junior Member |
|
|
Hi All,
I am trying to load some signature ascii data from load input file. so i wrote the code like below in my controal file to load that to database. For SIGN_IMAGE column it was mentioned as RAW(2000) in oracle db . The below is worked fine when i tried in window and oracle 8i environment.
SIG_TYPE POSITION(23:23) CHAR,
SIGN_IMAGE POSITION(24:1977) VARRAW(2000)
NULLIF SIGN_IMAGE=BLANKS,
SIGN_IMAGE1 POSITION(1978:3930) VARRAW(2000)
NULLIF SIGN_IMAGE1=BLANKS
But when i ported the same thing to solaris and oracle 10g environment. The above code is giving below error when SQL Loder loading the data.
The error is like : Variable length field exceeds maximum length.
and before this error it is printing this message also like
value used for ROWS parameter changed from 64 to 25
But here i am giving a lenght of 1954 only with including 2 bytes length of the string.
Could any one tell me what is exactly the problem? i am not able sort out the issue.
Thanks,
Shashi
|
|
|
|
|
|
| Re: Variable length field exceeds maximum length. [message #279054 is a reply to message #279014] |
Tue, 06 November 2007 14:29  |
shashi_ys@yahoo.co.in
Messages: 5
Registered: November 2007
|
Junior Member |
|
|
It is sure that problem is coming from RAW because it is specially mentioning the column name while displaying the error.
For each raw cloumn positions in data file first 2 bytes represents string low length and high length like for SIGN_IMAGE positions 24-25, for SIGN_IMAGE1 positions 1978-1979 etc..
log file issue
value used for ROWS parameter changed from 64 to 31
Record 1: Rejected - Error on table SIG.SIGC, column SIGN_IMAGE.
Variable length field exceeds maximum length.
controal file :
LOAD DATA
INFILE 'sigc.sig'
BADFILE 'load_7.bad'
DISCARDFILE 'load_7.dis'
APPEND
INTO TABLE sig.sigc
(
TRANS_NO POSITION(1:15) CHAR,
TDR_ID POSITION(16:18) INTEGER EXTERNAL,
DVCE_TYPE POSITION(19:20) CHAR,
CAP_CD POSITION(21:22) CHAR,
SIG_TYPE POSITION(23:23) CHAR,
SIGN_IMAGE POSITION(24:1977) VARRAW(2000)
NULLIF SIGN_IMAGE=BLANKS,
SIGN_IMAGE1 POSITION(1978:3930) VARRAW(2000)
NULLIF SIGN_IMAGE1=BLANKS,
SIGN_IMAGE2 POSITION(3931:5883) VARRAW(2000)
NULLIF SIGN_IMAGE2=BLANKS,
SIGN_IMAGE3 POSITION(5884) VARRAW(2000)
NULLIF SIGN_IMAGE3=BLANKS
)
DATA line
0001200000002480050600?¸a4GW ' & ' & _" _! _" ^# ^# _" ^# _" ^# _! _" & ' & ' & !%_!5 & !% & !U & !% ' V X _R _! _Z _Q _" _"__1 _"_ ( _!__2_ (_ 0 & 'pa@NS _! _"_^3 _" ^# _! ^$ ^# _! ^$ _! _" _!paDC' ' P V _ P ^ & '_ ._ 0__*_ 0 ^# _" _! _" _! ^T X _Q! H PpaTI$_ (_ 0_ (_ 0__2_ (^_9 _"_^3 _" _! _R _Z P! H P! H & ^ W !% & & ' _! _" ^# _" _! _" _" _Q _" X P &pa]KA_ 0^ 8_ 0_ (__2_ 0_ ( X _! P P _Z! H _Q P _Z _R _!__2 _!^ 8__2_ (^ 8_ 6_ 0 ' Ppe"F; P ^ P & P ' & '_ 0_ 0__)_ 0 _"__) _Z _" ^S _Y! @!_Jpe3K/ _" _" _! _"__1 ^$ _! _" P _! ' ^ ' & & !% ' & & ' &_ ( _" X P P _Y!_J _R!_I!_J ^S _R _Y _"_ (__1^ 8_ 0^ 8_ 7^ 8_ ( & _" X PpeDF# & & _" _" _! _" ^# _" PpeDKW '_ 0 V P X _R X P _Q _"_ 0_ 0_ ( _" _! _Z P _Q _Z!_J _Q _R _! _" _"_ 0^ 8__1_ /_ 0_ 6_ ( & ' X V! H W! H! N! H! @ X P _Z _! _" _! ^$ _! ^# _" ^# _R _! ' & ' !$ !% !% !%_!5 !$ ' & W & & P X _" _R _! _Z _! _" _"__)_ 0__*_ 0 Ppi#J+ X_ (_ 0_ (^ @_ (__1__*__1 _R _Z _Q X P P! H X ' & V_ 7 &_ ( '_ 6 _" Ppi-KG_ 0_ 7__)_ 0_ /__1^ 8_ 0 _R _Z P _Q _Z _Q _Z _" _Q__2__) _"__2_ (^ @_ (_ 6 V ' ^ P P ' X V X P P X P! H P ^ P X W & '_ 6_ (__2_ (__1__2 _! _" _R _Q! H _Z P Ppi@H& _Q P X _" V X ' P_ 6_ (__2__)_ 0__2 _" _! _R _Q X P! H P _ & ' &_ . '_ 6 P XpiTN# &_ (^ 8__2^ 8_ 0__)__2_^3 _" _! ^# _R _" _! _R _!__2 _"_ 0_ (_ 6_ ._ 7_ 6_ / & & ' X P _Q! H!_J Ppi\KW _" _R X _Q _Z _Q _R _Zpm&O9_ . _"__2 _!_^3_^, ^#_^3 ^# ^# ^$ _Qpm(JS_ 0_ ( _!_ 0 _"__) _" _" _! _Z P X P V _ &_ ._ 7_ 0 P Ppm1ID_ 0_ 0__* _!__2 _" _Q _" X _! P P X W X !% P & & W & _" _! _" _" _! ^# _" X V ' !] & V ' & _" _!__2 _" _! _" P & ' & V _ V _! @ ^! H!_B Xpa44V &_ 0__*__1__*__2 ^# ^# ^# _R _!!_J X P V _ !% & !% & '_ . &_ 7_ 6 Ppa<7Q_ 0_ ._ 0 _"__* _! _" ^# _" ]\ ^$ ^S ^# _Z _! W &paE3A _"_ 0 &_ 0_ ( _"_ 0__) _"_^3 _" ^S ^# _Z P!_I W !\ ' "S !%\!% !$ !%_!5 & '_ ( ^# _" ^# _! ^$ ^[ _! ^T ^# _YpaW7)_ 0_ ( _"__1 _" ^# _" ^# _! ^T X _Q! H P! H Xpe 7Q_ 0_ ( X _" _Q ^# _R ^# ^# ^\ ^# ^S _" _! _Z V !% !U !] !T !% !% !% &_ 0 _"__) ^$ _! _" ^# _" ^[ P _Y! @ X W ^ ' &_ ._ 7_ . '_ 6_ 0 & W P ^pe37/ ' _! _" _" _! ^# ^$ _! ^[ ^$ ^# _! ^$ _!pe-3A _" X _Q X! @ X P! H P ' X & V ' & ' !$ ' & _" _! ^$ _! ^# ^$ ^[ ^# ^# ^S _" _" V !% ^ W !\ W P ^ P X P _R _! _Z ^# _R ^# ^# _Z _! _RpeE/G X P X P W X P &_ 7_ (__1_ 0__*__1_^, _! ^# ^\ _Q _Z!_A!_J! H P! HpeU3A &_ 0_ 0__* _" _! _Z _Q _" ^S _" ^# _" _! _"__1 _"__2_ (_ 0pe]5D _" _! _" _" _! ^# ^\ ^# ^S _" ^# X & W & !% !%_"3_!-_ 6_!- &_ 7_ 0 & P V _ P X! @ X P X _Q ^$ _! _" _!__2_ ( _"_ 0_ (_ 0 Vpi&0> _! ^$ _Q _" _! ^$ _! _" & _ !$ ' !% !$ ' !U V ^ P! H P!_J X!_B Xpi65<_ ( _"_ 0 _! _"__2 _!__* _! ^$ ^# _Y _R _R X V ^ !U ' & &_ 7_ . '_ 0_ ( X VpiA1D_ 0^ 8 _"_ 0 _! ^$ _!_^+ ^$ ^[ ^S ^# ^[ _R _R_ 6 &_!5_!- !%_!5 !$ !% !U !% ^ W P X P _Y P _" _! _" ^#__2_ (__1_ (_ 0 ' &piF0> P X P! H P! H! H V __ . &_ 0_ (^ @_ (__2__*__1 _" _Q _Z _R! H!_I! H PpiT3! P &__2 _" _! _R _! _" _" ^# _! ' & ' !$ W & ' ^ V! H P! H Xpm$;! & _" _" _! ^# ^$ _! ^# _" _" ^#pm'3/ _"_ 0 _" _! _R _! _Z P _R! H! H P! N X V W & _Z _! ^$ ^S ^# ]] ^# ]% ]U_^3 _!__*^_:^_A^ 8^ 8^ ?^ 8^ @p
|
|
|
|
|
|
|
|
| Re: Variable length field exceeds maximum length. [message #279724 is a reply to message #279265] |
Fri, 09 November 2007 14:14 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
It sounds like a characterset conversion issue. What should be a siggle byte could be being translated into muti-byte fot the target database characterset. If Oracle knows your file is supposed to be binary, there should be no characterset conversion. However...
1.) if you FTP your data around, make sure that chr(10) hasn't been replaced by chr(13)+chr(10) (make sure you ftp in binary mode) especially when Windows machines are involved.
2.) if you set your client characterset to exactly match your database characterset, then no characterset conversion will take place (which is what you want). Set NLS_LANG in the sqlldr environment to match the database.
example:
select * from v$nls_parameters where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET AL32UTF8
Corresponding NLS_LANG is determined as follows:
NLS_LANG=<NLS_LANGUAGE>_<NLS_TERRITORY>.<NLS_CHARACTERSET>
i.e.
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
