Записки жертвы высоких технологий

    Здравствуйте! Мой блог посвящен в большей своей части моей профессиональной деятельности на поприще использования технологий ORACLE для разработки баз данных OLTP и OLAP хранилищ данных. В заметках я периодически размещаю разнообразные SQL, PL/SQL и Java скрипты написанные мной и не только мной, ссылки на интересные источники в сети либо другую полезную информацию, которая каким либо образом касается моей работы с замечательными продуктами компании ORACLE.
    Вы можете связаться со мной по контактному емейлу, если у вас есть какие-либо вопросы, связанные с разработкой баз данных на основе продуктов ORACLE, буду рад помочь вам, если это будет в моих силах.

2 апр. 2025 г.

Разбиение (split) строки на части по длине с помощью SQL

Попросили меня выполнить code-review скрипта, сгенерированного AI. Скрипт написан на PL/SQL, и выполняет следующие действия:

- во входной строке ищет и заменяет различные написания слова "строение", например "с.", "ст.", "стр-е" итп и заменяет на стандартизированное написание "стр.",

- затем разбивает полученную строку на части по набору разделителей (в данном примере это "," и ";")

- полученный набор строк дополнительно разбивается на подстроки по длине в 10 символов

Вот исходный код:

DECLARE
original_text VARCHAR2(4000) := 'Это строение, которое находится рядом со ст. 5; также есть стр-е в центре города. Это очень важное стро.';
modified_text VARCHAR2(4000);
chunk_size INTEGER := 10;
start_pos INTEGER := 1;
delimiter VARCHAR2(10) := ',;'; -- Разделители
current_chunk VARCHAR2(4000);
BEGIN
-- Заменяем слова
modified_text := REPLACE(original_text, 'строение', 'стр.');
modified_text := REPLACE(modified_text, 'стр-е', 'стр.');
modified_text := REPLACE(modified_text, 'стро.', 'стр.');
modified_text := REPLACE(modified_text, 'ст.', 'стр.');
DBMS_OUTPUT.PUT_LINE('Измененный текст: ' || modified_text);
-- Если строка больше 10 символов, делим ее на части
IF LENGTH(modified_text) > chunk_size THEN
-- Разделяем строку по разделителям
FOR chunk IN ( SELECT REGEXP_SUBSTR(modified_text, '[^'||delimiter||']+', 1, LEVEL) AS part FROM dual CONNECT BY REGEXP_SUBSTR(modified_text, '[^'||delimiter||']+', 1, LEVEL) IS NOT NULL ) LOOP current_chunk := TRIM(chunk.part); SELECT REGEXP_SUBSTR(modified_text, '[^'||delimiter||']+', 1, LEVEL) AS part
FROM dual
CONNECT BY REGEXP_SUBSTR(modified_text, '[^'||delimiter||']+', 1, LEVEL) IS NOT NULL
) LOOP
current_chunk := TRIM(chunk.part);
start_pos := 1; -- Делим текущую часть на подстроки по 10 символов
WHILE start_pos <= LENGTH(current_chunk) LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(current_chunk, start_pos, chunk_size));
start_pos := start_pos + chunk_size;
END LOOP;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE(modified_text);
END IF;
END;
/

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

А вот как  можно изменить этот код, чтобы сразу было понятно, что его писал не AI и не начинающий разработчик? Вот я и задумался, а как бы написал такой код я. Вот что у меня в итоге получилось путем нескольких итераций:

declare
    original_text varchar2(4000) := 'Это строение, которое находится рядом со ст. 5; также есть стр-е в центре города. Это очень важное стро.';
    modified_text varchar2(4000);
    chunk_size INTEGER := 10;
    delimiter     varchar2(10) := ',;'; -- разделители

begin
    dbms_output.put_line('Исходный текст  : ' || original_text);
    -- заменяем слова
    modified_text := regexp_replace(srcstr     => original_text,
                                pattern    => '(\W|^)(строение|стр-е|стро.|ст.)(\W|$)',
                                replacestr => '\1стр.\3',
                                position   => 1,
                                occurrence => 0,
                                modifier   => 'i');
    dbms_output.put_line('Измененный текст: ' || modified_text);

    -- если строка больше 10 символов, делим ее на части
    for chunk in (
        with q_chunks as (
            select rownum rn, regexp_replace(regexp_substr(modified_text, '[^'||delimiter||']+', 1, level), '(^\s+|\s+$)', null) t 
            from dual 
            connect by level <= regexp_count(modified_text, '[^'||delimiter||']+'))
       
select distinct chunk.rn, level, regexp_substr(chunk.t, '.{1,'||chunk_size||'}', 1, level) t
        from q_chunks chunk
        connect by regexp_substr(chunk.t, '.{1,'||chunk_size||'}', 1, level) is not null
        order by rn, level

    ) loop
       dbms_output.put_line(chunk.t);
    end loop;
