Re: Getting a Clob into a Long

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.

Jared

On Mon, 18 Oct 2004 18:16:28 -0400, Bobak, Mark <mark.bobak_at_il.proquest.com> 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: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of
> bonnergj_at_songs.sce.com
> Sent: Monday, October 18, 2004 5:44 PM
> To: oracle-l_at_freelists.org
> 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
>
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l