вторник, 27 ноября 2012 г.

О соединении данных столбца в одну строку с помощью SQL-запроса

Постановка задачи: в некоей таблице имеется столбец с данными. Необходимо эти данные склеить в строку с помощью одного SQL-запроса.

Часть I. Аналитика и иерархия.

Простой способ решения задачи подсказал Т.Кайт в статье On Ignoring, Locking, and Parsing. Этот способ основывается на использовании аналитических функций и функции SYS_CONNECT_BY_PATH для иерархических запросов и применим начиная с Oracle9i Database Release 1. Идея заключается в следующем.
  1. Выбираемые данные необходимо разделить на подгруппы, для которых будут склеиваться строки и в каждой подгруппе пронумеровать элементы в порядке их склеивания.
      select
        deptno,
        ename,
        row_number() over (partition by deptno order by ename) rn
      from emp
  2. С помощью иерархического запроса все данные подгруппы соединяются в одну ветку дерева. Для каждого узла с помощью функции SYS_CONNECT_BY_PATH вычисляется строка со всеми предшествующими значениями поля, разделенными заданным разделителем.
    select
      deptno,
      sys_connect_by_path(ename, ' ' ) scbp
    from
      (select
         deptno,
         ename,
         row_number() over (partition by deptno order by ename) rn
       from emp
      )
    start with rn = 1
    connect by prior rn = rn-1
    and prior deptno = deptno
  3. Далее остается сгруппировать полученные данные по идентификатору подгруппы и выбрать для каждой значение строки максимальной длинны.
    select
      deptno,
      max(sys_connect_by_path(ename, ' ' )) scbp
    from
      (select
         deptno,
         ename,
         row_number() over (partition by deptno order by ename) rn
       from emp
      )
    start with rn = 1
    connect by prior rn = rn-1
    and prior deptno = deptno
    group by deptno
    order by deptno
В результате получаем список сотрудников по отделам.
   DEPTNO         SCBP
---------         ----------------------------------
       10         CLARK KING MILLER
       20         ADAMS FORD JONES SCOTT ...
       30         ALLEN BLAKE JAMES MARTIN ...
Этот способ прост и удобен, но имеет один недостаток. Функция SYS_CONNECT_BY_PATH работает с переменными VARCHAR2, которые, как известно, ограничиваются размером 4000 символов. При большом объеме склеиваемых данных, неизбежно возникает ошибка ORA-01489: result of string concatenation is too long. Эту проблему решает второй способ решения задачи.

Часть II. XML и CLOB.

Второй вариант предусматривает использование аггрегатной функции XMLAGG для получения склеенной строки в виде XML-объекта, который может быть преобразован в CLOB.
select
  deptno,
  XMLAGG(XMLELEMENT("node", ename)).getCLOBVal() scbp
from emp
group by deptno
Для упорядочивания элементов в склеиваемой строке функции XMLAGG можно добавить сортировку.
select
  deptno,
  XMLAGG(XMLELEMENT("node", ename) order by ename).getCLOBVal() scbp
from emp
group by deptno
А если возникнет необходимость отсортировать полученные склеенные строки, можно воспользоваться преобразованием типов cast.
select
  deptno,
  XMLAGG(XMLELEMENT("node", ename) order by ename).getCLOBVal() scbp
from emp
group by deptno
order by cast( substr(scbp, 1, 4000) as varchar2(4000))
В результате получится следующее:
   DEPTNO         SCBP
---------         ----------------------------------
       20         <node>ADAMS</node><node>FORD</node> ...
       30         <node>ALLEN</node><node>BLAKE</node> ...
       10         <node>CLARK</node><node>KING</node>
Недостаток данного способа – наличие тэгов, которые необходимо обрабатывать в зависимости от целей поставленной задачи. Хотя для случая, ставшего причиной поиска описанного решения, тэги оказались скорее плюсом.
Этот же метод можно применить и для более коротких строк, где не требуется использование CLOB-данных. XML-объект можно преобразовать в varchar2 с помощью функции getStringVal().
select
  deptno,
  XMLAGG(XMLELEMENT("node", ename) order by ename).getStringVal() scbp