end;
/

    Во первых, очевидно, что заменять различные написания на нечто стандартное - это вотчина регулярных выражений, непонятно, почему AI решил не пользоваться ими. Тем более, что очевидно, что требуется менять только отдельные слова, но не части слов, что нельзя сделать обычными сериями REPLACE. Т.е. исходный код уже логически неверен. Ну ладно, идем дальше. 
    Часть кода, которая делит текст по разделителям, в принципе работает корректно, причем тут как раз и использованы регулярные выражения. Я фактически оставил ее без изменений, только добавил вызов regexp_replace для удаления пробельных символов спереди и сзади порезанных частей строк. Непонятно, почему в исходном коде это действие вынесено в отдельный вызов TRIM, который удаляет только пробелы. 
    Далее начинается самое интересное, собственно тема поста. Я задумался - можно ли используя SQL разбить текст на строки по длине. Оказалось, что да - можно это сделать используя известный прием с select ... from dual connect by ...
Таким образом можно совместить шаг разбиения строки по разделителям с шагом разбиения подстрок по длине. Тут хотелось бы заметить, что у меня, как всегда, возникли некоторые трудности с увязыванием обоих иерархических запросов, поэтому не взыщите за использование rownum, distinct и order by, предлагаю списать это на небрежность написания примера.
    В целом результат моего code review - оптимизация предложенного кода, устранение логических ошибок, минимизация переключения контекстов выполнения, а также использование иерархического SQL для разбиения строки на подстроки по длине. Для более ясного понимания прикладываю простейший пример по теме с нарезкой строки по 5 символов с помощью SQL:

with q_data_to_split as (select '0123456789abcdefghijklnmopqrstuvwxyzABCDEFGHIJKLNMOPQRSTUVWXYZ' t, '.{1,5}' p from dual)
select regexp_substr(t, p, 1, level) t
from q_data_to_split
connect by regexp_substr(t, p, 1, level) is not null

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

declare
    t varchar2(4000) := '0123456789abcdefghijklnmopqrstuvwxyzABCDEFGHIJKLNMOPQRSTUVWXYZ';
    chunk_size    integer := 5;
    start_pos integer := 1;
begin
    WHILE start_pos <= LENGTH(t) LOOP
        DBMS_OUTPUT.PUT_LINE(SUBSTR(t, start_pos, chunk_size));
        start_pos := start_pos + chunk_size;
    END LOOP;
end;

12 дек. 2024 г.

Передача списка значений в качестве значения переменной в процедуру PL/SQL из клиентского приложения

    Когда я только начинал писать на Java и C#, то всегда мучался с выдумыванием различных вариантов для передачи списков в процедуры PL/SQL из клиентского слоя. Понятное дело, что на уровне клиента всегда несоизмеримо проще сформировать некий документ в транспортабельном формате, чтобы потом обработать его в БД, для этого очевидно можно и нужно использовать готовые форматы XML или JSON, не выдумывая ненужных велосипедов, но принципы эффективного парсинга сформированных таким образом документов не всегда очевидны. 

    Со временем у меня сформировалась и устоялась некоторая технология, которая дает возможность неплохо унифицировать работу с различными наборами параметров при их формировании и при обработке на уровне PL/SQL. Собственно сложность в основном заключается в парсинге переданных значений на уровне PL/SQL. Именно поэтому я изначально и уделяю достаточно много времени тому, чтобы эффективно разбивать переданные строки на значения без выполнения сложных операций строковой обработки

Вот достаточно простой пример. Передача некоего списка значений в процедуру, где каждое из передаваемых значений является объектом (т. е. имеет свой собственный набор атрибутов помимо значения, например порядковый номер или что-то подобное). Задачу имеет смысл разбить на несколько этапов:

1 этап - сериализация списка в xml формат на уровне клиента, выполняется достаточно просто за счет использования базовых интерфейсов в используемом языке

2 этап - передача значения в параметр процедуры. Тут возникает только один вопрос, какой тип использовать для передачи параметра. Как правило я использую varchar2 параметры для передачи небольших списков и CLOB параметры, если длина списка непредсказуема либо точно превышает 4000 байт

