Search code examples
sqloraclefileinsertspool

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

Solution

  • 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...