Загрузка данных в формате CSV в таблицу Oracle

   Довольно часто встречается задача по загрузке данных из Excel в таблицы Oracle.  Если это разовая задача, то самый простой и быстрый способ загрузки - это SQL Loader.  Если же загрузку нужно производить через интерфейс вашего клиента, понадобится другой подход.

  Один из способов загрузки - использование пакета HTMLDB_TOOLS by Vikas

Обсуждение на Oracle.com
Пакет HTMLDB_TOOLS by Vikas

   Первым делом сохраняем путь к нашему CSV-файлу в переменную MY_FILENAME. Затем нужно дважды запустить процедуру htmldb_tools.parse_file. Первый раз запускаем без параметра "p_table_name"

htmldb_tools.parse_file(:MY_FILENAME,'MY_COLLECTION','MY_HEADINGS','MY_COLUMNS','MY_DDL');

В этом прогоне выполняются операции:

1.  Содержимое CSV-файла сохраняется в поле BLOB
2.  Выполняется конвертация BLOB -> массив varchar2
3. Создается коллекция
4. Коллекция заполняется из массива varchar2


  Затем выполняем второй запуск той же процедуры (обратите внимание на параметры):

htmldb_tools.parse_file(:MY_FILENAME,'MY_COLLECTION','MY_HEADINGS','MY_COLUMNS','MY_DDL','MYTABLE');

Т.е. добавился параметр - название таблицы для загрузки данных, MYTABLE. В этом прогоне выполняется вставка данных из коллекции в таблицу.

Этот метод загрузки показывал неплохие результаты при загрузки данных до 100тыс. строк

Отчет в формате Word с помощью Apex и BI Publisher

 
   Итак, вы получили задание: ваше Apex-приложение должно формировать отчет в формате MS Word. У отчета должна быть строго определенная шапка, колонтитул, строго оговоренные отступы. Строки должны добавляться динамически.
 
   Пример: бланк доверенности на получение материально-технических ценностей. Из таблиц БД сюда должна подгружаться информация из шапки отчета, остальную информацию оператор вводит в клиентской части программы на Application Express.

Для решения этой задачи можно использовать средства Apex по работе с шаблонами, созданными с помощью программы BI Publisher

Как установить BI Publisher, написано здесь

1. Создаем Report Query. Описываем наш запрос по которому будем выбирать данные для отчета,  указываем здесь же глобальные переменные которые должны быть доступны в этом контексте.
  Доходим до "Download Definition"

Жмем на Download, cохраняем XML - проекцию отчета в файл, назовем его my_xml_scheme. Сохраняем Report Query под именем my_report_query.


2. В MS Word  с помощью плагина BI Publisher подгружаем my_xml_scheme. Затем редактируем шапку отчета и область данных, вставляя соответствующие поля и форматируя их.
Должно получиться что-то подобное:

Сохраняем наш готовый шаблон в формате RTF, назовем его my_template.

3. В Apex создаем новый Report Layout, выбираем тип Named Columns (RTF) и затем указываем в качестве источника наш только что сохраненный RTF-шаблон my_template

Сохраняем Report Layout, назовем его test_layout

4. Открываем в Report Query сохраненный в 1)  my_report_query . В графе "Report layout" указываем test_layout  , сохраняем изменения

Теперь осталось привязать вызов нашего отчета к кнопке формы. Сделать это можно следующим образом:

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

Как сформировать сложный XLS - отчет средствами PL/SQL

  Предположим, вам поступила задача по выдаче сложного отчета с замысловатой шапкой через ваш веб-клиент. Как это сделать? Предлагаемый способ довольно прост и позволяет реализовать отчеты любой сложности, используя только PL/SQL и XML.

  Задача: Сформировать отчет на базе XLS-шаблона в веб-клиенте.
   Условия:
       1) отчет должен иметь строго определенную шапку
       2) определенные ячейки нужно заполнять динамически
       3) в отчет необходимо динамически добавлять строки

Шаблон отчета в Excel:

