Home » SQL & PL/SQL » SQL & PL/SQL » Inserting multiple records with CLOB using select (Oracle PL/SQL 11G)
Inserting multiple records with CLOB using select [message #598253] Fri, 11 October 2013 15:18 Go to next message
Messages: 1
Registered: October 2013
Location: Boston
Junior Member

I'm updating a large piece of legacy code that does the following type of insert:

INSERT INTO foo_temp
(id, varchar2_column)
SELECT id, varchar2_column
FROM foo;

We're changing varchar2_column to clob_column to accommodate text entries > 4000 characters.
So I want to change the insert statement to something like:

INSERT INTO foo_temp
(id, clob_column)
SELECT id, clob_column
FROM foo;

This doesn't work, since clob_column stores the location of each text entry, rather than
the actual content. But is there some way that I can achieve the insert with one
call to a select statement, or do I need to select each individual record in foo,
open the clob_column value, read it into a local variable and then write the content
to the matching record in foo_temp?

Thanks in advance!
Re: Inserting multiple records with CLOB using select [message #598255 is a reply to message #598253] Fri, 11 October 2013 15:30 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3123
Registered: May 2013
Location: World Wide on the Web
Senior Member
See if this asktom link helps http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:388196216305

icon1.gif  Re: Inserting multiple records with CLOB using select [message #598268 is a reply to message #598253] Sat, 12 October 2013 01:18 Go to previous message
Michel Cadot
Messages: 65154
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Database Application Developer's Guide - Large Objects

Previous Topic: How to multiplied count in SQL
Next Topic: Writing Turkey characters to text file
Goto Forum:

Current Time: Wed Aug 23 07:00:24 CDT 2017

Total time taken to generate the page: 0.05824 seconds