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?

  • 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 '15 at 7:40
  • 1
    Different encodings maybe (multi-byte vs. single-byte)? Different line endings? How many rows does the output contain? – a_horse_with_no_name May 11 '15 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 '15 at 7:49
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.

  • 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 '15 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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