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

Вопросы

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

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

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

spool MyText.txt;

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

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

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

SPOOL ONSPOOL XXXXX.bkpSELECT * 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_usersUSERNAME USER_ID CREATEDREPORT 52 11-Sep-13WEBFOCUS 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 offspool 'c:farmerList.csv'/select FIRSTNAME','LASTNAME','TRN','CELL','PARISHspool 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 offselect '"' || empno || '","' || ename || '","' || deptno || '"' as textfrom empspool 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.csvselect 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 9999set pagesize 50000spool myfile.csvselect xfrom(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 xfrom (        ...  here is the "core" select     ));spool off

Проблема заключается в том, что вы теряете заголовки столбцов. Это можно легко исправить при помощи следующего кода:

set heading offspool myfile.csvselect 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 xfrom(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 xfrom (        ...  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 offset feedback offset linesize 1000set pagesize 0set sqlprompt ''set trimspool onset headsep offspool output.datselect '|', <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 20SQL> SET trimspool ONSQL> SET linesize 200SQL> 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,          ,        1014 rows selected.SQL>

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

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

/*csv*/

К примеру, в моём SQL Developer версии 3.2.20.10 :

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

Сейчас можно легко сохранить этот отображение в файл.

В SQL Developer версии 4.1

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

SET SQLFORMAT csv

Сейчас можно без труда сохранить отображение в файл.

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

select /*csv*/ table_name, tablespace_namefrom all_tableswhere owner = 'SYS'and tablespace_name is not null;

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

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

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

set echo offset heading offset feedback offset linesize 1024   -- or some other value, big enoughset pagesize 50000set verify offset trimspool onspool output.csvselect trim(       '"'   || replace(col1, '"', '""') ||        '","' || replace(col2, '"', '""') ||       '","' || replace(coln, '"', '""') || '"' ) –- и т.п. для всех полейfrom yourtable/spool off

Если вы хотели бы оставить единичные кавычки в значениях полей ввода используйте приведенный ниже программный код:

set echo offset heading offset feedback offset linesize 1024   -- or some other value, big enoughset pagesize 50000set verify offset trimspool onspool output.csvselect trim('"'   || replace(col1, '''', '''''') || '","' || replace(col2, '''', '''''') ||'","' || replace(coln, '''', '''''') || '"' ) –- и т.п. для всех полейfrom yourtable/spool off

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

set markup csv on

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *