Результат запроса Oracle в CSV или XML

Недавно встал вопрос о том как выгрузить огромный объем данных из Oracle в XML. И изначально я хотел призвать на помощь PHP, но загвоздка была бы в том что выгрузка длилась бы около суток, а то и более. И если я правильно не предугадаю какое время выполнения дать для PHP, то мне пришлось бы повторить процедуру, и возможно не один раз. Поэтому я решил покопать и найти в Oracle подобную возможность.

И как выяснилось все оказалось довольно просто!

Oracle в CSV

Для начала мы воспользуемся функцией объединения строк || (две вертикальные палочки).

SELECT 'Hello' || ' word!' FROM dual;

А теперь код генерирующий CSV для примера:

SELECT table.id || ',' || table.name FROM table;

Или вот реальный пример который использовал я:

SELECT id || ',' || numberobj || ',' || full_rka || ',' || kad_no || ',' || DECODE(actual, 1, 'true', 'false') || ',' || modified|| ',' || building_id || ',' || categorybuilding_id FROM apartments;

Но это только начало, мы получаем эти данные в консоль, а нам надо во внешний файл. Теперь нам поможет команда spool. Данная команда используется так:

/* Для начала вывода в файл */
spool имя_файла;
/* Для окончания вывода в файл */
spool off;

Причем я рекомендую использовать spool off, для того что бы Oracle закрывал файл, т.к. он видимо не всегда пишет данные сразу, а переодически складывает их в буфер.

И так пример:

spool /opt/tmp/apartments.csv;
SELECT id || ',' || numberobj || ',' || full_rka || ',' || kad_no || ','  || DECODE(actual, 1, 'true', 'false') || ',' || modified|| ',' ||  building_id || ',' || categorybuilding_id FROM apartments;
spool off;

Как вы видите, ничего сложного, но вас скорее всего смутят заголовки, которые Oracle так охотно расставляет. Отключаем заголовки:

SET HEADING OFF;

а так же отключаем вывод о том сколько строк было в ответе

SET FEEDBACK OFF;

Соответственно данные параметры необходимо поместить перед SELECT-ом.

И так, с CSV закончили, теперь перходим к XML.

Oracle в XML

Тут я не стал ломать себе голову и воспользовался предыдущими нароботками. К сожалению под рукой не оказалось старших товарищей которые бы наставили на путь истинный, и я выдавил из себя следующее:

SET HEADING OFF;
SET FEEDBACK OFF;
spool /opt/tmp/apartments.xml;
SELECT '  <apartment>
<id>' || id || ' </id>
<numberobj>' || numberobj || '</numberobj>
<rka>' || full_rka || '</rka>
<kadno>' || kad_no || '</kadno>
<actual>' || DECODE(actual, 1, 'true', 'false') || '</actual>
<modified>' || modified|| '</modified>
<buildingid>' || building_id || '</buildingid>
<categorybuildingid>' || categorybuilding_id || '</categorybuildingid>
</apartment>' FROM apartments;
spool off;

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