Re: Why is this external table not working???

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 11 Jan 2008 18:12:53 -0800
Message-ID: <1200103955.75440@bubbleator.drizzle.com>


amerar_at_iwc.net wrote:

> On Jan 11, 5:46 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> ame..._at_iwc.net wrote:

>>> This is driving me crazy!
>>> Ok, I have an external table that is tab delimited. I am not
>>> interested in all of the fields, jsut a few of them. My table
>>> definition is below. But, it is not working! Here is my definition
>>> and what happends when I query the data:
>>> CREATE TABLE TARGET_PRICE_EST_EXT
>>> (
>>> ID VARCHAR2(5),
>>> REPORT_DATE VARCHAR2(8),
>>> ESTIMATE VARCHAR2(8)
>>> )
>>> ORGANIZATION EXTERNAL
>>> ( TYPE ORACLE_LOADER
>>> DEFAULT DIRECTORY INDATA_DIRECTORY
>>> ACCESS PARAMETERS
>>> ( RECORDS DELIMITED BY NEWLINE
>>> FIELDS TERMINATED BY '|'
>>> MISSING FIELD VALUES ARE NULL (
>>> "ID" POSITION(1:5) CHAR(5),
>>> "REPORT_DATE" POSITION(49:8) CHAR(8),
>>> "ESTIMATE" POSITION(65:8) CHAR(8))
>>> )
>>> LOCATION (INDATA_DIRECTORY:'SnDailyUpload.txt')
>>> )
>>> REJECT LIMIT 50;
>>> ID REPORT_D ESTIMATE
>>> ----- -------- --------
>>> AAD A 7.20 11 5 00949
>>> JON A 6.71 47. /2005 5
>>> CCFG 0 8.20 0 7 5 0185
>>> EERT 3.80 3.8 2007 -99
>>> LLF A 82 10.00 007 3 03
>>> Any thoughts???
>> You have a choice ... you can either be positional or delimited but you
>> can't choose to be both.
>>
>> My recommendation would be tab delimited and then select what you want.
>> --
>> Daniel A. Morgan
>> Oracle Ace Director & Instructor
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -
> 
> 
> Can I forget about the whole delimiter thingy?  Can I say that a tab
> character is X amount of spaces, and then just use positions?

No.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Jan 11 2008 - 20:12:53 CST

Original text of this message