Re: External table field options

On 29 May 2005 19:41:59 -0700, "Barbara Boehmer" <baboehme_at_hotmail.com> wrote:

>Well, it isn't a csv (comma separated value) file, since it is
>separated by semicolons, but that really doesn't matter. You can trim
>the zeroes from in front of the addrcode, but why not make a number
>column instead?

I think I will use Sql*loader because of the possibility to use sql functions for column data.

The reason why I originally started to investigate external tables was to have a common structure for data from slightly different source and the data could have been read very easily from a scheduled dbms_job. After the data had been read into the specified external table, a common pl/sql would then convert it to the actual tables and I could skip creating slightly different code for the data conversion.

Well, it can be done using sql*loader + cron script /scheduled batch file almost as easily.

I really wonder why the option of using an sql function to convert the field is not supported by Oracle External Tables as the engine behind external tables has so much in common with Oracle Sql*Loader.

>scott_at_ORA92> CREATE OR REPLACE FUNCTION my_to_date
> 2 (p_date IN VARCHAR2)
> 3 RETURN DATE
> 4 AS
> 5 BEGIN
> 6 RETURN TO_DATE (p_date, 'DDMMYYYY');
> 7 EXCEPTION
> 8 WHEN OTHERS THEN
> 9 RETURN NULL;
> 10 END my_to_date;
> 11 /

I think that reading in the date using the external tables (or sql*loader) can be done just specifying the date format such as in my example:

         addrdate DATE "DDMMYYYY" NULLIF addrdate='00000000' (the reason why the date was NULL in the sample was that the date value was read into streetno field).

Thank you for your response. It was very helpful! Received on Tue May 31 2005 - 00:09:24 CDT