Compact Spool file from Sqlplus

Question

I have scripts that I run in Oracle SQL Developer and then export the result to my pc as a csv. I now started using sqlplus with a .bat to run my scripts and spool the result to my pc. But I noticed that the export from sqlplus is twice as large as the export file from SQL Developer. The contents/result of the files are exactly the same. Here are my sql file called oracle_sql.sql

set embedded on;
set pagesize 0;
set colsep ',';
set echo off;
set feedback off;
set linesize 1000;
set trimspool on;
set headsep off;
set termout off;
set heading on;
set und off;
spool mytable.csv
select item, item_desc
from mytable;
spool off

here are my .bat file code

@echo off
sqlplus jasons/jasons@localhost @oracle_sql.sql

Do I need to have an extra setting in my sql file that I am not aware off?

Answer 1

That's because of the set linesize 1000 clause. That one is making all your lines in the spool file to be 1000 rows long, even if there is no such data.

Try this from a clean SQL sqlplus session:

set linesize 1000
spool test.txt
select * from dual;
spool off
exit

Then, go back to shell and open the test.txt with any text editor, move to the lines belonging to the SQL query results (not the lines with the prompt where you wrote the query) and check it's length.

Those 3 lines will be 1000 bytes long.