Re: Convert CSV data held in a CLOB into columns

From: Galen Boyer <galen_boyer_at_yahoo.com>
Date: 9 Dec 2007 11:34:01 -0600
Message-ID: <ubq8z4mkh.fsf@rcn.com>

On Sun, 9 Dec 2007, jeremy0505_at_gmail.com wrote:
> In article <uzlwk5sum.fsf_at_rcn.com>, Galen Boyer says...

>> On Sat, 8 Dec 2007, jeremy0505_at_gmail.com wrote:
>> 
>> > Alternatively, might we take the CLOB content, write it out via
>> > UTL_FILE then refer to it as an external table?
>> >
>> > As I said, I would certainly like to be able to do all the work
>> > without any depdency on referencing files on the o/s.
>> 
>> You most certainly could do this, but you would have to reference
>> files on the OS, but maybe, you don't mind referencing files on the
>> OS through PL/SQL?
>> 

>
> If it's necessary then we'll do it. The app can control where the
> files are written, how they are named etc.
>

>> But, it sounds as though the files are different, ie, you would need
>> a dynamic process to get them in, get the first record, create an
>> external table from it and then load the file, or something like
>> that?  If this is the case, I don't get the final mapping of the
>> data.  Lets say you dynamically create an external table to reference
>> a file, each time you recieve a new one.  Those columns would mean
>> something?  Where do you map those columns to your final data-model?
>> 
>> I'd think you would have a set format for your CSV files, define
>> external tables before hand, and the only thing you would do at
>> runtime is accept a clob and write it out to a particular filename in
>> the particular directory and then select from the external table.
>> 

>
> This is along the lines I was thinking. The ability to make it
> "dynamic" in terms of the columns provided in the CSV is not such an
> issue - the CSV would be of a particular type - e.g. PERSON DATA - and
> sure we have a fixed data model for that in the application. We could
> allow the user to define a spreadsheet with the only the columns they
> wish to include and, as long as there is a corresponding column in the
> target table, then that column's data would be loaded. Of course we
> would consider data type, size etc. to ensure compliance before
> loading into final app table. We could also provide a log table of any
> data issues arising, columns that couldn't be mapped etc.
>
> Fot total flexibility (say where we are loading data from a 3rd party
> system where we have no control over the column layout), we could
> allow a mapping table to transalte source columns to target columns.
>
> It may sound as though we are trying to re-invent sqlloader

Well, an external table resolves down to sqlloader calls, so you could say oracle did the same. I say that Oracle put a SQL-based interface on sqlloader and that is a very good thing.

> - and in a very limited way I guess this is true. But it is important
> to have the complete process managed within the application. - no
> going off to the server to run sqlloader or ftp an excel file in.

Well, PL/SQL writing a file isn't "going off to the server". Its referencing an Oracle directory object. Have the process accept the file, code up the reciept of that, marshall it to a directory into a file matching the name in the external table and then kick off a select clause.

I find that much more easily managed all within the database than ftp'n, kicking off sqlloader and ...

Basically, do any of your shell operations within PL/SQL.

-- 
Galen Boyer

Received on Sun Dec 09 2007 - 11:34:01 CST