Из PL/SQL в Excel за деньги?! Ну уж неееет!!!....

Привет всем страждущим знаний! (хотя кого я обманываю - тут кроме меня и нету никого)

Ковырялся тут в интернетах за нуждой и случайно наткнулся на некий эстонский сайтик, расположенный по адресу https_//www_oraexcel_com/ по скандинавски аккуратно, но безлико сделанный в material дизайне и предлагающий всем желающим за €820 пакет для работы с xlsx файлами офисного пакета. Стало интересно,  чего там такого на €820 понаписано, попрыгал по примерам. Оказалось - ничего, оригинального там нет. Минуты через две, когда думал уже закрывать и забывать, вдруг вспомнилось, что у меня тоже есть очень похожий пакет для работы с xlsx из PL/SQL совершенно без СМС, рекламы и регистрации. 

Конечно, скажу сразу, этот пакетик не мой, а очень уважаемого мною нидерландского разработчика Anton Scheffler, с которым я когда-то давным-давно списывался и просил разрешения пользоваться этим пакетов в своих системах, на что он мне ответил согласием, да и пакет то, собственно доступен публично, но только почему-то мало известен, а если и известен, то под именем AS_XLSX.

Функционал AS_XLSX схож с функционалом вышеописанного пакета ORA_EXCEL от эстонцев, но, видимо, у коммерческого пакетика бантиков побольше, особенно не разбирался. Главное, что основную свою функцию AS_XLSX выполняет - генерирует полноценные *.XLSX файлы со стилями и прочими ништяками и способен сохранять их в виде файлов или BLOB объектов. Так как исходный код открыт, то нет никаких проблем с модификацией данного пакета не возникает.

Ссылка на пакет AS_XLSX от автора: https://technology.amis.nl/languages/oracle-plsql/create-an-excel-file-with-plsql/

Есть также альтернативное тоже совершенно бесплатное решение с исходным кодом Alexandria (сам не пользовался): https://github.com/mortenbra/alexandria-plsql-utils

ORACLE APEX также может использоваться для генерации pdf документов, но я не тестировал ни интерфейсы, ни производительность OA, так что ничего внятного сказать по этому поводу не могу.

Что же касается меня, то могу сказать, что я реализовал несколько вариантов работы с PDF документами в ORACLE:

1) я взял за основу пакет AS_XLSX и модифицировал и расширил его нужными мне методами для рисования схем. Меня не устроила конечная скорость пакета, т.к. мне приходится создавать десятки тысяч документов за сутки.

2) После первой попытки я взял за основу java библиотеку PDFClown, загрузил ее в БД и реализовал интерфейсы, позволяющие мне работать с документами на бекенде. Меня бы все устроило, если бы не одна неприятная особенность бекендной JVM, а именно то, что она деградирует многопоточную обработку, вытягивая все потоки в конвейер, т.е. сводит на нет все усилия по распараллеливанию процессов. Сначала я думал расстроиться, но потом вчитался в док и понял, что некая логика в этом есть, т.к. основная задача JVM в данном случае, не завалить сервер. Тут уж спору нет, пришлось от такой реализации также отказаться, потому что распараллеливать процессы на уровне сессий мне не очень удобно, т.к. я в многопоточном режиме создаю PDF документы, а в процессе работы каждый из потоков создает некий объект контроллер, который в свою очередь генерирует десятки потоков, подгружающих в документ изображения с сервера изображений.

3) Я выгрузил функционал из БД и реализовал daemon app, которое установлено на отдельном хосте и выполняет нужные мне операции, загружая сгенерированные документы в базу данных. Такая реализация на данный момент меня вполне устраивает, но я на всякий случай заложил возможность сборки таких хостов в кластер при нехватке производительности. В настоящее время я тестирую производительность различных open source или просто freeware библиотек, подбирая наиболее производительную и менее требовательную к памяти, т.к. тут к PDFClown появились некоторые вопросы. В основном по использованию кириллицы в документах, а также при работе с аннотациями в PDF документах

Вот такая незатейливая история длиной в 3 ковидных года. :)

Из неопубликованного. Забытые regexp этюды для работы со строковым представлением дробных чисел со степенью вида [+/-]NNN[.MMM[E[+/-]PPP]]

    Точно не помню, зачем это мне было нужно, но теперь, по прошествии нескольких лет, выглядит просто интересно. Насколько я помню, была у меня попытка написать регулярное выражение, которое могло бы обработать строку и определить, корректно ли написано число, чтобы не возникала исключительная ситуация Дополнительно к этому для сравнения написал функцию isNumber(), которая проверяла бы корректность написания числа с помощью вызова to_number() с гашением исключительных ситуаций.

with  

    function isNumber(p_String in varchar2) return number is 

      n number; 

    begin 

      n := to_number(p_String); 

      return 1; 

    exception when others then return 0; 

    end; 

 

select * from ( 

    with 

        q_pattern as ( 

            select '^'|| 

            '([-+]?)'|| 

            '('|| 

                '([[:digit:]]+[.,]?)(E[-+]?[[:digit:]]+)?|'|| -- целое число с научной нотацией 

                '([[:digit:]]*([.,][[:digit:]]+))(E[-+]?[[:digit:]]+)?'|| -- дробное число с научной нотацией 

            ')$' pattern from dual 

        ), 

        q_nums as (select '.1,1.,.,1.E1,E2,1,-1,.1E1,.1E-1,-.1E+1,1.,-1.,.E3,.0E3, -.0E3, 1.23847,-1.23847,22E2, 22E-2, 22F2, 22E2.2, 1L,0x12, .1112E-1,-.1E-10, 123.2344E1, 123.2344E11.1' num_list from dual), 

        q_tab as (select regexp_substr(num_list, '\s*([^,]+)\s*',1,level,'i',1) num from q_nums connect by regexp_substr(num_list, '[^,]+',1,level) is not null) 

    select 

        num, 

        case when regexp_like(num, pattern) then 'Ok' else 'Fail' end by_regexp_like, 

        case when isNumber(num) = 1 then 'Ok' else 'Fail' end by_to_number 

    from 

        q_tab 

        cross join q_pattern 

) t

