Using tab character as separator on external tables

From: Arto Viitanen <arto.viitanen_at_csc.fi>
Date: Fri, 10 Jun 2005 13:55:30 +0300
Message-ID: <42a971a1$0$16551$ba624cd0@newsread.funet.fi>

In bioinformatics tabular separated files (TSV) are quite common. It occured to me to try to set up Oracle external table (Oracle 10g) from such a file (GO terms and IDs file from http://www.geneontology.org/doc/GO.terms_and_ids). I created a directory object and used following statement:

create table ext_go_Terms (

     id char(10),
     term varchar2(200),
     term_kind char(1)

   )
   organization external (

     type oracle_loader
     default directory TableTest
     access parameters (
      records delimited by newline
      badfile 'GO.bad'
      discardfile 'Go.discarded'
      logfile 'GO.log'
      skip 7
      fields terminated by '09' notrim (
        id char(10),
        term char(200),
        term_kind char(1)
      )

    )
    location ('GO.terms_and_ids_sharp')
   ) reject limit unlimited;

Since there is no symbol for TAB character like for newline, I used hexnumber 09. However, trying to refer the table (SELECT count(*) from ext_go_terms) gave nothing (or count as 0) and error log said:

   Fields in Data Source:

     ID                              CHAR (10)
       Terminated by "09"
     TERM                            CHAR (200)
       Terminated by "09"
     TERM_KIND                       CHAR (1)
       Terminated by "09"
KUP-04021: field formatting error for field ID

KUP-04026: field too long for datatype
KUP-04101: record 8 rejected in file .../GO.terms_and_ids

But, when I replaced all TABs with # and changed to TERMINATED TO '#', the table worked as it shold.

Since TSV is so common format, I'd like to use external tables. So, how can I use them ?

--

Arto Viitanen
CSC Ltd
Finland

Received on Fri Jun 10 2005 - 05:55:30 CDT