3 этап - парсинг переданного списка в PL/SQL. Как вариант, можно использовать встроенную поддержку XML 

with q_xml as (
select 
        xmltype('<ordering>
            <person id = "1" checkout="1799-12-01">George Washington</person>
            <person id = "2" checkout="1826-12-01">John Adams</person>
            <person id = "3" checkout="1826-12-01">Tomas Jefferson</person>
            <person id = "4" checkout="1836-12-01">James Medisson</person>
            <person id = "5" checkout="1831-12-24">James Monroe</person>
        </ordering>') xml_data from dual
    )
select xt.id, xt.val, xt.checkout
from 
    q_xml x, 
    XMLTABLE('/ordering/person'
             PASSING x.xml_data
             COLUMNS 
               val     VARCHAR2(4000)  PATH '.',
               id     number  PATH '@id',
               checkout     date  PATH '@checkout'
             ) xt

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

18 мая 2023 г.

Из 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 ковидных года. :)


10 мар. 2023 г.

Из неопубликованного. Забытые 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
----------- 
Северный Кавказ
Волга-Север
Северо-Запад

7 мар. 2023 г.

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;

30 окт. 2020 г.

Нумерация групп при разбиении результирующего набора на части по значению в одном из столбцов

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

На вход попадают следующие данные об адресах ADDRID и уникальных точках привязки к этим адресам IDX. Считаем, что сортировка данных проводится вне системы, но требуется сохранить естественный порядок поступления данных. Некоторые строки не имеют привязок адреса к точкам, такие строки считаем разделителями групп привязок точек к адресу:

ADDRID    IDX
999   1
999   2
999
999   4
999   5
999
999   7
          ... 

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

ADDRID    IDX    GROUPID
999   1      1
999   2      1
999
999   4      2
999   5      2
999
999   7      3
          ...  

Как вариант результата, NULL значения можно тоже относить к какой-либо группе, но это практически не меняет логику написания самого запроса. Как же написать такой запрос. 

q_data: Имитируем источник исходных данных, отдающий данные с учетом собственной сортировки (согласно начальным требованиям задания).

q_data_indexed: Подразумевается, что мы не можем управлять порядком следования данных в источнике, да в нем, очевидно, отсутствует тот столбец, по которому можно упорядочить данные. Поэтому, прежде всего нужно закрепить порядок поступаемых данных, для чего применяем либо ROW_NUMBER() OVER (ORDER BY <...выражение дополнительной сортировки>) RN, либо используем обычный ROWNUM. Также создаем столбец NULLC для выделения значением 1 тех строк, по которым происходит разбиение на группы, в нашем случае это те строки, в которых значение IDX IS NULL

q_data_grouped: В принципе, после выполненных выще действий остается только произвести суммирование единичек в столбце NULLC с помощью аналитической функции SUM() OVER(ORDER BY...) согласно закрепленному порядку по столбцу RN, чтобы получить нарастающий итог в столбце GROUPID. Каждое значение NULLC = 1 будет добавлять 1 в индекс группы GROUPID, а нули в NULLC не будут добавлять в него значение.

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


with q_data as (
    select 999 addrid, 1 idx from dual union all 
    select 999 addrid, 2 idx from dual union all 
    select 999 addrid, null idx from dual union all 
    select 999 addrid, 4 idx from dual union all 
    select 999 addrid, 5 idx from dual union all 
    select 999 addrid, null idx from dual union all 
    select 999 addrid, 7 idx from dual),
q_data_indexed as (
    select addrid, idx, rownum rn, case when idx is null then 1 else 0 end nullc from q_data),
q_data_grouped as (
    select addrid, idx, sum(nullc) over(order by rn) + 1 groupid from  q_data_indexed)
select addrid, idx, case when idx is null then null else groupid end groupid from q_data_grouped

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


with dat as (
    select 1 as ord, 99 adrid, 1 as id from dual union all
    select 2 as ord, 99 adrid, 2 as id from dual union all
    select 3 as ord, 99 adrid, null as id from dual union all
    select 4 as ord, 99 adrid, 3 as id from dual union all
    select 5 as ord, 99 adrid, 4 as id from dual union all
    select 6 as ord, 99 adrid, null as id from dual union all
    select 7 as ord, 99 adrid, 5 as id from dual union all
    select 8 as ord, 99 adrid, 6 as id from dual),
l as (
    select listagg(nvl(to_char(id),'*'),',') within group(order by ord) line from dat),
parts as (
    select level as gr ,trim(',' from regexp_substr(line, '[^*]+',1,level)) as p from l connect by regexp_substr(line, '[^*]+',1,level) is not null)