from emp
group by deptno
order by scbp

среда, 14 ноября 2012 г.

Окно - Исходный формат отчета отличается от конечного

Итак, если создан отчет с шаблоном в формате RTF,  а вывод в виде Excel, и выпадает окошко с сообщением, что исходный формат отчета отличается от конечного, а нам это окошко надо убрать с глаз долой, тогда правим:

В регистре:
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security
новый параметр (если нету) с типом DWORD , называем ExtensionHardening - значение 0

вторник, 6 ноября 2012 г.

Использование Oracle SQL*Loader для загрузки данных в таблицы

Использование Oracle SQL*Loader для загрузки данных в таблицы

Данный минифак не претендует на полноту отражения всех особенностей работы с SQL*Loader, а служит для того, чтобы быстро войти в тему
SQL*Loader — специальная утилита Oracle сервера для загрузки данных в БД из локальных файлов с данными. Для того чтобы воспользоваться ее функционалом создадим несколько файлов, которые будем использлвать в работе, и положим их в папку C:\oracle\ora90\BIN\load (в общем случае):

  • data.dat или data.txt — собственно файл с данными. Пусть он будет представлять строки данных разделенных сиволом перевода строки.
  • load.ctl — контрольный файл — содержит инструкции по загрузке данных в БД, в нашем случае будет иметь вид:
    LOAD DATA -- Загружаем данные
    INFILE 'data.txt'  "str '\n'" -- из файла data.txt, разделитель строк str '\n' - символ перевода строки
    INTO TABLE load_data -- в таблицу load_data
    FIELDS terminated BY ',' OPTIONALLY ENCLOSED BY '"'
    (DATA char) -- название столбца и тип данных в таблице load_data
    То что мы не используем, но встречается часто: fields terminated by ‘,’ означает, что границы данных определены по символу «,», а optionally enclosed by ‘»‘ определяет, что данные могут содержать символ обрамления.
  • load.bat — пакетный файл (англ. batch file) выполняющий запуск sqlldr.exe имеет вид:
    SET nls_lang=russian_cis.ru8pc866 -- устанавливаем нужную кодировку
    sqlldr.exe userid=user_name/user_pass@test control=LOAD.ctl errors=100 bad=DATA.bad -- test — строка связи*, load.ctl — контрольный файл, data.bad — файл плохих записей
    Строка связи — это та строка, которая должна быть прописана в ORA Network Configuration File: D:\oracle\ora90\network\admin\tnsnames.ora (в общем случае). Errors=100 — количество допустимых ошибок (по умолчанию 50).
  • Далее находим нашу папку C:\oracle\ora90\BIN\load, находим файл load.bat, запускаем его и любуемся на результат:
    SELECT * FROM load_data;
    Пока все, пишите об ошибках и неточностях.
Пара полезных ссылок по теме:
Примечание:
Интересно, что при сохранении *.xslx в «.csv с разделителем запятой» разделителем на самом деле становится точка с запятой.

Разбор/парсинг значения поля (строки) по разделителям на строки

Источник:  http://plsqlbook.ru/category/question-answer/


Простой пример того, о чем речь:
SELECT regexp_substr(str, '[^#]+', 1, LEVEL)
  FROM (SELECT 'one#two#tree' str FROM dual)
