Search code examples
sqloracle-databaseoutputoracle-sqldeveloperflat-file

SQL Developer spooling: how to get rid of the first, empty line

I am using the following spooling script with Oracle SQL Developer 4.1:

set echo off
set feedback off
set termout off

spool A.txt
select /*csv*/ * from A where rownum <= 1000;
spool off

spool B.txt
select /*csv*/ * from B where rownum <= 1000;
spool off

...

But the spooled output files contain a blank line at the beginning.

This is the same problem detailed in sqlplus spooling: How to get rid of first, empty line?. I tried using SET NEWPAGE NONE but, as of SQL Developer 4.1.2, this only results in an error message and no change in the output format:

SP2-0158: unknown SET option "newpage"

Is there any way to suppress the output of this first, empty line in SQL Developer?

Solution

  • two things:

    1. it's the default SQL*Plus behavior, which we try to emulate 100% as much as possible
    2. there's a bug - we're not supporting SET PAGESIZE 0. if you use this in conjunction with SET TRIMSPOOL ON, you'll lose the blank line(s)

    we've got it on the list for the next release

    2020 Update

    Using Version 20.2 of SQL Developer, your script works as expected

    enter image description here

    Unfortunately I see the issue in SQLcl (command line version of SQLDev) version 20.2, but it's fixed for 20.3 thanks to feedback from some folks on Twitter earlier this Summer.

    Here's what it'll look like in a month or so when SQLcl 20.3 is released

    10:38:34 nolog >show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 20.3.0.0 build: 20.3.0.240.1605
10:40:31 nolog >set echo off
10:40:49 nolog >set feedback off
10:40:52 nolog >set termout off
10:40:56 nolog >spool A.txt
10:41:04 nolog >select /*csv*/ * from regions;
"REGION_ID","REGION_NAME"
1,"Europe"
2,"Americas"
3,"Asia"
4,"Middle East and Africa"
10:41:14 nolog >spool off
10:41:19 nolog >exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

c:\SQLDev\sqlcl\20.3-lines\sqlcl\bin>type A.txt
"REGION_ID","REGION_NAME"
1,"Europe"
2,"Americas"
3,"Asia"
4,"Middle East and Africa"

    c:\SQLDev\sqlcl\20.3-lines\sqlcl\bin>