Registration Forgot password?
Login
Password
Remember me
Login through VKontakte
General > Articles > Saving result of select into csv file

Saving result of select into csv file

When I need fast send csv file with selecting results, I can't see better solution that sqlplus + spool


-- set name of output file

spool output.csv



-- switch off displaing time after select

set timing off

-- switch off displaying column headers

set heading off

-- set space between new page start and old page finish (line count)

-- if you have 64-bit OS (Windows 7 for example) and 32-bit database (Oracle 11 XE for example) when you 

-- set newpage parameter to 0 you can found in output file character with code 255 (0xFF) in this case 

-- simples way set newpage 1 and just skip empty line on loading csv file

set newpage 0

-- switch off output selecting data on screen

set termout off

-- set line size must be large or equal output string length

set linesize 1000

-- switch off blank space from end of output line to [linesize]

set trimspool off

-- switch off rows count output

set feedback off





-- there your select just concatenate all your columns in one (to remove blank space in output file)

select level || ',' || 

       'Some test string' || 

       to_char(sysdate - level / 10, 'DD.MM.YYYY HH24:MI:SS') 

  from dual connect by level < 1001;





spool off



quit

You can add comment on current page, or on forum page there.

Last comment

Name*E-mail
Code*