CONNECT BY instr(str, '#', 1, LEVEL-1) > 0
напоминалка:
  • ‘[^#]+’ такая маска найдет все символы кроме решетки
  • instr(str, ‘#’, 1, LEVEL-1) > 0 поиск позиции разделителя начиная с первой позиции и, видимо, с LEVEL = 2 (возвратит 0 если позиция при заданных параметрах не найдена)
  • CONNECT BY условие подсказывает ораклу как долго продолжать цикл
Результат:
1 one
2 two
3 tree
 
 
Вариант 2
 
select 
   ltrim(rtrim(
    substr(s_element,
         instr(s_element,',',1,iter.pos)+1,
         instr(s_element,',',1,iter.pos+1)-
         instr(s_element,',',1,iter.pos)),',')) s_element,iter.pos
from 
     (select ','||s_in||',' s_element from dual) csv,
     (select rownum pos from ra_customer_trx_all) iter
where 
     iter.pos <= ((length(csv.s_element)-length(replace(csv.s_element,',')))/length(','))-1 

где  s_in  - исходная строка

пятница, 19 октября 2012 г.

Создание шаблонов в формате Excel

Этот пост о том как создавать отчеты, выводящиеся в Excel с использованием XML Publisher.

На проекте необходимо было создать отчет в Excel. Проблема состояла в том, что в шаблоне было около 100 столбцов - поэтому вариант с созданием шаблона в RTF и последующим его выводом в виде Excel отпала сразу. Вариант с преобразованием шаблона в формате Excel в книгу XML и последующим выводом тегов из пакета тоже отпал - из-за настройки базы и аппсов русские символы, генерируемые в пакете, в итоге выводились в виде кракозябок. Выход был найден.

1. Создается шаблон в  Excel. В поля, в которых хотим сделать вывод переменных пишем :PARAM_NAME:   - ОБЯЗАТЕЛЬНО с двух сторон двоеточия - точки входа и выхода.



2. Сохраняем документ в формате книги XML

3. Разделяем наш файл по строкам на неизменяемые части - шапка, подвал, и на изменяемые - те что будут выводиться в цикле и заполняться данными.

4. Создаем таблицу, в которой будем хранить уникальный идентификатор нашего отчета, уникальный идентификатор части отчета (шапки, подвала, изменяемой части), описание этой самой части и поле типа CLOB для хранения куска XML с этой самой частью.



Чтобы не возникло проблем с кодировкой, правим:
<?xml version="1.0" encoding="windows-1251"?> 

Чтобы не выдавало ошибок при генерированиии большого количества строк ( по умолчанию часть  ExpandedRowCount равна около 300 строк), правим:
<Table ss:ExpandedColumnCount="19" ss:ExpandedRowCount="65000" x:FullColumns="1"

5. В пакете производим вывод нужных частей

    /*  Сводный отчет */
  PROCEDURE SVODNIJ(P_ORG_ID NUMBER, P_YEAR VARCHAR2, P_TYPE VARCHAR2,  P_DATA_VALUE VARCHAR2, P_VARIANT VARCHAR2) IS
    body_  clob;
  BEGIN
 
    -- Вывод шапки отчета
    body_ := xx_utils.get_xml_body(145, 11);
    body_ := xx_utils.reps(':FILIAL:',GET_ORG_NAME(P_ORG_ID), body_);
    body_ := xx_utils.reps(':YEAR:', GET_YEAR(P_YEAR), body_);
    xx_utils.put_line(body_);


________________________________________

 function get_xml_body(p_report_id number, --ID отчета
                        p_body_id   number--ID строки отчета
                        ) return clob as
    xml_clob clob;
  begin
    select t.xml_body
      into xml_clob
      from xx_reports t
     where t.report_id = p_report_id
       and t.body_id = p_body_id;
 
    return xml_clob;
  exception
    when others then
      return null;
  end get_xml_body;

__________________________

 function reps(var in varchar2, --Что заменяется
                val in varchar2, --На что заменяется
                sou in clob --Источник замены
                ) return clob as
  begin
    --return replace(sou, var, convert(val, 'UTF8', 'CL8MSWIN1251'));
    return replace(sou, var, val);
  end;



_____________________________


Для вывода я создавала параллельную программу, с исполняемым файлом - процедурой из пакета. При отработке запроса в файле результатов формируется xml документ и ОЕБС автоматически открывает его в Excel, таким образом получая нужный нам вид отчета.

пятница, 12 октября 2012 г.

Открытие периодов в запасах

1. Полномочия "Запасы" - Цикл закрытия учета - Периоды учета запасов
2. Полномочия "Суперпользователь закупок" - Настройка - Финансы - Учет - Открыть и закрыть периоды