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
cdavis@hsph.harvard.edu
Messages: 1
Registered: October 2013
Location: Boston
Junior Member
Hi,

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: 1850
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

Regards,
Lalit
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: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Database Application Developer's Guide - Large Objects

Previous Topic: Performance with Materialized view fast refresh
Next Topic: while running a Stored procedure getting error
Goto Forum:
  


Current Time: Mon Jul 28 21:39:04 CDT 2014

Total time taken to generate the page: 0.14260 seconds