November 13, 2015 - 10:38 am UTC
Reviewer: Steven Grzbielok from Ratingen. Germany
thanks for your answer. Exactly what I wanted.
February 24, 2016 - 7:07 pm UTC
Reviewer: Scott from CT
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?
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"
March 22, 2016 - 1:59 pm UTC
Reviewer: A reader
Thanks a lot!!!
August 22, 2017 - 4:46 pm UTC
ins.sql looks like:
select /*insert*/* from table_name
Insert into "table_name" (PROP_KEY,LNG,PROP_VALUE) values ('key123','en',(CLOB));
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 220.127.116.11 Build 15.21, I was able to do the following ....
December 11, 2017 - 6:19 pm UTC
Reviewer: Athadu from US
- 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.
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);
l varchar2(32000) := rpad('aaaaaa', 2000, 'x');
insert into t values (1, l);
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
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.
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
April 01, 2020 - 8:20 am UTC
Reviewer: Avinash Raj from India
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'))
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s".
Do I miss anything?
April 02, 2020 - 3:24 am UTC
We need to see a describe command for "REGP"."Profile"