Как вывести CSV-файл, используя SQLPLUS Spool?

2023-06-02 0 ∞

Вопросы
Ответы

Вопросы

Я хочу вывести несколько запросов в формате CSV без использования SQL-клиент или языка программирования, применяя только SQLPlus. Как это сделать?

Как вывести результаты в текстовый файл с помощью SQLPlus?

Пытаюсь вывести результат запросов в текстовый файл, используя SQLPlus. Но у меня возникают трудности. Вот команда, которую я использую:

spool MyText.txt;

Файл MyText.txt не существует. Создаст ли его SQLPlus?

Как с помощью SQLPlus вывести имя файла из таблицы?

У меня есть следующий код:

SPOOL ON
SPOOL XXXXX.bkp
SELECT * FROM my_table WHERE field1=’value’;
SPOOL OFF

Нужно, чтобы XXXXX.bkp было значением поля этой таблицы.

Размер выводимого файла больше 4 Гб

Я осуществляю выборку миллиардов строк из базы данных Oracle с помощью sqlplus на RHEL 5 следующим образом:

spool on
>set heading off
>set trimsp on pages 0

Но когда размер файла, в который помещаются результаты выборки, становится больше 4 Гб, запись прекращается.

Oracle SQLPlus: как вывести результаты с помощью SQLPlus без команды spool off?

Есть ли способ вывести данные в SQLPlus без использования команды spool off?

Как осуществить вывод SQLPlus в csv-файл?

Я пытаюсь создать csv-файл с помощью SQLPlus из командной строки. Но заголовки повторяются много раз, а нулевые значения появляются в конце строки. Как это исправить?

Форматирование заголовков при выводе из SQLPlus в csv-файл

Нужно вывести результаты в csv-файл из таблицы Oracle, используя SQLPlus. Вот нужный формат данных:

"HOST_SITE_TX_ID","SITE_ID","SITETX_TX_ID","SITETX_HELP_ID"
"664436565","16","2195301","0"
"664700792","52","1099970","0"

Экспорт результатов запроса в одну колонку

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

Команда Spool: не выводить SQL запрос в файл

Я хочу вывести результаты запроса в csv-файл, используя следующий код:

spool c:test.csv 
select /*csv*/ username, user_id, created from all_users;
spool off;

Но первой строкой в результатах записывается SQL запрос.

select /*csv*/ username    user_id     created from all_users
USERNAME    USER_ID CREATED
REPORT  52  11-Sep-13
WEBFOCUS    51  18-Sep-12

Есть ли способ исправить это?

Как правильно экспортировать результаты запроса select в csv-файл, используя spool?

Код:

set heading off
  set arraysize 1
  set newpage 0
  set pages 0
  set feedback off
  set echo off
  set verify off

spool 'c:farmerList.csv'
/

select FIRSTNAME','LASTNAME','TRN','CELL','PARISH

spool off

Файл сохраняется в директории. Но в него записывается только "select FIRSTNAME','LASTNAME','TRN','CELL','PARISH", а не результаты запроса. Что я делаю не так?

Как отправить результат экспорта из spool с помощью swiftmailer без использования команды?

php app/console swiftmailer:spool:send --env=prod

Мне нужно вставить этот код в php-файл, чтобы администратор сервера добавил скрипт в планировщик.

Ответы

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

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

Также можно установить нужные параметры вывода. В SQLPlus можно использовать переменную COLSEP, чтобы получить файл с разделителями без генерации строки путём соединения полей. При этом нужно добавить кавычки вокруг строк, которые могут содержать запятые.

Например, так:

set pagesize 0 linesize 500 trimspool on feedback off echo off

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

spool emp.csv
/
spool off

Также можно использовать приведенный ниже код. Но учтите, что он не выводит пробелы между полями.

set colsep ,     -- разделить поля запятыми
set pagesize 0   -- убрать заголовки
set trimspool on -- убрать пустые значения в конце
set headsep off  -- это может быть полезным в зависимости от заголовков
set linesize X   -- X должно быть общей шириной всех полей
set numw X       -- X должно быть длинной числовых значений полей (чтобы избежать научного формата для поля ID)

spool myfile.csv

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

Вывод будет примерно таким:

TABLE_PRIVILEGE_MAP           ,SYSTEM                        
    SYSTEM_PRIVILEGE_MAP          ,SYSTEM                        
    STMT_AUDIT_OPTION_MAP         ,SYSTEM                        
    DUAL                          ,SYSTEM 
...

Это проще, чем перечислять все поля и соединять их запятыми. Также можно использовать простой скрипт для команды sed, чтобы убрать пробелы перед запятыми.

Например:

sed 's//s+,/,/' myfile.csv

Мне нужно вывести результат из SQLPlus в csv-файл, но вывод содержит 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

В SQLPlus я предпочитаю использовать команду set colsep вместо редактирования заголовков столбцов. Также можно применить команду sed, чтобы отредактировать получившийся файл.

set colsep '","'     -- Разделяем столбца запятыми
sed 's/^/"/;s/$/"/;s//s *"/"/g;s/"/s */"/g' $outfile > $outfile.csv

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

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 для форматирования получившегося файла.

Я написал небольшой скрипт для SQLPlus, который использовал dbms_sql и dbms_output, чтобы создать csv-файл (на самом деле ssv). Вы можете найти его в моём репозитории.

Убедитесь, что вы инициализировали linesize и pagesize какими-нибудь рациональными значениями и включили trimspool и trimout. Выведите результаты в файл. Затем…

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

Команда sed может быть выполнена в скрипте. Звёздочка до и после ctrl-A убирает пробелы. Этот метод помогает решить проблему с запятыми в данных. Я заменяю их точками с запятой.

Новые версии клиентских приложений для работы с SQL поддерживают несколько вариантов форматирования результатов запроса:

В SQLPlus

С помощью команд SQLPlus можно отформатировать результаты. Используйте функцию 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>

2. В SQL Developer Version до версии 4.1

Альтернативный вариант – использовать новую подсказку /*csv*/ в SQL Developer

/*csv*/

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

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

В SQL Developer версии 4.1

Новая функция SQL Developer 4.1 позволяет использовать следующую команду без подсказок.

SET SQLFORMAT csv

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

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

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

Параметр underline позволяет убрать подчёркивания под заголовками столбцов.

set pagesize 50000 -- 50 тысяч – максимум для версии 12c
set linesize 10000   
set trimspool on  - убираем пробелы в конце
set underline off – убираем подчёркивания под заголовками
set colsep ~

select * from DW_TMC_PROJECT_VW;

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

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, '"', '""') || '"' ) –- и т.п. для всех полей
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, '''', '''''') || '"' ) –- и т.п. для всех полей
from   yourtable
/
spool off

Если вы используете SQL Developer версии 12.2, можно просто добавить

set markup csv on

Сергей Бензенкоавтор-переводчик статьи «How do I spool to a CSV formatted file using SQLPLUS»