Re: External table field options

From: foo <chrisval_at_bigpond.com.au>
Date: 29 May 2005 21:21:27 -0700
Message-ID: <1117426887.828726.49690@g44g2000cwa.googlegroups.com>

Kirmo Uusitalo wrote:
> Hi,
>
> Given this csv file:
> 0101;Fox Street 20 A;27052005
> 00102;42nd Street 20921;25022005
> 00105;Fox Street 21;00000000
>
> I wish to read its contents into Oracle 9i external table.
> I have created the following sql statement to create the table but it
> needs a few fixes.

[snip]

If you take Sybrand' advice and use a little of your own imagination, then you might come up with something like this:

create or replace function GetAddrCode( addrcode in varchar2 ) return varchar2 as
begin

   return to_char( to_number( addrcode ) ); EXCEPTION
   when others then
   return null;
end GetAddrCode;
/

You could of course have performed the coersion in place, but having a function allows you to write neater code in larger contexts, ime and imo.

Hope this help's.

Cheers,
Chris Val Received on Sun May 29 2005 - 23:21:27 CDT