Re: Using tab character as separator on external tables

Arto Viitanen schrieb:
> 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

Your syntax is wrong, correct syntax for field terminators is not "09" but 0x'09'

Best regards

Maxim Received on Fri Jun 10 2005 - 06:23:44 CDT