Hi,

I am having trouble with my external table.  We are running Oracle 10g
R2.

Basically we have a weird record that looks like this:

NNRNT  AARON RENTS INCFIN-LEASING COS 34  8  2 6. 4. 1. 1.  .  .
56 1.5.1.415+ 3012 2018 2014 .66  .31314  7 .20  5.8  1446Sp  0.29
-39Mo  1.181.13  Dc 2/19  0.35  0.32  6  0.53  0.51  312/06  1.48
1.44  .01 6  1.66  .06 7 23.3    -3  15 14.34+

As you can see, I need to do a fixed position definition.  My table
definition is below.  What I try and select from  the table, I get
this error:

KUP-04101: record 97 rejected in file /u01/NI00/prod/data/indata/
external_tables/W_GIDE.DAT
KUP-04021: field formatting error for field F2_EPS_EST
KUP-04024: field end is before field start

I do not understand this at all.  There are some NULLS or Spaces
within the values.  That is why I am using fixed format.   Here is my
table definition.   Can anyone offer some help?

Thank you.

CREATE TABLE ESTIMATE_RECOM_EXT (
   M_TICKER      VARCHAR2(5),
   F2_EPS_EST    VARCHAR2(40),
   F1_EPS_EST    VARCHAR2(40),
   Q0_EPS_EST    VARCHAR2(40),
   Q0_EPS_ACT    VARCHAR2(40),
   Q0_EPS_SURP   VARCHAR2(40),
   RANK          VARCHAR2(3),
   STRONG_BUY    VARCHAR2(2),
   MODERATE_BUY  VARCHAR2(2),
   HOLD          VARCHAR2(2),
   MODERATE_SELL VARCHAR2(2),
   STRONG_SELL   VARCHAR2(2),
   CURRENT_AVG   VARCHAR2(3),
   LAST_AVG      VARCHAR2(3))
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY INDATA_DIRECTORY
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS LRTRIM (
      M_TICKER       POSITION(3:5),
      F2_EPS_EST     POSITION(216:6),
      F1_EPS_EST     POSITION(203:6),
      Q0_EPS_EST     POSITION(168:6),
      Q0_EPS_ACT     POSITION(130:6),
      Q0_EPS_SURP    POSITION(136:3),
      RANK           POSITION(44:3),
      STRONG_BUY     POSITION(50:2),
      MODERATE_BUY   POSITION(53:2),
      HOLD           POSITION(56:2),
      MODERATE_SELL  POSITION(59:2),
      STRONG_SELL    POSITION(62:2),
      CURRENT_AVG    POSITION(73:3),
      LAST_AVG       POSITION(77:3))
    )
  LOCATION ('W_GIDE.DAT')
)
REJECT LIMIT 100;

ame...@iwc.net schrieb:
<quote>
*end*

The end parameter indicates the absolute byte or character offset into
the record for the last byte of the field. If start is specified along
with end, then end cannot be less than start. If * or increment is
specified along with end, and the start evaluates to an offset larger
than the end for a particular record, then that record will be rejected.
</quote>

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/et_par...

Best regards

Maxim

On Dec 1, 7:18 pm, "ame...@iwc.net" <ame...@iwc.net> wrote:

This isn't, apparently, a 'problem' with external tables, it's a
problem with  you knowing your data and it's layout in your source
file.  I'd be checking each line for length and for data positioning;
line 97 (for one) doesn't meet your fixed-length record criteria as
it's too short.

David Fitzjarrell