CODEWITHSUNDEEP
sqloracle-databaseplsql
Arav
Arav

Reputation: 5247

Oracle Spool setting

When i spool the multiple select query output to a txt file. I see empty new lines after each select query how can i get rid of it.

define spool_file = 'D:\test1'

--set serveroutput on;

SET ECHO OFF

SET NEWPAGE 0

SET SPACE 0

SET PAGESIZE 0

SET FEEDBACK OFF

SET HEADING OFF


-- set echo on  ;

spool D:\test1;

select 'H,correction.csv,'  ||  to_char(sysdate,'DD/MM/YYYY')  from dual;

select 'D,' ||record_id      from cl_record where status=15;

select 'T,correction.csv,' from cl_record where status=15;

spool off;

Upvotes: 2

Views: 15417

Answers (1)

Alain Pannetier
Alain Pannetier

Reputation: 9514

Try TRIMSPOOL

SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON

I changed your script to

define spool_file = '/home/alain/test.log'
--set serveroutput on;
SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET trimspool on
--set echo on ;
spool /home/alain/test.log;
select sysdate from dual;
select 'hello ' || 'world' from dual;
spool off;

The output was 

$ cat test.log
SQL> select sysdate from dual;
03-08-2011 07:48:26
SQL> select 'hello ' || 'world' from dual;
hello world
SQL> spool off;

Upvotes: 1

Related Questions