Решение.

  Чтобы легко манипулировать содержимым отчета,  будем использовать XML - формат. Но прежде надо немного подготовить исходный шаблон для выполнения условия 2). Чтобы легко находить и заполнять нужную ячейку новым значением, расставим в отчете "маячки". Я кодировал "маячок" как  №строки*1000000000+№заполняемой колонки.  См.пример:

   Для отображения нужного нам шаблона отчета, сохраняем наш XLS файл в формате "XML-таблица 2003". Затем открываем XML как текстовый файл и копируем все строки в поле CLOB. Например, с помощью PL/SQL Developer:

! Важно. Корректно вставляются только данные в кодировке UTF-8. Неявную перекодировку можно производить например в Блокноте (Открыли в блокноте ANSI, скопировали в буфер, вставили в таблицу).

! Важно. Копируем во вкладку Text

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

В хранимой процедуре "вытаскиваем" наш XML-шаблон в переменную:

select clob_content into myclob from t_app_clob where clobid = 1;

   Функция поиска и замены позволит нам легко выполнить условие 2) : "определенные ячейки нужно заполнять динамически". Вот примерный текст:

--------
function replaceclob(pclob clob,sourcesubstr varchar2, destsubstr varchar2) return clob is
   outclob clob;
   vpos number;
  begin
    outclob:= pclob;
    vpos := instr(outclob,sourcesubstr);
    while (vpos > 0) loop
        outclob := substr(outclob,1,vpos-1)||destsubstr||substr(outclob,vpos+length(sourcesubstr));
        vpos := instr(outclob,sourcesubstr);
    end loop;
    return outclob;
  end;
---------

Используем функцию с нашими "маячками":

begin
   myclob := replaceclob(myclob,'8000000001',v_newvalue);
end;

Таким образом заполняем все требуемые значения.  Теперь необходимо выполнить условие 3) и добавить новые строки к отчету. Для этого придется вручную "сверстать" требуемые строки в XML. Например:

--------
 myclob := myclob|| '   <Row ss:AutoFitHeight="0" ss:Height="13.6875">'||chr(10)||
    '<Cell ss:StyleID="s310"><Data ss:Type="String">'||to_char(myi)||'</Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="21" ss:StyleID="m76547168"><Data ss:Type="String">'||upper(trim(nn.fio))||'</Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="8" ss:StyleID="m76547188"><Data ss:Type="String"></Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="9" ss:StyleID="m76547208"><Data ss:Type="String"></Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="13" ss:StyleID="m76547228"><Data ss:Type="Number">'||nn.dfallper||'</Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="10" ss:StyleID="m76547248"><Data ss:Type="Number">'||nn.df1mes||'</Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="11" ss:StyleID="m76547268"><Data ss:Type="Number">'||nn.df2mes||'</Data></Cell>'||chr(10)||
    '<Cell ss:MergeAcross="9" ss:StyleID="m76547392"><Data ss:Type="Number">'||nn.df3mes||'</Data></Cell>'||chr(10)||
   '</Row>'||chr(10);
---------

Когда наш XML полностью заполнен нужными данными, надо вывести его на web-страницу. Сделаем это с помощью пакета HTP.

 Примерный код процедуры вывода текста:

-------------------
 procedure WriteToWeb( c clob ) is

  tmp varchar2(32000);
  pos number := 1;
  len number;

begin
  len := dbms_lob.getlength(c);
  len := NVL(len,0);
  -- а вот здесь задача выдачи содержимого clob через htp.p
  loop
    exit when pos > len;
    tmp := dbms_lob.substr(c, 32000, pos);
    htp.prn( tmp );
    pos := pos + 32000;
  end loop;
exception when no_data_found then
--  htp.p('данных не найдено');
  null;
end;
--------------------


Осталось пара штрихов:

    owa_util.mime_header( 'text/html', FALSE, 'utf-8' );
 -- Set the name of the file
 htp.p('Content-Disposition: attachment; filename="REPORT.xls"');
 -- Close the HTTP Header
 owa_util.http_header_close;  
-- загружаем наш отчет как вложение на веб-странице
  WriteToWeb(myclob);


Вот и все. Такой способ позволяет сформировать отчет ЛЮБОЙ сложности.   Удачной вам разработки :)

PS: Данный метод хорошо показал себя при работе с Excel - шаблонами.  Как быть если вам нужно сформировать документ MS Word -  см. следующую заметку.