External Table problem

  • From: <adolph.tony@xxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 30 Aug 2006 09:58:07 +0200 (CEST)

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 

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 

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

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


Other related posts: