Как вывести CSV-файл, используя SQLPLUS Spool?
- Вопросы
- Как вывести результаты в текстовый файл с помощью SQLPlus?
- Как с помощью SQLPlus вывести имя файла из таблицы?
- Размер выводимого файла больше 4 Гб
- Oracle SQLPlus: как вывести результаты с помощью SQLPlus без команды spool off?
- Как осуществить вывод SQLPlus в csv-файл?
- Форматирование заголовков при выводе из SQLPlus в csv-файл
- Экспорт результатов запроса в одну колонку
- Команда Spool: не выводить SQL запрос в файл
- Как правильно экспортировать результаты запроса select в csv-файл, используя spool?
- Как отправить результат экспорта из spool с помощью swiftmailer без использования команды?
- Ответы
Вопросы
Я хочу вывести несколько запросов в формате 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 без использования команды?
Как отправить результат экспорта из 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