Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Convert CSV data held in a CLOB into columns

Re: Convert CSV data held in a CLOB into columns

From: DA Morgan <>
Date: Sat, 08 Dec 2007 10:02:05 -0800
Message-ID: <>

jeremy wrote:

> In article <93697bc7-3f2d-4230-992e-db710f35ae57
>>, Mark D Powell says...

>> On Dec 7, 5:05 pm, jeremy <> 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. 

In theory an external table should be able to handle anything that SQL*Loader can handle. They are essentially the same technology.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sat Dec 08 2007 - 12:02:05 CST

Original text of this message