Andrew Andrew - 1 year ago 95
SQL Question

spool for insert in sql developer not working

I have below select query for which the result of this query i want to create insert scripts and saved it in files. I have used spool.

set long 10000
set lines 100000
set sqlformat insert
spool c:\temp\Insert_TEST_GRP.sql
select ID,NAME,TEST_DATE from TEST_GRP sd
where TEST_DATE =
( select min(TEST_DATE)
from TEST_GRP sd2
where sd.ID = sd2.ID
)
and sd.TEST_DATE <> TO_DATE ('01.01.2000', 'dd.mm.yyyy');
spool off


The file has been created. But when i view the file i am getting the result which is not in the form of insert statements as i want to run this insert statement again.

Below hows the data looks like in file which looks in incorrect format:
enter image description here

Answer Source

We don't have access to your table or your data.

But here it is working with the demo schema HR and its EMPLOYEES table

set sqlformat insert
spool c:\users\jdsmith\desktop\SO_inserts.sql
select * from employees;
spool off

enter image description here

You're using SET LONG - does your table have LOBS in it?

Also, I noticed you asked this same question on the OTN Forums...

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download