Токенизация строки с помощью xmltable

    Посмотрел я свой бложик ретроспективно... Блин, столько лет работы, а в нем про одно разбиение по разделителю да работа с CSV. Уныло как-то стало. А потом задумался, - но ведь это же довольно значимая часть работы любого разработчика баз данных. Мы проводим часы и дни в поисках оптимальных решений для обработки данных, выискивая наилучшие подходы и поэтому выбор инструментов у нас по любой проблеме (даже такой, казалось бы, примитивной, как токенизация) должен быть достаточно широк. 
    Поэтому решил добавить еще один удобный вариант разбиения на токены, который используется у нас при работе с фронтендом. При данном подходе, разумеется, ограничена длина списка токенизируемых значений, поэтому применять нужно с оглядкой на возможную максимальную длину входной строки. 

 

select 

    xt.item 

from 

    xmltable(

        'for $text in tokenize($input, ",") return $text' 

        passing 'Северный Кавказ,Волга-Север,Северо-Запад' as "input" 

        columns item varchar2(4000) path '.'

    ) xt

Вывод:

ITEM

----------- 

Северный Кавказ

Волга-Север

Северо-Запад

PL/SQL парсер для CSV данных, находящихся в CLOB-переменной (в соотв. с RFC 4180)

Ну, вот и снова пришлось написать что-то набившее оскомину. На этот раз - очередной парсер для CLOB-переменной, содержащей CSV данные, сохраненные в формате Excel. Порылся в stackoverflow, ничего толкового не нашел, поэтому понял, что быстрее написать самому. Парсит достаточно большие файлы. У меня есть образец на 9Мб который достаточно быстро обрабатывается, разумеется без вставки в БД, просто с выводом в output. Потом, подумал, что надо бы сделать пример и потом самому к нему обращаться при необходимости, чтобы не рыться в каталогах. 

Итак, 

- входное значение указано в CLOB-переменной c. 

- в качестве разделителя значений используется ";"

- символ экранирования многострочных значений - двойная кавычка, 

- символ двойной кавычки - двойная двойная кавычка

- cимвол новой строки - 0x0A (10)

- результат выводится в output буфер

declare
    -- CLOB value for tests, separator is semicolon, has been copied from file saved as Windows-1251 CSV worksheet by Excel 2019
    c clob := '"1";"Serge;
George
Galina";3
""2"";Tatiana;3
3;"Nikolas
""Maxim""
Lisa";3
4;;3
5;"Daria;";3
6;""Leo"";3
7;Alexey;""3""
8;Ksenia;"3"';
    -- special characters
    separator       constant char(1) := ';';   -- semicolon separator
    quote_char      constant char(1) := '"';  -- quoting character
    new_row         constant char(1) := chr(10); -- new row character
    -- output cell value as a comment
    procedure cell_out(prow in number, pcol in number, pval in varchar2) is
    begin
        dbms_output.put_line('Cell ('||prow||', '||pcol || ') = '|| nvl(replace(pval,new_row,'<\n>'), '<null>'));
    end;
    -- CSV parser
    procedure parse_csv_clob (p_clob in out nocopy clob) is
        offset          number := 1;
        amount          number := 1;
        total           number := nvl(dbms_lob.getlength(p_clob),0);
        read_buffer     varchar2(1);
        read_buffer1    varchar2(1);
        cell_buffer     varchar2(32767);
        row_index       pls_integer := 1;
        col_index       pls_integer := 1;
        quoting         boolean := false;
    begin
        if ( dbms_lob.isopen(p_clob) != 1 ) then dbms_lob.open(p_clob, 0); end if;
        loop
            exit when ( offset > total );
            dbms_lob.read(p_clob, amount, offset, read_buffer); -- read the symbol
            if read_buffer = new_row and not quoting then -- new row
                cell_out(row_index, col_index, cell_buffer);
                row_index := row_index + 1;
                col_index := 1;
                cell_buffer := null;
            elsif read_buffer = separator and not quoting then -- new cell
                cell_out(row_index, col_index, cell_buffer);
                cell_buffer := null;
                col_index := col_index + 1;
            elsif read_buffer = quote_char then -- quoted value or a escaped double quote symbol (if next symbol is the same)
                if offset < total then dbms_lob.read(p_clob, amount, offset + 1, read_buffer1); else read_buffer1 := null; end if;
                if read_buffer1 is null or read_buffer1 != quote_char then quoting := not quoting;
                else 
                    cell_buffer := cell_buffer || quote_char;
                    offset := offset + 1;
                end if;
            else -- add any other symbols to a current cell value
                cell_buffer := cell_buffer || read_buffer;
            end if;
            offset := offset + 1;
            if (offset > total and not quoting) then
                if length(cell_buffer) > 0 then
                    cell_out(row_index, col_index, cell_buffer);
                end if;
            end if;
        end loop; 
        if ( dbms_lob.isopen(p_clob) = 1 ) then dbms_lob.close(p_clob); end if; 
        if quoting then raise_application_error(-20001, 'Closing quote character has not been found.'); end if;
    exception
        when others then
            dbms_output.put_line('CSV-PARSER-ERROR: '||sqlerrm);
    end;
begin
    parse_csv_clob(c);
end;