Anyone Know External Tables?
ame...@iwc.net
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;
Maxim Demenko
> Hi,
>
<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_params.htm#sthref1758
Best regards
Maxim
fitzj...@cox.net
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