spool old and new values in SQL

[daves1]

Hi,
I am running an sql script in oracle to spool a csv file.
I am using the following set up:

Define name='JOE'
spool names.txt
set linesize 200
set pagesize 50000
set heading off
set termout off
Select 'Names' from dual;
select '&Name'  from user1;
select surname from user1;
SPOOL OFF;
 

Which gives me the following:
Names

old 1: select '&Name' from user1
new 1: select 'JOE' from user1

JOE
Bloggs


It is giving me the correct values but i dont want the lines that have the old and new value. i also want to get rid of the space between Names and Joe.

Any ideas would be greatly appreciated!

[Dave44]

I dont know if you can turn those off in sql plus.

Have you looked at using UTL_FILE to make your CSV?

[daves1]

set verify off seems to be working for me!
Cant eliminate the blank lines but i am happy with that. cheers!

[amitpatel66]

SET FEED OFF
SET VERIFY OFF 
SET HEADING OFF 
SET TERMOUT OFF
SET ECHO OFF
SET LINESIZE 32767 
SET PAGES 0 
SET WRAP OFF 
SET SCAN OFF 
SET TRIM ON 
SET TRIMS ON 
SET TAB OFF 
SET SERVEROUTPUT OFF
SET PAUSE OFF
SET TIMING OFF
spool d:/a.txt;
SELECT 'Name' FROM dual;
SELECT empname from emp;
spool off
quit;
/
 

I have used lots of SET commands above. Few of them might not be required. Just in case!!

[daves1]

Just in case anyone reads this, the set linesize 0 gets rid of the blank lines!

[Saii]

I dont we can set linesize 0 its valid range starts from 1