Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: Getting a Clob into a Long

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Tue, 19 Oct 2004 07:52:32 -0400
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE07A7F9B4@exchsen0a1ma>


Gary,

Just an idea - you can have multiple pl/sql long datatypes and concatenate them together in the insert statement like this:

CREATE TABLE tomtest(col1 LONG);

DECLARE
 l_col1 LONG;
 l_col2 LONG;
BEGIN
  INSERT INTO tomtest(col1) VALUES(l_col1 || l_col2); END; So you could load up the multiple local columns and perform the insert.

Would this help?

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

From: bonnergj_at_songs.sce.com [mailto: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 Received on Tue Oct 19 2004 - 06:48:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US