Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> odd problem with external tables. (10.2.0.3 on Red Hat)

odd problem with external tables. (10.2.0.3 on Red Hat)

From: Stephens, Chris <chris_stephens_at_admworld.com>
Date: Fri, 9 Mar 2007 12:48:29 -0600
Message-ID: <7070047601C21A4CB387D50AD3661F6E06546B54@050EXCHANGE.research.na.admworld.com>


i've got an external table defined as

CREATE TABLE CMAP_WH.OIL_WORLD_DATA ( MEASURE_NAME VARCHAR2(50 BYTE), GEOGRAPHY_NAME VARCHAR2(50 BYTE), COMMODITY_NAME VARCHAR2(50 BYTE), IGNORE VARCHAR2(50 BYTE), YEAR VARCHAR2(4 BYTE), QUARTER VARCHAR2(4 BYTE), MEASURE_VALUE NUMBER, STATUS VARCHAR2(50 BYTE)
)

ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY OIL_WORLD_LOC ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252 STRING SIZES ARE IN BYTES BADFILE OIL_WORLD_LOC:'OIL_WOLD_BAD.bad'

DISCARDFILE OIL_WORLD_LOC:'OIL_WORL_DISCARD.DIS' LOGFILE OIL_WORLD_LOC:'OIL_WORLD_LOG.log'

FIELDS TERMINATED BY '\t'

OPTIONALLY ENCLOSED BY '' AND '' NOTRIM (

MEASURE_NAME CHAR, GEOGRAPHY_NAME CHAR, COMMODITY_NAME CHAR, IGNORE CHAR, YEAR CHAR, QUARTER CHAR, MEASURE_VALUE CHAR, STATUS CHAR
)

)

LOCATION ( OIL_WORLD_LOC:'oil_world.txt'

)

)

REJECT LIMIT UNLIMITED; The ignore column corresponds to a blank field (tabtab) in the source file. When I go to access the data, errors are generated in the logfile of the form:

KUP-04021: field formatting error for field STATUS

KUP-04023: field start is after end of record

KUP-04101: record 1 rejected in file
/rd2/flat_files/oil_world/oil_world.txt

If i go out and manually insert a '*' into that column it works fine.

I've also tried MISSING FIELD VALUES ARE NULL. That produces errors in the logfile like :

ORA-12899: value too large for column QUARTER (actual: 6, maximum: 4) error processing column QUARTER in row 3 for datafile /rd2/flat_files/oil_world/
oil_world.txt
ORA-12899: value too large for column QUARTER (actual: 6, maximum: 4)

..but that's b.s. if you look below, the quarter lengths are 4 characters. I've got to be doing something retarded here that i'm just not seeing on a friday afternoon.

Anybody know why oracle isn't just skipping over the blank column?

Here are the ascii values of the first few rows. The bolded character is what I had to manually insert:

Typ=1 Len=40:
83,84,79,67,75,9,69,85,45,50,53,9,83,111,121,98,101,97,110,115,9,42,9,50 ,48,48,53,9,49,48,51,9,49,55,51,50,46,56,9,42

Typ=1 Len=40:
83,84,79,67,75,9,69,85,45,50,53,9,83,111,121,98,101,97,110,115,9,42,9,50 ,48,48,53,9,52,48,54,9,49,51,56,56,46,54,9,42

Typ=1 Len=40:
83,84,79,67,75,9,69,85,45,50,53,9,83,111,121,98,101,97,110,115,9,42,9,50 ,48,48,53,9,55,48,57,9,49,52,56,53,46,50,9,42

REJECT LIMIT 1; CONFIDENTIALITY NOTICE:
        This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 09 2007 - 12:48:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US