Home » RDBMS Server » Server Utilities » Variable length field exceeds maximum length.
Variable length field exceeds maximum length. [message #279010] Tue, 06 November 2007 10:35 Go to next message
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 #279014 is a reply to message #279010] Tue, 06 November 2007 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure the problem comes from RAW and not CHAR column?
Post:
- character sets (client and database)
- actual control file
- data line that failed (not all RAW data of course)
- log file

Use code tags.

Regards
Michel
Re: Variable length field exceeds maximum length. [message #279054 is a reply to message #279014] Tue, 06 November 2007 14:29 Go to previous messageGo to next message
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 #279061 is a reply to message #279010] Tue, 06 November 2007 15:10 Go to previous messageGo to next message
shashi_ys@yahoo.co.in
Messages: 5
Registered: November 2007
Junior Member
And one more thing, here both client and server are running on same machine.
Re: Variable length field exceeds maximum length. [message #279265 is a reply to message #279061] Wed, 07 November 2007 09:33 Go to previous messageGo to next message
shashi_ys@yahoo.co.in
Messages: 5
Registered: November 2007
Junior Member
Does Any one have any idea? please help me out....
Re: Variable length field exceeds maximum length. [message #279724 is a reply to message #279265] Fri, 09 November 2007 14:14 Go to previous messageGo to next message
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
Re: Variable length field exceeds maximum length. [message #279726 is a reply to message #279724] Fri, 09 November 2007 15:27 Go to previous message
shashi_ys@yahoo.co.in
Messages: 5
Registered: November 2007
Junior Member
Hi Andrew,

i used raw insted of varraw in controal file. it got worked out and completed it. Thank you very much for information. Have a nice weekend.

Thanks,
Shashi

[Updated on: Fri, 09 November 2007 15:27]

Report message to a moderator

Previous Topic: Sql Loader issue
Next Topic: Exports
Goto Forum:
  


Current Time: Sat Dec 03 13:56:55 CST 2016

Total time taken to generate the page: 0.20969 seconds