Anyone Know External Tables?

From: <amerar_at_iwc.net>
Date: Sat, 1 Dec 2007 17:18:07 -0800 (PST)
Message-ID: <ca98b696-b5aa-4b97-8ca9-6c687fbf38d5@i29g2000prf.googlegroups.com>

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; Received on Sat Dec 01 2007 - 19:18:07 CST