Export and insert CLOB data containing SQL statements with SQL Developer

Question

I have a table with a CLOB column containing SQL code. Now I want to transfer the content from the developer database into the productive one. I use the following script to export the content of the dev table:

set long 100000
set lines 1000
spool d:\export.sql
select /*insert*/* from myTable;
spool off

However, the import into the prod table is not working due to ' characters in the SQL code. A generated insert statement looks like this:

insert into myTable (id, name, sql) 
values (1, 'John', 'select * /* this is a hint */ 
                    from table1 
                    where attr1 = 'hi,you' and attr2 = 'me, too')

How can I insert this CLOB, or how do I export it in a better way?

thatjeffsmith · Accepted Answer · 2018-05-09 14:36:03Z

1

I'd use Data Pump if it's available.

If not, I'd use SQL*Loader.

What you can do, is use SQL Developer to unload your table to a SQL*Loader setup, each CLOB will be written to a file, and they can be loaded then w/o issues like what you're seeing.

I wrote this here for how to do this with BLOBS, but would be the same process.

The output will be all the files you need to move your table over to the new system, the control file, the data stream, and all the LOBS.

Once you have your files, you will need to make sure you have an Oracle Client installed, or have the full Instant Client.

This will give you access to SQL*Loader.

It's a command-line utility, no GUI. It works much like SQL*Plus does. You'll want to make sure your Oracle ENV is setup so you can start it up and connect.

But.

Everything you need is in the ZIP that SQLDev put together for you, the biggest piece is the .ctl (control file).

Docs

sqlldr scott CONTROL=ulcase1.ctl ulcase1.log

'scott' is the database username, it'll prompt you for a password. You'll subsitute the ulcase1.ctl for the ctl file you got from SQLDev. The log bit is optional, but IMPORTANT.

By the way, this should run FAST.

If you're running this on your pc, your connect string will be more like

sqlldr hr@server:port/service

edited May 9, 2018 at 14:36

answered May 9, 2018 at 13:49

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thanthla Over a year ago

the export is working like charm, but how do you manage the import? Can you extent your answer please. I never worked with the "Loader".

thatjeffsmith Over a year ago

done @Thanthla - it may take you a bit to figure this out on the first go, but it will save you a TON of time going forward

Collectives™ on Stack Overflow

Export and insert CLOB data containing SQL statements with SQL Developer

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related