Skip to Main Content
  • Questions

  • Exporting a table that contains a CLOB column

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Steven.

Asked: November 13, 2015 - 8:26 am UTC

Last updated: May 23, 2022 - 10:35 am UTC

Version: 12c

Viewed 100K+ times! This question is

You Asked

Hi Ask-Tom-team,
What is the recommended way for exporting a table that contains a CLOB column to a SQL script? The standard feature of the SQL Developer only exports all columns that are not CLOBs.
I want to export the table to a SQL script for including it in the installation of an APEX plugin.

Thank you. Best regards
Steven

and Chris said...

As stated in the docs, SQL developer only supports this for loader or PDF exports:

For CLOB data, exporting is supported only if the format is loader (SQL*Loader) or pdf (PDF). Some export types export only a subset of the string followed by an ellipsis (...).


http://docs.oracle.com/cd/E55747_01/appdev.41/e55591/dialogs.htm#sthref828

It sounds like you want to have insert statements for your release scripts. In which case you can spool the data to a file. If you put the comment /*insert*/ in the script, SQL Dev will create insert statements for you:

drop table t purge;
create table t (x int, c clob);
declare 
  l varchar2(32000) := lpad('aaaaaa', 5000, 'x');
begin
  insert into t values (1, l);
end;
/

set long 100000
set lines 1000
spool c:\temp\ins.sql
select /*insert*/* from t;
spool off


ins.sql will then look like:

SQL> select /*insert*/* from t;

REM INSERTING into t
SET DEFINE OFF;
Insert into "t" (X,C) values (1,TO_CLOB('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
|| TO_CLOB('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
|| TO_CLOB('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
|| TO_CLOB('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
|| TO_CLOB('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
|| TO_CLOB('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
|| TO_CLOB('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
|| TO_CLOB('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
|| TO_CLOB('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
|| TO_CLOB('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxaaaaaa'));

Elapsed: 00:00:00.488
SQL> spool off

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perfect solution

Steven Grzbielok, November 13, 2015 - 10:38 am UTC

Hi Chris,
thanks for your answer. Exactly what I wanted.

Best regards
Steven

No joy

Scott, February 24, 2016 - 7:07 pm UTC

We are having the exact same issue. Though I was unable to duplicate the aforementioned example ... the /*insert*/ had no impact on the SELECT statement. Was this part of a larger method? Or some other flag that needed to be set?

Oracle 12c
Chris Saxon
February 25, 2016 - 3:49 am UTC

The /*insert*/ is a SQL Developer facility, so you need to use it within that tool using "Run Script" (not "Run Statement") and the output should appear in the "Script Output"

Cheers,
Connor

IT WORKS!!!

A reader, March 22, 2016 - 1:59 pm UTC

Thanks a lot!!!

not working

KDPV, August 22, 2017 - 4:46 pm UTC

ins.sql looks like:

select /*insert*/* from table_name
spool off


Script output:
Insert into "table_name" (PROP_KEY,LNG,PROP_VALUE) values ('key123','en',(CLOB));

Chris Saxon
August 24, 2017 - 1:01 pm UTC

Which version of SQL Developer are you using?

What's your complete script (including create table + inserts)?

With SQL Developer 4.0.2.15 Build 15.21, I was able to do the following ....

Athadu, December 11, 2017 - 6:19 pm UTC

- Used the query: SELECT * from TABLE;
- Ran the query
- Right-click on the 'tabular' Query Result window.
- Select Export
- Format: Insert
- Save As: Single File

- Finish the remaining wizard steps.

The out file will have INSERT statements, including the CLOB column value.
Chris Saxon
December 12, 2017 - 9:39 am UTC

Sorry, but I'm not able to reproduce this in the latest version (17.3.1).

Following those steps with a table created, populated and queried like so:

drop table t purge;
create table t (x int, c clob);
declare 
  l varchar2(32000) := rpad('aaaaaa', 2000, 'x');
begin
  insert into t values (1, l);
end;
/

select * from t;


Gives the following export file:

REM INSERTING into EXPORT_TABLE
SET DEFINE OFF;
Insert into EXPORT_TABLE (X) values (1);


And the docs still state:

For CLOB data, exporting is supported only if the format is loader (SQL*Loader) or pdf (PDF). Some export types export only a subset of the string followed by an ellipsis (...).

https://docs.oracle.com/database/sql-developer-17.3/DMDUG/data-modeler-dialogs.htm#f1_idedexportobjects_html

Could you provide the create table, inserts and select you used to get clob data in your insert export?

Great trick, what else can we do with SQLDeveloper

Wayne, January 13, 2020 - 3:33 pm UTC

This is a great trick. Never seen it before. What else can we do besides insert? I'd love to have a version that does a merge. Where is the /*insert*/ hint (is that the right term?) documented.

Thanks for all the great info on Ask Tom.
Connor McDonald
January 14, 2020 - 2:37 am UTC

/*insert*/ is not a real database hint - it is understood by SQL Developer and SQLcl only.

You can do "json", "xml", "csv".

Check out the "set sqlformat" command.

Generated script throws error

Avinash Raj, April 01, 2020 - 8:20 am UTC

This script generates what is needed but, the same Output is not working and throwing error when I ran it on another DB instance:

Generated Script (There are more lines for a single record):

SET DEFINE OFF;
Insert into "REGP"."Profile" (ID,FIRST_NAME,LAST_NAME,SPEAKER_PIC,SPEAKER_BIO) values (1,'Anthony','Smith',TO_BLOB(HEXTORAW('626567696E2030207575656E636F64652E6275660D0A6C505B5F23462C2E5F505A2020242449263234382020302420202024'))
|| TO_BLOB(HEXTORAW('2020302020505B5F2346502342402958202230382724412C322531283224413836253134362621403525314035254134380D'))
|| TO_BLOB(HEXTORAW('0A6C255140362551403725513C362551403F2A2220382652343B253134412C3224452A324C4E2B42583727532C582C525457'))
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s".


Do I miss anything?
Connor McDonald
April 02, 2020 - 3:24 am UTC

We need to see a describe command for "REGP"."Profile"

A reader, May 19, 2022 - 2:58 pm UTC

"It .... WORKS !!!!" way-slick answer. This site is best-of-breed.
Connor McDonald
May 23, 2022 - 3:44 am UTC

Thank you :-)

String concatenation is too long

I'm plicit conversion, May 23, 2022 - 8:23 am UTC

TO_BLOB(HEXTORAW('626567696E2030207575656E636F64652E6275660D0A6C505B5F23462C2E5F505A2020242449263234382020302420202024'))
|| TO_BLOB(HEXTORAW('2020302020505B5F2346502342402958202230382724412C322531283224413836253134362621403525314035254134380D'))
|| TO_BLOB(HEXTORAW('0A6C255140362551403725513C362551403F2A2220382652343B253134412C3224452A324C4E2B42583727532C582C525457'))

Concatenation || is not defined for a BLOB.
Chris Saxon
May 23, 2022 - 10:35 am UTC

You can use dbms_lob.append if you need to concatenate blobs
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here