RE: Getting a Clob into a Long
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 19 Oct 2004 17:27:05 -0700
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@xxxxxxxxxxxxx
>=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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
