External Tables which are delimited

From: <art_at_unsu.com>
Date: Tue, 11 Dec 2007 07:37:59 -0800 (PST)
Message-ID: <62737a09-3bd5-4642-b281-2a0cdcc61081@i29g2000prf.googlegroups.com>

Hi,

I have a file which I want to turn into an external table. The file has about 25 fields in it, but I am only interested in 3 of them. I know I can use the POSITION command to select different positions in the file, but each field is delimited by a TAB: (0x'09') I think.....

Anyhow, the file would look something like this:

A       A.N     AGILENT TECH    3       51      12/07/07
39.00   38.01   32.00   08/22/2004      5       00846U101
ACS     ACS.N   AFFILIATED COMP 3       43      12/07/07
56.00   44.78   56.00   10/05/2001      1

So, the fields are not the same length, but they are delitied by a tab.

So, how can I use both the POSITION and FIELDS TERMINATED BY? I tried this script, and it did not work:

CREATE TABLE TARGET_PRICE_EST_EXT
(

  TICKER       VARCHAR2(5 BYTE),
  REPORT_DATE  VARCHAR2(8 BYTE),
  ESTIMATE     VARCHAR2(8 BYTE)

)
ORGANIZATION EXTERNAL
  ( TYPE ORACLE_LOADER

     DEFAULT DIRECTORY INDATA_DIRECTORY
     ACCESS PARAMETERS
       ( RECORDS DELIMITED BY NEWLINE

    FIELDS TERMINATED BY 0x'09'
    LRTRIM (
"TICKER" 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; Anything I am doing wrong? I did not find any examples of this.....

Thank you. Received on Tue Dec 11 2007 - 09:37:59 CST