select 
    t.*, p.gr 
from 
    dat t
    left join parts p on t.id in (select regexp_substr(p.p, '[^,]+',1,level) from dual connect by regexp_substr(p.p, '[^,]+',1,level) is not null)

12 июл. 2019 г.

Работа со значениями типа LONG длиной до 4000 байт без использования PL/SQL

Как известно, со значениями типа LONG работа в ORACLE не особенно приветствуется. Точнее не приветствуется до той степени, что поддержка типа LONG в SQL практически отсутствует и значения для обработки могут быть обработаны только средствами клиентского ПО либо средствами PL/SQL.
Тем не менее, существует достаточно известный способ, с помощью которого все же можно воспользоваться значениями типа LONG непосредственно в SQL. Единственное ограничение этого способа - максимальная длина значений типа LONG должна быть не больше максимальной разрешенной длины SQL типа VARCHAR2, т.е. не более чем 4000 байт.
Приводимый мной пример в принципе дает понимание этого метода. Мне нужно было достать из системного словаря информацию о максимально возможных значениях ключа партиционирования таблицы по каждой из имеющихся партиций.

select partition_name, high_value from user_tab_partitions where table_name = '<ИМЯ_ТАБЛИЦЫ>'


Запрос крайне прост, но в SQL невозможно без определенных процедур воспользоваться значением HIGH_VALUE, так как оно имеет тип LONG. По "классике" нужно написать PL/SQL процедуру, которая преобразует тип LONG в VARCHAR2 по ROWID строки, но этот метод достаточно трудоемок и приводит к тому, что запрос при выполнении начинает переключать контекст выполнения с SQL в PL/SQL и обратно, что накладно.

Альтернативный метод таков:

with q_xml as (select dbms_xmlgen.getxmltype('select partition_name, high_value from user_tab_partitions where table_name = ''<ИМЯ_ТАБЛИЦЫ>''') xml from dual)
select
    z.*
from
    q_xml,
    xmltable('/ROWSET/ROW' passing q_xml.xml columns partition_name varchar2(64) path 'PARTITION_NAME', high_value varchar2(1024) path 'HIGH_VALUE' ) z

Запрос, конечно, становится более сложным, чем исходный, но тем не менее отпадает потребность в написании PL/SQL функции.

23 мая 2019 г.

Разбиение CSV-значений в таблице на элементы

Много я ковырялся с разбиением строк на токены пока не натолкнулся на интересную задачку, связанную с разбиением CSV-значения на элементы и cross join полученных элементов с исходной записью. Для начала реализовал ее с помощью XMLTable, но такой способ требует подготовки исходного значения для использования в XMLTable, т.к. каждый элемент списка должен быть помещен в кавычки, например использование значения 'a,b,c,d,e' невозможно, но '"a","b","c","d","e"' вполне подходит. К сожалению, не всегда возможно изменять строку перед такой операцией, например из-за лимита длины буфера, как в моем случае. Я решил поискать альтернативные решения, которые позволяли бы выполнять cross join элементов без изменения исходной строки. В конце концов нашел на asktom.oracle.com прекрасный вариант, который меня полностью удовлетворил:


SQL> CREATE TABLE MY_TABLE(

  2  R_ID int,
  3  R_Site varchar(20),
  4  R_Name varchar(20),
  5  R_Role varchar(20)
  6  );

Table created.

SQL>
SQL> INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
  2  VALUES ('1' , '123,-456,-789', 'qwer' , 'Owner');

1 row created.

SQL>
SQL> INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
  2  VALUES ('2' , '56,-741,-852', 'qaz' , 'Manager');

1 row created.

SQL>
SQL>
SQL> select
  2    t.r_id,
  3    t.r_name,
  4    t.r_role,
  5    regexp_substr(t.R_Site, '[^,]+', 1, commas.column_value)  as site
  6  from
  7    MY_TABLE t,
  8    table(cast(multiset(select level from dual connect by  level <= regexp_count(t.R_Site, '[^,]+')) as sys.OdciNumberList)) commas;

      R_ID R_NAME                                                       R_ROLE                                                       SITE
---------- ------------------------------------------------------------ ------------------------------------------------------------ --------------------
         1 qwer                                                         Owner                                                        -456
         1 qwer                                                         Owner                                                        -789
         1 qwer                                                         Owner                                                        123
         2 qaz                                                          Manager                                                      -741
         2 qaz                                                          Manager                                                      -852
         2 qaz                                                          Manager                                                      56

