How do I spool to a CSV formatted file using SQLPLUS

Question

I want to extract some queries to a CSV output format  Unfortunately  I can t use any fancy SQL client or any language to do it  I must use SQLPLUS   How do I do it

User · Answer

I wrote this purely SQLPlus script to dump tables to CSV in 1994.

As noted in the script comments, someone at Oracle put my script in an Oracle Support note, but without attribution.

https://github.com/jkstill/oracle-script-lib/blob/master/sql/dump.sql

The script also also builds a control file and a parameter file for SQL*LOADER

User · Answer

You can explicitly format the query to produce a delimited string with something along the lines of   select      foo         bar        from tab   And set up the output options as appropriate   As an option  the COLSEP variable on SQLPlus will let you produce delimited files without having to explicitly generate a string with the fields concatenated together   However  you will have to put quotes around strings on any columns that might contain embedded comma characters

User · Answer

You could use csv hint  See the following example   select   csv   table name  tablespace name from all tables where owner    SYS  and tablespace name is not null

User · Answer

I know this is an old thread  however I noticed that no one mentioned the underline option  which can remove the underlines under the column headings   set pagesize 50000--50k is the max as of 12c set linesize 10000    set trimspool on  --remove trailing blankspaces set underline off --remove the dashes underlines under the col headers set colsep    select   from DW TMC PROJECT VW

User · Answer

You should be aware that values of fields could contain commas and quotation characters  so some of the suggested answers would not work  as the CSV output file would not be correct   To replace quotation characters in a field  and replace it with the double quotation character  you can use the REPLACE function that oracle provides  to change a single quote to double quote   set echo off set heading off set feedback off set linesize 1024   -- or some other value  big enough set pagesize 50000 set verify off set trimspool on  spool output csv select trim                  replace col1                                 replace col2                                replace coln                      -- etc  for all the columns from   yourtable   spool off   Or  if you want the single quote character for the fields   set echo off set heading off set feedback off set linesize 1024   -- or some other value  big enough set pagesize 50000 set verify off set trimspool on  spool output csv select trim           replace col1                             replace col2                            replace coln                         -- etc  for all the columns from   yourtable   spool off

User · Answer

With newer versions of client tools  there are multiple options to format the query output  The rest is to spool it to a file or save the output as a file depending on the client tool  Here are few of the ways    SQL Plus   Using the SQL Plus commands you could format to get your desired output  Use SPOOL to spool the output to a file   For example   SQL gt  SET colsep   SQL gt  SET pagesize 20 SQL gt  SET trimspool ON SQL gt  SET linesize 200 SQL gt  SELECT   FROM scott emp        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ----------       7369 SMITH      CLERK           7902 17-DEC-80        800                    20       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30       7566 JONES      MANAGER         7839 02-APR-81       2975                    20       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10       7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20       7839 KING       PRESIDENT            17-NOV-81       5000                    10       7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30       7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20       7900 JAMES      CLERK           7698 03-DEC-81        950                    30       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10  14 rows selected   SQL gt     SQL Developer Version pre 4 1   Alternatively  you could use the new   csv   hint in SQL Developer     csv     For example  in my SQL Developer Version 3 2 20 10     Now you could save the output into a file    SQL Developer Version 4 1   New in SQL Developer version 4 1  use the following just like sqlplus command and run as script  No need of the hint in the query   SET SQLFORMAT csv   Now you could save the output into a file

User · Answer

There is a problem using sqlplus to create csv files  If you want the column headers only once in the output and there are thousands or millions of rows  you cannot set pagesize large enough not to get a repeat  The solution is to start with pagesize   50 and parse out the headers  then issue the select again with pagesize   0 to get the data  See bash script below      bin bash FOLDER  csvdata mydb  CONN  192 168 100 11 1521 mydb0023 world  CNT 0376 ORD  0376  TABLE  MY ATTACHMENTS   sqlplus -L logn pswd     CONN  lt  lt EOF  gt  dev null set pagesize 50  set verify off  set feedback off  set long 99999  set linesize 32767  set trimspool on  col object ddl format A32000  set colsep    set underline off  set headsep off  spool   ORD   TABLE  tmp  select   from tblspc   TABLE  where rownum  lt  2  EOF LINES  wc -l   ORD   TABLE  tmp   cut -f1 -d         LINES  -le 3    amp  amp      echo  No Data Found in   TABLE          LINES  -gt 3    amp  amp      cat   ORD   TABLE  tmp   sed -e  s       g  -e  s       -e  s      g  -e  s      g    tail -n  3   head -n 1  gt      ORD   TABLE  headers    sqlplus -L logn pswd     CONN  lt  lt EOF  gt  dev null set pagesize 0  set verify off  set feedback off  set long 99999  set linesize 32767  set trimspool on  col object ddl format A32000  set colsep    set underline off  set headsep off  spool   ORD   TABLE  tmp  select   from tblspc   TABLE   EOF LINES  wc -l   ORD   TABLE  tmp   cut -f1 -d         LINES  -le 3    amp  amp      echo  No Data Found in   TABLE          LINES  -gt 3    amp  amp      cat   ORD   TABLE  headers  gt    FOLDER    ORD   TABLE  csv   cat   ORD   TABLE  tmp   sed -e  s       g  -e  s       -e  s      g  -e  s      g    tail -n  2   head -n -1  gt  gt    FOLDER    ORD   TABLE  csv

