RE: Getting a Clob into a Long

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 19 Oct 2004 17:27:05 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87AC1@irvmbxw02>

The original question was about taking the value in a LOB column and putting it in a LONG. If the value is more than 32K in size, there is no way to do that using PL/SQL or SQL in Oracle. A program will need to be written using OCI calls (or an interface to OCI calls - which I presume is what Perl does) or Pro*C or another similar language.

It is possible in PL/SQL to RETRIEVE all the data in a LONG column, even if the data is more than 32K, but it is not possible to INSERT into a LONG column when the data is more than 32K.

-----Original Message-----
Jared Still

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. =20

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=20 be quite a bit easier than PL/SQL, as it avoids the=20 intermediary table.

On Mon, 18 Oct 2004 18:16:28 -0400, Bobak, Mark

>=20

> Sorry to say, I think you're looking at implementing a Pro*C solution.

>=20

> I know it can be done via Pro*C. I don't think you'll find any
> direct SQL or PL/SQL solution.

>=20
>=20

> -----Original Message-----
> bonnergj_at_songs.sce.com

>=20

> ...
> I have a work table that has a clob on it as one of the columns. I
need =3D
> 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
=3D
> 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 =3D
> 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.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 19 2004 - 19:22:40 CDT