hello,
I wonder if anyone can help me please?
I am trying to spool a flat csv (.txt) file from a query which returns a very large dataset. The problem I am having is that I can not seem to fit everything on one row. There is a lot of whitespace being brought back in the spooled file and I can not figure out how to get rid of it. I'm sure plenty of my set commands are redundant but I've been trying everything I can think of to get this efficient and get rid of this pesky whitespace.
I am aware that concatenating the SQL is one solution, but I'm
very keen not to have to add yet more coding to an already complex sql query. Is there any way I can get rid of the whitespace and hence get my characters per row down to a more manageable number?
here is the code below, lets say the query returns three fields (emp_id, emp_name, emp_state) and two records (joe bloggs and john smith), I would want the output in my text file to be:
emp_id,emp_name,emp_state
1,joe bloggs,florida,
2,john smith,texas,
as opposed to what i am getting at the moment which is quite a lot of spaces between the commas (i cant figure out how to post it here)
could anybody please advise? is there a way I can do this without having to concatenate all through my sql query??
thank you
jon
set echo off
set feedback off
set heading on
set linesize 5000
set tab on
set trimspool on
set trim on
set term off
set colsep ,
set headsep ,
Spool C:\Jon_Oracle_Testing\spooltext.txt
@C:\Jon_Oracle_Testing\superquery_031110.sql --big query, around 65 columns and 250,000 rows
spool off;