Sqlplus Spooling: How to Get Rid of First, Empty Line

sqlplus spooling: How to get rid of first, empty line?

SET NEWPAGE NONE is the correct answer.

SET NEWPAGE 0 will cause a page feed, which is not what the OP wants.

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

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

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>

SqlDeveloper SPOOL: Blank first line

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;

remove blank lines from csv using shell script for oracle

Use the

SET PAGESIZE 0

command to avoid the blank lines. this also suppresses column headings, so you can remove

SET HEADING OFF

The command

SPOOL on

does not make sense because it starts spooling in a file named on.lst. So remove this command, too.

If you want to display the heading with the column name
you can try the following settings

set HEADING ON
SET FEEDBACK OFF
set WRAP OFF
set COLSEP ,
SET LINESIZE 32767
set NEWPAGE none
set UNDERLINE OFF
set TRIMSPOOL ON
set TRIMOUT ON
set PAGESIZE 50000

´heading on´ is the default so you must not set it. It enables the display of the column names when a select starts. underline off suppresses the '---' line between column names and data of a select. pages 50000 sets the pagesize to its maximum value (Oracle 11.2). linesize 32767 sets the linesize to its maximum value (Oracle 11.2). newpage none is necessary to suppress this empty line at the beginning of a page that was the primary concern of your posting.
All this can be found in the SQL*Plus Command Reference

The termout off parameter suppresses only output created by a scripts that is executed with the @ or @@ command. It dos not suppress out by a command entered in the SQL*plus console. If you use

sqlplus user/passw@connect <<!
...
!

you use the here-document syntax of the shell language which simulates the interactive input. So put your sql commands in a script, e.g. script.sql, and execute

sqlplus user/passw@connect @script.sql

then termout off will suppress terminal output.

---

Instead of

colsep ,
select username,userid
...

which returns something like

user1                 ,          14
nextuser              ,         236
myuser                ,          11
...

you can use leave the COLSEP unchanged and execute

select username||','||userid
...

to get the following output

user1,14
nextuser,236
myuser,11
...

Maybe this is useful
https://dba.stackexchange.com/a/64620/2047

How to remove unnecessary line breaks in SQL Plus Spooling?

Ok this one solved my problem.

From this,

SET HEADING OFF;
SET ECHO OFF;
SET PAGES 999;
SET LONG 999999;

I added this:

SET LONGCHUNKSIZE 999999;
SET PAGESIZE 0;
SET LINESIZE 500;

---

Related Topics