2

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?

Improve this question
3
  • Headers, commas and quotes? Though it is puzzling that the discrepancy is so great. Have you run a diff on the files?
    – Vérace
    May 11, 2015 at 7:40
  • 1
    Different encodings maybe (multi-byte vs. single-byte)? Different line endings? How many rows does the output contain?
    – user1822
    May 11, 2015 at 7:43
  • Vérace, I am not sure what you mean by diff. In this code there are about 500 rows. I also have other scripts that will have outputs with about 1 million rows.
    – Jason Samuels
    May 11, 2015 at 7:49

1 Answer 1

Reset to default
2

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.

Improve this answer
3
  • Changing the linesize does help making the file smaller, but when i change it to say 15 then the two columns of a record gets split into two seperate rows with an empty row after each record.
    – Jason Samuels
    May 11, 2015 at 20:48
  • @JasonSamuels I don't understand why this decreased the output. The trailing blanks should be trimmed by the "set trimspool on" directive.
    – miracle173
    Jan 2, 2020 at 20:45
  • @SergiPorta You do not use the "set trimspool on" directive that will trim the trailing blanks of the output lines. The OP uses trimspool.
    – miracle173
    Jan 2, 2020 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.