CODEWITHSUNDEEP
sqloracle10goracle-sqldeveloper
Thanthla
Thanthla

Reputation: 586

Export and insert CLOB data containing SQL statements with SQL Developer

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?

Upvotes: 0

Views: 5541

Answers (1)

thatjeffsmith
thatjeffsmith

Reputation: 22447

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.

enter image description here

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.

enter image description here

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

Upvotes: 1

Related Questions