Как мне спулировать файл в формате CSV с помощью SQLPLUS?

Я использую эту команду для скриптов, которые извлекают данные для таблиц размеров (DW). Итак, я использую следующий синтаксис:

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 '|', <table>.*, '|'
  from <table>
where <conditions>

spool off

И работает. Я не использую sed для форматирования выходного файла.

Я вижу аналогичную проблему...

Мне нужно скопировать CSV файл из SQLPLUS, но на выходе есть 250 столбцов.

Что я сделал, чтобы избежать раздражающего форматирования вывода SQLPLUS:

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

проблема в том, что вы потеряете имена заголовков столбцов...

вы можете добавить это:

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

Я знаю, что это своего рода хардкор, но он работает для меня...

С более новыми версиями клиентских инструментов существует несколько вариантов форматирования вывода запроса. Остальное состоит в том, чтобы заманить его в файл или сохранить вывод в виде файла в зависимости от клиентского инструмента. Вот несколько способов:

• SQL * Plus

Используя команды SQL * Plus, вы можете форматировать, чтобы получить желаемый результат. Используйте SPOOL, чтобы отправить файл в файл.

Например,

SQL> SET colsep ,
SQL> SET pagesize 20
SQL> SET trimspool ON
SQL> SET linesize 200
SQL> 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>

• Версия разработчика SQL до 4.1

В качестве альтернативы вы можете использовать новый /*csv*/ hint в Разработчик SQL.

/*csv*/

Например, в моей SQL Developer версии 3.2.20.10:

Теперь вы можете сохранить вывод в файл.

• SQL Developer Version 4.1

Новый в SQL Developer версии 4.1, используйте следующую команду, как и команду sqlplus, и запустите ее как script. Нет необходимости в подсказке в запросе.

SET SQLFORMAT csv

Теперь вы можете сохранить вывод в файл.

Если вы используете 12.2, вы можете просто сказать

set markup csv on

Я знаю, что это старый поток, однако я заметил, что никто не упомянул опцию подчеркивания, которая может удалить подчеркивания под заголовками столбцов.

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;

Это грубо, но:

set pagesize 0 linesize 500 trimspool on feedback off echo off

select '"' || empno || '","' || ename || '","' || deptno || '"' as text
from emp

spool emp.csv
/
spool off

Вы можете явно форматировать запрос, чтобы создать строку с разделителями с чем-то вроде строк:

select '"'||foo||'","'||bar||'"'
  from tab

И при необходимости настройте параметры вывода. В качестве опции переменная COLSEP в SQLPlus позволит вам создавать файлы с разделителями без необходимости явного создания строки с полями, объединенными вместе. Однако вам нужно будет помещать кавычки вокруг строк в любые столбцы, которые могут содержать встроенные запятые.

предпочитают использовать "set colsep" в приглашении sqlplus вместо редактирования имени col по одному. Используйте sed для редактирования выходного файла.

set colsep '","'     -- separate columns with a comma
sed 's/^/"/;s/$/"/;s/\s *"/"/g;s/"\s */"/g' $outfile > $outfile.csv

Я однажды написал небольшой SQL * Plus script, который использует dbms_sql и dbms_output для создания csv (на самом деле ssv). Вы можете найти в моем репозитории githup.

Вы должны знать, что значения полей могут содержать запятые и кавычки, поэтому некоторые из предложенных ответов не будут работать, так как выходной файл CSV будет неправильным. Чтобы заменить символы котировки в поле и заменить его символом двойной кавычки, вы можете использовать функцию REPLACE, которую предоставляет oracle, чтобы сменить одну кавычку на двойную кавычку.

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

Или, если вам нужен символ одиночного кавычка для полей:

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

Используйте vi или vim для написания sql, используйте colsep с элементом управления A (в vi и vim перед ctrl-A ctrl-v). Не забудьте установить линию и страницы в нечто рациональное и включить trimspool и trimout.

перетащите его в файл. Тогда...

sed -e 's/,/;/g' -e 's/ *{ctrl-a} */,/g'  {spooled file}  > output.csv

Это свойство sed можно превратить в script. "*" До и после ctrl-A сжимает все бесполезные пространства. Разве это не здорово, что они потрудились включить html-вывод из sqlplus, но не native csv?????

Я делаю это так, потому что он обрабатывает запятые в данных. Я превращаю их в полуколоны.

Вы можете использовать подсказку csv. См. Следующий пример:

select /*csv*/ table_name, tablespace_name
from all_tables
where owner = 'SYS'
and tablespace_name is not null;