6 rows selected.


Единственное, что я сделал с найденным примером, так это заменил невнятное выражение в CONNECT BY на приведенный в примере выше вызов regexp_count(...) и убрал секцию ORDER BY из-за зависания сессии.

30 окт. 2018 г.

Подсчет места, занятого объектами схем(ы) на дисках

with
    -- список схем через запятую и шаблон поиска
    q_schema_names_list as (select '&schema_owners' list, '\s*([^,]+)\s*' pattern from dual),
    -- таблица со схемами
    q_schema_names as (select upper(regexp_substr(list,pattern,1,level,'i',1)) schema_name from q_schema_names_list connect by regexp_substr(list,pattern,1,level,'i',1) is not null )
-- результат
select
    owner$ "Владелец",
    tablespace_name "Табличное пространство",
    segment_name "Сегмент",
    segment_type "Тип сегмента",
    master_object "Объект владедец",
    sum_size$ "Размер объекта"
from (
    -- приводим значения в удобочитаемый вид
    select
        case when grouping$type = '1111111' then 'ИТОГО по запросу' when grouping$type = '0111111' then 'Итого по схеме '|| owner else owner end owner$,
        tablespace_name,
        segment_name,
        segment_type,
        master_object,
        case
            when (tt.sum_size / 1024 / 1024 / 1024 / 1024) > 1 then to_char(tt.sum_size / 1024 / 1024 / 1024 / 1024 
/ 1024 ) || ' PB'
            when (tt.sum_size / 1024 / 1024 / 1024 / 1024) > 1 then to_char(tt.sum_size / 1024 / 1024 / 1024 / 1024) || ' TB'
            when (tt.sum_size / 1024 / 1024 / 1024) > 1 then to_char(tt.sum_size / 1024 / 1024 / 1024) || ' GB'
            when (tt.sum_size / 1024 / 1024) > 1 then to_char(tt.sum_size / 1024 / 1024) || ' MB'
            when (tt.sum_size / 1024) > 1 then to_char(tt.sum_size / 1024) || ' KB'
            else to_char(tt.sum_size) || ' B'
        end sum_size$,
        case when grouping$type = '1111111' then 10E6 when grouping$type = '0111111' then 10E6-1 else rn end rn$
    from (
        -- rollup запрос с подсчетом суммарных размеров
        select
            owner,
            tablespace_name,
            segment_name,
            segment_type,
            master_object,
            bytes,
            rn,
            grouping(owner)||
            grouping(tablespace_name)||
            grouping(segment_name)||
            grouping(segment_type)||
            grouping(master_object)||
            grouping(bytes) ||
            grouping(rn)grouping$type,
            sum(bytes) sum_size
        from (
            -- перечень интересующих нас сегментов по списку схем, сортируем список по убыванию занимаемого объектами размера
            select
                s.owner,
                s.tablespace_name,
                s.segment_name,
                s.segment_type,
                case
                    when s.segment_name like 'BIN$%' then '<Recyclebin> => ' || (select distinct original_name from dba_recyclebin r where r.owner = s.owner and r.object_name = s.segment_name)
                    when s.segment_type = 'TABLE' then s.segment_name
                    when s.segment_type in ('INDEX', 'LOBINDEX') then (select t.table_name from dba_indexes t where t.owner = s.owner and t.index_name = s.segment_name)
                    when s.segment_type = 'LOBSEGMENT' then (select t.table_name from dba_lobs t where t.owner = s.owner and t.segment_name = s.segment_name)
                    when s.segment_type = 'LOB PARTITION' then (select distinct t.table_name from dba_lob_partitions t where t.table_owner = s.owner and t.lob_name = s.segment_name)
                    when s.segment_type = 'TABLE PARTITION' then nvl((select distinct t.table_name from dba_tab_partitions t where t.table_owner = s.owner and t.partition_name = s.segment_name),s.segment_name)
                    when s.segment_type = 'INDEX PARTITION' then nvl((select distinct t.table_name from dba_indexes t where t.table_owner = s.owner and t.index_name = s.segment_name),s.segment_name)
                end master_object,
                s.bytes,
                row_number() over (order by owner, bytes desc) rn
            from
                dba_segments s
            where
                s.owner in (select schema_name  from q_schema_names)
        ) t group by rollup (owner, tablespace_name, segment_name, segment_type, master_object,bytes, rn)
    ) tt where grouping$type in ('0000000', '0111111', '1111111')
    order by
        tt.owner, rn$
) z