1

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

Andrew
  • 3,632
  • 24
  • 64
  • 113
  • Maybe show us the incorrectly formatted data, and what you are expecting? – OldProgrammer Aug 16 '17 at 16:21
  • 1
    Also maybe mention which version of SQL Developer you're using, and how you're viewing the spooled file. Do you get an error from `set sqlformat insert` perhaps, if you're on a very old version? – Alex Poole Aug 16 '17 at 16:47
  • thanks Alex i understood the issue is with my SQL developer version. I am running older version i.e v3.0. But when i checked now in higher version it works perfectly – Andrew Aug 16 '17 at 16:51

4 Answers4

2

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

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
  • i have removed set long 10000 , set lines 100000 but still not getting the result in proper format. I have mentioned in my question the result i am getting – Andrew Aug 16 '17 at 16:43
  • Without your TEST_GRP table and data, it will be impossible for us to help you. – thatjeffsmith Aug 16 '17 at 16:45
  • can you please tell me how are you running it in sql developer ? I am running it using F5 key. I am getting the result but the result format is not correct. – Andrew Aug 16 '17 at 16:47
  • With F5. Why are you ignoring my request? – thatjeffsmith Aug 16 '17 at 16:48
  • Sorry i am not ignoring your request. As you said i have removed set long. Do you need actual data ? – Andrew Aug 16 '17 at 16:49
  • 1
    thanks i understood the issue is with my SQL developer version. I am running older version i.e v3.0. But when i checked now in higher version it works perfectly – Andrew Aug 16 '17 at 16:52
  • No, I asked if your data had LOBS - a BLOB or a CLOB - the problem here very well could be data dependent. I've asked for your data, because yes, I need it. – thatjeffsmith Aug 16 '17 at 16:52
  • Thanks Jeff it works now the problem was with my sql developer version – Andrew Aug 16 '17 at 16:53
2

The set sqlformat method to format your query results was added in version 4.1.

If you're on an earlier version (e.g. 3.0 as you said in a comment) then it would complain, which you seem to have overlooked; e.g. in 4.0:

set sqlformat insert

gets this in the script output window:

line 1: SQLPLUS Command Skipped: set sqlformat insert

The /*insert*/ method was available earlier than that:

select /*insert*/ * from dual;

which gets

REM INSERTING into dual
SET DEFINE OFF;
Insert into "dual" (DUMMY) values ('X');

(don't really attempt to insert into dual, of course). You can also use the export wizard (tools->database export); or run your query with control-enter, right-click on the output grid and choose 'export' (though it may repeat the query).

Upgrading to the current version is the sensible thing to do though.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Your code examples suggest that your are using sqlplus. I'm positive that that sqlformat command works in sqlcl. The source quoted also uses sqlci, but imho doesn't make it explicit enough: "Now that we have our own SQL*Plus command line interface (AKA SQLcl),..." – Arman Sep 29 '21 at 08:28
  • 1
    @Arman - no, this was using SQL Developer, which is what the question was about. The "SQLPLUS" in the error message is because SQL Developer recognises most SQL\*Plus syntax but that old version didn't understand `set sqlformat` - neither does SQL\*Plus of course - but it was just a generic message for any `set` it didn't know. SQLcl didn't exist when SQL Developer 3.0 was released, I believe. But `sqlformat` works in both SQLcl and SQL Developer (from version 4.1 anyway). – Alex Poole Sep 29 '21 at 08:46
1

You need to return a string that is the INSERT statement formatted with the columns you need. Example

set long 10000  
set lines 100000  
set sqlformat insert  
spool c:\temp\Insert_TEST_GRP.sql  
select 'INSERT INTO TEST_GRP (ID,NAME,TEST_DATE) VALUES (' ||
     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

If you are using sqldeveloper, then you can just use the built-in export function and export the result grid as inserts.

OldProgrammer
  • 12,050
  • 4
  • 24
  • 45
  • The `set sqlformat insert ` should mean you don't need to manually construct the insert statements. (And if you do, you need to consider adding single- and double-quotes around strings, date formats, etc.) – Alex Poole Aug 16 '17 at 16:40
  • thanks its working but when i view the insert statement and tried to run it i am getting error as SQL Error: ORA-00917: missing comma. There is date column and in values field its not taken as string but just like number format as 17-JAN-17 12.00.00. How can i make it string ? – Andrew Aug 16 '17 at 16:41
1

I used SQL Developer. Make sure to click Run script(F5) instead of run statement.

For multi statement in sample file use "/" between statement.

SET FEEDBACK OFF
set sqlformat insert
spool C:\Necessary_file\Reqular_task\QDE\profile_const.sql
select * from export_profile where profile_name='SPS_DIAG';
/
select * from profile_const where profile_name='SPS_DIAG';
/
select * from profile_panel where profile_name='SPS_DIAG' order by 5
/
spool off

enter image description here

Shahidul Islam Molla
  • 541
  • 6
  • 6