Re: Convert CSV data held in a CLOB into columns

In article <93697bc7-3f2d-4230-992e-db710f35ae57 @y43g2000hsy.googlegroups.com>, Mark D Powell says...
> On Dec 7, 5:05 pm, jeremy <jeremy0..._at_gmail.com> wrote:
> > Oracle 10gR2
> >
> > Would like to be able to support upload of a CSV file and then populate
> > a "staging" table with its contents.
> >

>
> What is the primary reason you want to be able to parse the column
> names and then receive the data in one file? For a target why can't
> missing data values just be missing as in two delimiters in a row with
> no value between them?

Just looking to see how flexible we can be - the design isn't completed yet.
>
> One idea to consider might be to define a generic load table
> (external) where every column is varchar2(4000) and then just use sql
> to convert each column to the proper target table type.
>
> If you open the select on the external table as a cursor you could
> select the first line and use the column names (and maybe target table
> name) to build a dynamic sql insert statement string with bind
> variables. Then for cursor rows 2 to EOF you just fire the dynamic
> insert.
>

This was definitely one approach.

> Unless the data source does a good job formatting and verifying the
> data handling errors can be a problem with overly generic approaches.
>
> You should also consider just defining a sqlldr control file for each
> expected external file that you are going to want to load into a table
> or holding (processing) table.

WOuld rather keep the entire thing within pl/sql.
>
> Perl and Pro*C could be used at the OS level to parse the file and
> submit the records to the database via an insert if you need to
> control the timing of the task execution outside the db. The external
> table would be good when you want to trigger the load from within a db
> task.

Definitely do not want to use these tools.

Can a CLOB be referred to as an external table? If so then we can keep the entire process IN the database and cut out steps like establishing directory objects, placing CSV file in corresponding o/s directory etc.

-- 
jeremy