Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: External Table problem

RE: External Table problem

From: Williams, Trevor <>
Date: Thu, 31 Aug 2006 08:46:35 +0800
Message-ID: <>

Did you consider pre-processing the file with dos2ux? Trevor

-----Original Message-----
[] On Behalf Of
Sent: Wednesday, 30 August 2006 3:58 PM
Subject: External Table problem

Hi All,

I have some builtin java code ftp-ing files from an NT to a HP server. The code
then loads a table as select * from external table (definition below).

The records are delimited by ^M (as they are created on the NT box).

Now to my problem.... *most* of the time everything works ok, but occasionally we get one of
two errors:

Error 1:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

Cause (as described in the log file which was added for debugging):

KUP-04021: field formatting error for field ORDER_DATE
KUP-04026: field too long for datatype
KUP-04101: record 5208 rejected in file 
/pkg/vdcfa/home/orafa/GDF/GDF16CT_ROOT/aio-external-tabl es/

When I check the source file (and bad file), I see that the last field ORDER_DATE, doesn't have
a terminating record delimiter (^M). BUT, when I check the other files that ran in ok, they
also are often missing the last delimiter. I don't have consistency here!

PS on Error1: I want all or nothing here, so want to reject all if there are any errors, hence
the reject limit 0.

Error 2:
No bad file created, instead an exception is raised, but in the log file I see:

KUP-04020: found record longer than buffer size supported, 524288, in /pkg/vdcfa/home/orafa/GDF/
GDF16CT_ROOT/aio-external-tables/ KUP-04053: record number 5208

Again this record (5208, the last one), is missing a terminating ^M.

I have found that increasing the READSIZE fixes the problem (in my development env), but for
how long? What combination of file size with missing ^M will cause the problem again?
I'd like to know for sure what causes the problem *and* if its possible to declare that the
last record can be missing the delimiter in the external table definition.

As an aside: the source files come from an external agency, and although the ^M *should* be
there, it often isn't. I'd like our code to be robust enough to deal with this.

External Table Definintion:

create table aio_logistic_predlv_ext (


organization external (
  type oracle_loader
  default directory aio_external_tables
  access parameters (
    records delimited by 0X'0d0a'
    badfile aio_external_tables:'_aio_logistic_predlv_ext.bad'     nodiscardfile
    logfile aio_external_tables:'_aio_logistic_predlv_ext.log'     >>> READSIZE 52428800 -- added to fix the problem<<<     characterset we8pc850
    fields terminated by '|'
    missing field values are null
    PICK_NO ,
    IMEI ,

    COMMENT_ ,
    ORDER_DATE char date_format date mask "DD.MM.YY"     )
  location ('aio_logistic_predlv_ext.dat') )
reject limit 0;


If I change the delimiter to NEWLINE then I get KUP-04021: field formatting error for field ORDER_DATE KUP-04026: field too long for datatype
i.e. the "^M"s make the field too big.

Thanks for any pointers

Endlich Sicherheit bei Computerabstuerzen - Phoenix Recover Pro.


Disclaimer.  This e-mail is private and confidential. If you are not the intended recipient, please advise us by return e-mail immediately, and delete the e-mail and any attachments without using or disclosing the contents in any way. The views expressed in this e-mail are those of the author, and do not represent those of this company unless this is clearly indicated. You should scan this e-mail and any attachments for viruses. This company accepts no liability for any direct or indirect damage or loss resulting from the use of any attachments to this e-mail.
Received on Wed Aug 30 2006 - 19:46:35 CDT

Original text of this message