User · Answer

I have once written a little SQL Plus script that uses dbms sql and dbms output to create a csv  actually an ssv   You can find it on my githup repository

User · Answer

prefer to use  set colsep  in sqlplus prompt instead of editing col name one by one   Use sed to edit the output file   set colsep           -- separate columns with a comma sed  s      s      s  s      g s   s     g   outfile  gt   outfile csv

User · Answer

If you are using 12 2  you can simply say  set markup csv on spool myfile csv

User · Answer

spool D  test txt      select   from emp          spool off

User · Answer

I use this command for scripts which extracts data for dimensional tables  DW   So  I use the following syntax   set colsep     set echo off set feedback off set linesize 1000 set pagesize 0 set sqlprompt    set trimspool on set headsep off  spool output dat  select       lt table gt           from  lt table gt  where  lt conditions gt   spool off   And works  I don t use sed for format the output file

User · Answer

It s crude  but   set pagesize 0 linesize 500 trimspool on feedback off echo off  select        empno             ename             deptno        as text from emp  spool emp csv   spool off

User · Answer

You could also use the following  although it does introduce spaces between fields   set colsep       -- separate columns with a comma set pagesize 0   -- No header rows set trimspool on -- remove trailing blanks set headsep off  -- this may or may not be useful   depends on your headings  set linesize X   -- X should be the sum of the column widths set numw X       -- X should be the length you want for numbers  avoid scientific notation on IDs   spool myfile csv  select table name  tablespace name    from all tables  where owner    SYS     and tablespace name is not null    Output will be like       TABLE PRIVILEGE MAP            SYSTEM                             SYSTEM PRIVILEGE MAP           SYSTEM                             STMT AUDIT OPTION MAP          SYSTEM                             DUAL                           SYSTEM        This would be a lot less tedious than typing out all of the fields and concatenating them with the commas   You could follow up with a simple sed script to remove whitespace that appears before a comma  if you wanted   Something like this might work    my sed skills are very rusty  so this will likely need work   sed  s  s       myfile csv

User · Answer

Use vi or vim to write the sql  use colsep with a control-A  in vi and vim precede the ctrl-A with a ctrl-v   Be sure to set the linesize and pagesize to something rational and turn on trimspool and trimout   spool it off to a file   Then     sed -e  s     g  -e  s    ctrl-a      g    spooled file    gt  output csv   That sed thing can be turned into a script   The      before and after the ctrl-A squeezes out all the useless spaces   Isn t it great that they bothered to enable html output from sqlplus but NOT native csv       I do it this way because it handles commas in the data   I turns them to semi-colons

User · Answer

I see a similar problem     I need to spool CSV file from SQLPLUS  but the output has 250 columns   What I did to avoid annoying SQLPLUS output formatting   set linesize 9999 set pagesize 50000 spool myfile csv select x from   select col1       col2       col3       col4       col5       col6       col7       col8       col9       col10       col11       col12       col13       col14       col15       col16       col17       col18       col19       col20       col21       col22       col23       col24       col25       col26       col27       col28       col29       col30 as x from                here is the  core  select           spool off   the problem is you will lose column header names     you can add this   set heading off spool myfile csv select col1 name       col2 name       col3 name       col4 name       col5 name       col6 name       col7 name       col8 name       col9 name       col10 name       col11 name       col12 name       col13 name       col14 name       col15 name       col16 name       col17 name       col18 name       col19 name       col20 name       col21 name       col22 name       col23 name       col24 name       col25 name       col26 name       col27 name       col28 name       col29 name       col30 name from dual   select x from   select col1       col2       col3       col4       col5       col6       col7       col8       col9       col10       col11       col12       col13       col14       col15       col16       col17       col18       col19       col20       col21       col22       col23       col24       col25       col26       col27       col28       col29       col30 as x from                here is the  core  select           spool off   I know it s kinda hardcore  but it works for me

[sql] How do I spool to a CSV formatted file using SQLPLUS?

Examples related to sql

Examples related to oracle

Examples related to csv

Examples related to sqlplus