Re: How TO: External Tables

On Jan 11, 1:16 pm, "ame..._at_iwc.net" <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???

If your file is tab delimited, you should not be using positional information in defining columns and defining your delimiter as 0x'09'

If the data is not tab delimited, then your position arguments are incorrect. The first digit is the starting character of your desired string and the second digit is the last character you desire. (i.e. (15:16) is 2 characters, (15:2) wont work)

If you need to skip columns and the file is tab delimited, you still have to define them in your control file: id char,
dummy1 char,
report_date char,
dummy2 char... Received on Fri Jan 11 2008 - 19:29:23 CST