Re: How TO: External Tables

On Jan 11, 8:29 pm, Vince <vinn..._at_yahoo.com> wrote:
> 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...- Hide quoted text -
>
> - Show quoted text -

amerar, Vince is correct in that your definition is defitely wrong. Either you have a fixed position input file or a delimited input file. You must define the correct delimiter tab instead of pipe, and you do have to account for the columns you do not want.

See the sqlldr section of the Utilities manual for control card examples.

Here is a complete sample load of a tab delimited file via an external table (though I do not skip any fields).

--
-- Alvin   Tolliver                1976
-- Kenneth Baer    M       1963
-- Mary    Dube    F       1973
--

UT1 > drop table loadtext2;

Table dropped.

UT1 > create table loadtext2 (
  2  	  c1   varchar2(12),
  3  	  c2   varchar2(12),
  4  	  c3   varchar2(01),
  5  	  c4   number
  6  	  ) organization external
  7  	 (type oracle_loader default directory markshome
  8  	 access parameters
  9  	 ( records delimited by newline
 10  	   badfile markshome:'bad_load'
 11  	   logfile markshome:'log_load'
 12  	   fields terminated by 0x'09'
 13  	   missing field values are null
 14  	  (
 15  	  c1,
 16  	  c2,
 17  	  c3,
 18  	  c4
 19  	  )
 20  	 )
 21  	location('textfile.txt')
 22  	)
 23  /

Table created.

UT1 > select * from loadtext2;

C1           C2           C
C4
------------ ------------ -
----------
Alvin        Tolliver
1976
Kenneth      Baer         M
1963
Mary         Dube         F
1973

UT1 > spool off


HTH -- Mark D Powell --