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/aio_logistic_predlv_ext.dat.prod

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/aio_logistic_predlv_ext.dat.prod
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     (
    PICK_NO VARCHAR2(15),
    IMEI VARCHAR(15),
.
.
.
    COMMENT_ VARCHAR(200),
    ORDER_DATE DATE
    )
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;

PS:

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
Tony








Endlich Sicherheit bei Computerabstuerzen - Phoenix Recover Pro. 
http://internetzugang.tiscali.de/internet_services/detail_rec.php

--
http://www.freelists.org/webpage/oracle-l


Other related posts: