2

I want to export multiple tables to a semicolon separated file each using SqlDeveloper (version is 4.1.5.21).

Currently I use

SET SQLFORMAT delimited
spool ..\dir\table1.csv
select * from table1;
spool off;

for each table in a script, which is fine.

The problem is that the resulting file has a blank first line. Is it somehow possible to delete it? I've done some research and it looks like the function which would take care of that in SqlPlus is not implemented in SqlDeveloper.

I've also tried to accomplish the same in SqlPlus, but I didn't even get close to the result which is produced by SqlDeveloper.

If it's not possible to get rid of that blank line using spool, is there any sql inbuilt function I could use to manipulate the resulting flag so I don't have to run a SqlDeveloper and then a bash script to get proper files?

Jdv
  • 962
  • 10
  • 34
  • What is the result (output) you are looking for? – BobC Mar 02 '17 at 15:41
  • I don't see a blank line in 4.2.0.16.356 (well, not at the top; I do get one at the bottom of the file). Not sure if that's version-specific behaviour - are you doing anything else int he script (or session) before what you showed; any other `set` commands? – Alex Poole Mar 02 '17 at 17:04
  • 1
    OK, I can indeed duplicate in 4.1.15, so the behaviour has changed (been fixed?) in 4.2.0 (still early adopter). I haven't found a way to make 4.1.15 behave the same. You used to be able to work around this with `set pagesize 0` but zero isn't accepted in that version. (Incidentally, 4.2.0 seems to have also changed how spool works - running your code directly (not via a separate @) no longer [echos the SQL statements](http://stackoverflow.com/q/19033943/266304), which is nice). – Alex Poole Mar 02 '17 at 18:13

1 Answers1

0

If anyone stumbles upon this question:

As Alex pointed out in the comments, this is a bug in version 4.1.15 (and maybe earlier versions) of SqlDeveloper. To get rid of it, upgrade to 4.2.0.

If you are, like me, for some reason stuck with an earlier version, you can use sqlcl which is shipped together with the SqlDeveloper to execute your script without the described problems.

My .sql script looks like this:

SET ECHO OFF
SET FEEDBACK OFF
SET sqlformat delimited ; " "

spool ..\relative\path.csv
select * from table1;

SPOOL OFF;
QUIT;
Jdv
  • 962
  • 10
  • 34