From oracle-l-bounce@freelists.org Tue Oct 19 19:22:40 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i9K0MeR26199 for ; Tue, 19 Oct 2004 19:22:40 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i9K0MeI26194 for ; Tue, 19 Oct 2004 19:22:40 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AB36472C3AD; Tue, 19 Oct 2004 19:28:49 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01783-59; Tue, 19 Oct 2004 19:28:49 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 22E4D72C521; Tue, 19 Oct 2004 19:28:48 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Subject: RE: Getting a Clob into a Long Date: Tue, 19 Oct 2004 17:27:05 -0700 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Getting a Clob into a Long Thread-Index: AcS1d7L5yOmpaIeCQLSSFZHREMrNFwAwzT9Q From: "Jacques Kilchoer" To: X-OriginalArrivalTime: 20 Oct 2004 00:27:06.0102 (UTC) FILETIME=[87363960:01C4B63B] X-archive-position: 11289 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Jacques.Kilchoer@quest.com Precedence: normal Reply-To: Jacques.Kilchoer@quest.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@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