Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Getting a Clob into a Long

Re: Getting a Clob into a Long

From: Jared Still <>
Date: Mon, 18 Oct 2004 18:03:29 -0700
Message-ID: <>

Ah well, it can be done directly with 100% PL/SQL.

You just need to convert the long via the to_log() function.

It does require that you use a global temporary table as an intermediary, but it can be done.

I'm citing from memory, so you best look at the to_lob() function in the SQL reference manual.

Of course, you could do it with Perl and DBI. It would be quite a bit easier than PL/SQL, as it avoids the intermediary table.


On Mon, 18 Oct 2004 18:16:28 -0400, Bobak, Mark <> wrote:
> Gary,
> Sorry to say, I think you're looking at implementing a Pro*C solution.
> I know it can be done via Pro*C. I don't think you'll find any
> direct SQL or PL/SQL solution.
> -Mark
> -----Original Message-----
> From:
> []On Behalf Of
> Sent: Monday, October 18, 2004 5:44 PM
> To:
> Subject: Getting a Clob into a Long
> I have a bit of a dilemma here. We have a production table that has a =
> long
> datatype on it and for reasons
> I won't get into cannot be converted to a clob for several more months.
> I have a work table that has a clob on it as one of the columns. I need =
> to
> copy the row from the clob table into the table with the long.
> This is all done in a stored procedure that gets called when needed. I =
> go
> through a loop that parses the
> clob into varchar2 chunks and then concatenates them into a plsql long
> datatype. Problem is a plsql long
> datatype can only be 32k. Some of the clobs are longer and thus the
> problem.
> There are built-in functions to convert/load from a long to a clob but =
> I
> haven't been able to find a way of going the other way.
> Longs on tables can hold 2gig but a plsql long can only handle 32k.
> Does anyone out there have any ideas how I could do this?
> I have tried every possible scenario I can think of with no success.
> Thanks for listening, Gary
> --
> --

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Received on Mon Oct 18 2004 - 19:59:12 CDT

Original text of this message