By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,740 Members | 790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,740 IT Pros & Developers. It's quick & easy.

spool old and new values in SQL

P: 4
Hi,
I am running an sql script in oracle to spool a csv file.
I am using the following set up:
Expand|Select|Wrap|Line Numbers
  1. Define name='JOE'
  2. spool names.txt
  3. set linesize 200
  4. set pagesize 50000
  5. set heading off
  6. set termout off
  7. Select 'Names' from dual;
  8. select '&Name'  from user1;
  9. select surname from user1;
  10. SPOOL OFF;
  11.  
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!
Sep 18 '08 #1
Share this Question
Share on Google+
5 Replies


100+
P: 153
I dont know if you can turn those off in sql plus.

Have you looked at using UTL_FILE to make your CSV?
Sep 18 '08 #2

P: 4
set verify off seems to be working for me!
Cant eliminate the blank lines but i am happy with that. cheers!
Sep 19 '08 #3

amitpatel66
Expert 100+
P: 2,367
Expand|Select|Wrap|Line Numbers
  1. SET FEED OFF
  2. SET VERIFY OFF 
  3. SET HEADING OFF 
  4. SET TERMOUT OFF
  5. SET ECHO OFF
  6. SET LINESIZE 32767 
  7. SET PAGES 0 
  8. SET WRAP OFF 
  9. SET SCAN OFF 
  10. SET TRIM ON 
  11. SET TRIMS ON 
  12. SET TAB OFF 
  13. SET SERVEROUTPUT OFF
  14. SET PAUSE OFF
  15. SET TIMING OFF
  16. spool d:/a.txt;
  17. SELECT 'Name' FROM dual;
  18. SELECT empname from emp;
  19. spool off
  20. quit;
  21. /
  22.  
I have used lots of SET commands above. Few of them might not be required. Just in case!!
Sep 19 '08 #4

P: 4
Just in case anyone reads this, the set linesize 0 gets rid of the blank lines!
Jan 30 '09 #5

Saii
Expert 100+
P: 145
I dont we can set linesize 0 its valid range starts from 1
Feb 19 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.