Re: External Tables which are delimited

On Dec 11, 9:37 am, a..._at_unsu.com wrote:
> 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.

It's logically inconsistent to say that fields are terminated by some character, then try to define fields by fixed positions.

Define your table with columns to account for all of the fields in the file. You are only interested in three of them? So SELECT only on what you are interested in. No different from a normal table that has more columns than what you are interested in. Received on Tue Dec 11 2007 - 10:00:12 CST