Re: Convert CSV data held in a CLOB into columns

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 09 Dec 2007 15:33:03 -0800
Message-ID: <1197243180.661199@bubbleator.drizzle.com>

jeremy wrote:
> In article <1197157179.144099_at_bubbleator.drizzle.com>, DA Morgan says...

>> jeremy wrote:
>>> In article <1197136923.818884_at_bubbleator.drizzle.com>, DA Morgan says...
>>>>> 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.
>>> - is there a way to tell Oracle 
>>> "look at the content of this CLOB and treat it like it is an external 
>>> table containing CSV content".
>> This makes no sense.
>>

> It seems you have missed the point.
>
> Re-read the original post and you will see what I am trying to achieve.
> A CLOB's content may have structure - e.g. XML - I am trying to
> determine if there is a method by which the content of a CLOB, when
> known to be in CSV format, can be easily procesed in pl/sql.

Clearly the answer is yes. You can efficiently use DBMS_LOB.SUBSTR and DBMS_LOB.INSTR to parse it up rather efficiently.

What you then do with the pieces is up to you.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sun Dec 09 2007 - 17:33:03 CST