On Dec 1, 7:18 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> 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;
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
Received on Mon Dec 03 2007 - 09:43:31 CST
