SQL*Loader Error 523 on Win2K 8.1.7.0.0 - cause & possible solution
From: FC <flavio_at_tin.it>
Date: Fri, 10 Jan 2003 21:32:26 GMT
Message-ID: <KZGT9.17042$AA2.816234_at_news2.tin.it>
Hello folks,
[Quoted] today I had a couple of hours of pure entertainment trying to figure out what could cause the SQL*Loader Error 523. I have a text file with fixed length fields but the possibility of the last column to be truncated at a some point before its expected size. The control file defines a variable record-size file, with fixed column positions, terminated by CR LF ( X'0D0A'). It has been running for months without a hiss, until today. Suddenly, the program failed with error 523.
The error is somewhat misleading, because I spent time checking user authorizations, disk quotas and so on, until I finally realised that the reason must be somewhere else.
I changed the ROWS from 128 to 1 and the BINDSIZE/READSIZE to the maximum expected length of the record (including the expected CR LF) and the problem was gone.
Before that, I tried without success some alternative methods like adding TRAILING NULLCOLS and OPTIONALLY TERMINATED BY WHITESPACE. During one test I also noticed that when it arrives at the last record of the file, if the last column being loaded is shorter than its expected length and the last record terminator is absent, SQL*Loader adds an extra character of junk.
Date: Fri, 10 Jan 2003 21:32:26 GMT
Message-ID: <KZGT9.17042$AA2.816234_at_news2.tin.it>
Hello folks,
[Quoted] today I had a couple of hours of pure entertainment trying to figure out what could cause the SQL*Loader Error 523. I have a text file with fixed length fields but the possibility of the last column to be truncated at a some point before its expected size. The control file defines a variable record-size file, with fixed column positions, terminated by CR LF ( X'0D0A'). It has been running for months without a hiss, until today. Suddenly, the program failed with error 523.
The error is somewhat misleading, because I spent time checking user authorizations, disk quotas and so on, until I finally realised that the reason must be somewhere else.
I changed the ROWS from 128 to 1 and the BINDSIZE/READSIZE to the maximum expected length of the record (including the expected CR LF) and the problem was gone.
Before that, I tried without success some alternative methods like adding TRAILING NULLCOLS and OPTIONALLY TERMINATED BY WHITESPACE. During one test I also noticed that when it arrives at the last record of the file, if the last column being loaded is shorter than its expected length and the last record terminator is absent, SQL*Loader adds an extra character of junk.
Now, the question is, are these SQL*Loader bugs or what ?
Thanks,
Flavio
Received on Fri Jan 10 2003 - 22:32:26 CET