Аббревиатуры из названий

В нашей серьезной организации все отделы должны иметь аббревиатурные названия, непонятные стороннему человеку (сиречь потенциальному шпиёну). Должны, но не обязаны. Поэтому у нескольких отделов таких аббревиатур нет. При переносе справочника на другой сервер пришлось озадачиться автоматической генерацией аббревиатур из названий отделов. Разумеется, помогли регулярные выражения. Приведенный ниже пример формирует аббревиатуры из названий отделов, выбрасывая союз " и " и тире из названий, как ненужные.

select

    -- переводим строку в верхний регистр

    upper(

        -- удаляем пробелы, схлопывая оставшиеся буквы в аббревиатуру

        regexp_replace(

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

            regexp_replace(

                rs_name,

                -- формируем шаблон типа (<динамическая последовательность1> | <динамическая последовательность2>[|...] | <фиксированная последовательность1>[|...])

                '('||

                -- убираем размноженные перечислители, заменяя их на одиночные

                regexp_replace(

                    -- убираем лишний перечислитель из начала предложения

                    regexp_replace(

                        -- убираем начальные буквы из каждого слова в предложении, заменяя их на перечислители "|"

                        regexp_replace(

                            rs_name,

                            '(^|\s)\w',

                            '|'

                        ),

                        '^[|]+',

                        null

                    ),

                    '[|][|]+',

                    '|'

                )||

                -- добавляем блок фиксированных последовательностей " и "

                '|\s+и\s+|\s*-]s*)',

                null

            ),

            '\s',

            null

        )

    )

from

    (

        select regexp_replace(rs_name, '(Радио|радио)(навигационн|локационн|астрономическ|вещательн)', '\1 \2') rs_name, rs_abbr from DIC_RADIOSERVICE t

    ) t

Приведенный алгоритм также учитывает некоторые общепринятые сокращения отдельных слов, таких как: радионавигационная - РН, а не Р, радиолокационная - РЛ, а не Р, радиоастрономическая - РА, но не Р, радиовещательная - РВ, но не Р. Для этого в исходное название между "Радио" и остатком названия службы вставляется пробел, чтобы в результате получилось бы правильное сокращение.

Очередной "шедевр" формирования результирующего набора из строки с разделителями

Ковырялся в коде старого приложения на предмет его анализа и наткнулся на "мощный алгорим", формирующий результирующий набор строк из строки с разделителями, например из такой: "1,2,3,4,5,6,7,8,9,10,a,b,c,d,e,f":

with src as (SELECT ','||'1,2,3,4,5,6,7,8,9,10,a,b,c,d,e,f'||',' str from dual)

select 

level lvl, 

trim(substr(Src.Str,instr(Src.Str,',',1,level)+1 chunk, 

instr (Src.Str,',',1,level+1)-instr(Src.Str,',',1,level)-1)) op_num

from 

src

connect by 

rownum<=(length(Src.Str)-length(replace(Src.Str,',')))-1

Код, конечно, работает, но:

1. В этом коде реально неудобно разбираться

2. Этот код не учитывает наличие пробелов или каких-либо "лишних символов" между разделителями, а по идее - должен

Варианты альтернативного исполнения:

1. Конвейерная (pipelined) функция

2. Функция, возвращающая массив

3. Regexp'ы (пример есть у меня в разделе "Сравнение строк по сигнатурам".

PS. Я бы вот так вот написал:

with params as (select '\s*([^,]+)\s*(,|$)' pattern,'1,2,3,4,5,6,7,   8,  9    ,10,,12, a, bb, ccc , dddd' string from dual)

select 

regexp_substr(params.string, params.pattern, 1, level, 'i',1) chunk 

from 

params 

connect by

regexp_substr(params.string, params.pattern, 1, level, 'i',1) is not null

Сравнение строк по сигнатурам

Мне понадобилось сравнивать две строки по сигнатурам (вхождениям символов в строку), то есть строки <A><B><C> и <B><A><C> в моем случае должны быть равны. Я, например, использую это сравнение при анализе имеющихся у таблицы индексов, чтобы в коде понять, проиндексирован ли интересующий меня список столбцов, если на входе у меня есть только строки с перечислением имен этих столбцов, чтобы отбросить дублированные списки.

declare

    l_1 varchar2(1024) := '<a><b><c>';

    l_2 varchar2(1024) := '<b><c><a>';

    l_equal varchar2(32);

begin

   

    select

        decode(count(*),0,'EQUAL', 'NOT EQUAL') is_equal

    into

        l_equal

    from

        (select regexp_substr(l_1, '\<[^>]*\>',1,level,'i') chunk from dual t1 connect by regexp_substr(l_1, '\<[^>]*\>',1,level,'i') is not null) t1

        full outer join (select regexp_substr(l_2, '\<[^>]*\>',1,level,'i') chunk from dual t2 connect by regexp_substr(l_2, '\<[^>]*\>',1,level,'i') is not null) t2on t1.chunk = t2.chunk

    where t1.chunk is null or t2.chunk is null;

    dbms_output.put_line(l_equal);

end;

Случайно на GitHub'е наткнулся на неискомую, но давно желанную вещь - набор интерфейсных функций для использования Java-библиотек регулярных выражений непосредственно на уровне PL/SQL для того, чтобы обойти ограничения, налагаемые POSIX стандартом на реализацию регулярных выражений в ORACLE

Адрес: https://github.com/xtender/XT_REGEXP/blob/master/README.md

С помощью этой библиотеки, например, становится возможным выполнять замены с опережающими и ретроспективными проверками:

select xt_regexp.replace_all('Please, make me happy!','(?<=Please)(,)','$1 Scott!!!!') from dual;

Result: Please, Scott!!!! make me happy!**

Получение имени текущего метода с помощью директив $$PLSQL_*

Написал очередную уродливую, но работающую, реализацию алгоритма получения имени текущего метода. Это нужно, например, при выводе сообщений об ошибках системы, типа 

"Ошибка в строке #17 метода PKG_PERSONS.CreateNewPerson(..): Не указана фамилия физического лица"

Очередная она потому что какой дурак ее не пишет разработчик эту проблему не решает. Уродливая она потому, что в PL/SQL просто обязана быть системная переменная, типа $$PLSQL_METHOD_NAME, содержащая имя текущего PL/SQL метода/процедуры/функции, а все прочие методы получения - очевидно уродливы. Кстати, вроде бы в 12-ой версии ORACLE эту проблему кое-как решили, добавив пакет UTL_CALL_STACK, о чем счастливо отрапортовали Том Кайт и документация ORACLE. А, что делать нам, несчастным и отсталым?!...

Короче, выглядит все это вот так:

   function get_fname(p_unit in varchar2, p_line in number) return varchar2

    is

        l_name varchar2(30);

    begin

        select distinct

            max(tt.proc_name) keep (dense_rank last order by tt.line) over() text

        into l_name

        from

        (

        select

            t.proc_name,

            t.line

        from

        (

            select

                regexp_substr(text, '(^|;\s*|\s+is\s+|)(procedure|function)\s+(return)*(\w+)\s*(\(|is)*',1,1,'i',4) proc_name,

                line

            from

                user_source s

            where

                s.type in ('TYPE BODY', 'PACKAGE BODY', 'TRIGGER', 'PROCEDURE', 'FUNCTION') and

                s.name = upper(p_unit) and

                line <= p_line

        ) t

        where t.proc_name is not null and

        upper(t.proc_name) in (select procedure_name from user_procedures p where p.object_name = upper(p_unit))

        order by line

        ) tt;

        return '"' || UPPER(sys_context('USERENV', 'CURRENT_SCHEMA')) || '"' || '.' || '"' || l_name || '"';

    end;

Пакет, в котором размещена функция, создан с указанием AUTHID CURRENT_USER. 

Вызов метода в коде:

...

raise_application_error(-20001, 'Ошибка в строке #'||$$PLSQL_LINE||' метода ' || PKG_DEBUG_TOOLS.get_fname($$PLSQL_UNIT, $$PLSQL_LINE)  || '(..): Не указана фамилия физического лица');'

...


P.S. Функцию поиска имени метода в USER_SOURCE, конечно, можно обмануть, вставив, например, между объявлением метода пакета и вызовом функции литерал, в котором будет описан вызов какого-либо другого метода пакета, только вот зачем...

P.P.S. 02.10.2013 Переписал метод, чтобы было более красиво и менее ресурсоемко, заодно исправил ошибку в разборе имени функции

P.P,P.S. 10.03.2023 Понаписал за прошедшее время разных определялок текущих методов и пакетов. Вот прилагаю, если у кого-то еще остались вопросы. Выбираем на свой вкус. Я сейчас предпочитаю п2 для определения текущего метода и п3 для определения текущего пакета (oracle 19c).

1. ThisPackageAndMethod varchar2(128) := regexp_substr(regexp_substr(dbms_utility.format_call_stack, 'package\s+body\s+([^[:space:]]+)',1,1,'i',1), '[^.]+[.][^.]+$');
2. ThisMethod           varchar2(128) := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1));
3. ThisPackage          varchar2(128) := $$PLSQL_UNIT;
4. ThisPackage          varchar2(128) := nvl(regexp_substr(dbms_utility.format_call_stack, 'package\sbody\s(((\w)+[.])?(\w+))',1,1,'i',4), 'task_'||to_char(sysdate, 'YYYYMMDDHH24MISS'));

Определение границ слов с помощью регулярных выражений в PL/SQL

Пишу сейчас очередной генератор структуры БД из метаданных для своей организации.
Для первоначального заполнения структуры новой БД взяли, разумеется старую версию оной. Однако, в результате анализа структуры выяснилось, что мои предшественники вовсю пользовались зарезервированными словами типа LEVEL и DESC для именования столбцов таблиц. Надо при генерации БД это учитывать и заключать названия столбцов в двойные кавычки ("). Все бы ничего, но есть такой элемент структуры таблицы, как CHECK и в нем условие ограничения описано в виде SQL-выражения, например:

     TYPE_ID is not null

или

    Upper(Name)=Name

Каким образом обработать их?
Тум, ту-ру-руууум! Разумеется регулярными выражениями. Для этого мне нужно определить границы слов, чтоб не заменить случаем Unnamed в Un"name"d. К сожалению, регулярные выражения ORACLE не поддерживают операторы определения границ слова \< и \>, поэтому придется выполнить два дополнительных прохода по строке до и после замены, чтобы создать их самому. Самый глубоко вложенный regexp_replace реализует обрамление chr(2) вокруг всех символов, которые могут быть использованы для разделения искомых идентификаторов. Следующий regexp_replace производит нужную нам замену. Самый верхний regexp_replace восстанавливает первоначальный вид строки.


select 
    regexp_replace(
        regexp_replace(
            regexp_replace('"NAME" IS NULL OR UPPER(NAME)=UNNAMED_FUNC(NAME)', '([^[:alnum:]_$"])',chr(2) || '\1' || chr(2)),
            '(\s|'||chr(2)||'|^)NAME(\s|'||chr(2)||'||$)',
            '"NAME"'
        ),
        chr(2),
        null
    )
from dual

цветом показаны:

    обрабатываемое выражение

    искомая строка

    замещающая строка

Шаблон поиска (^[:alnum:]_$") учитывает все символы, которые могут входить в название идентификатора. Символ " - добавлен для того, чтобы идентификатор, уже обрамленный двойными кавычками, не множил их вокруг себя.

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

CREATE PROFILE LonelyHeartProfile LIMIT
    SESSIONS_PER_USER 1
    CPU_PER_SESSION unlimited
    CPU_PER_CALL 6000
    LOGICAL_READS_PER_SESSION unlimited
    LOGICAL_READS_PER_CALL 100
    IDLE_TIME 30
    CONNECT_TIME 480;

CREATE USER LonelyHeart
    DEFAULT TABLESPACE LonelyHeart_Data_TS
    TEMPORARY TABLESPACE LonelyHeart_Temp_TS
    QUOTA 100M ON LonelyHeart_Data_TS
    PROFILE LonelyHeartProfile;

Парсинг кода RTF документов в PL/SQL

Набросал неспешно работающий парсер RTF для PL/SQL, предназначенный для пооператорного (с помощью regexp'ов) чтения RTF документа. Для парсинга небольших файликов, без наличия графики внутри, вполне приемлем. У меня есть в отладке "быстрая" версия, но пока что я не готов ее выложить на всеобщее обозрение - есть нерешенные вопросы по анализу структуры rtf.

Функция rtf_parse$lab() работает согласно Rich Text Format (RTF) Specification. Version 1.9.1 (C) Microsoft. Данные получает, как я уже говорил, пооператорно, regexp'ом находя начало следующего управляющего слова. Управляющие слова, согласно спецификации имеют вид:

({|}|[\*]\cw[params])[[<space>]PCDATA]

где
    {, } - открывающая или закрывающая скобки групп
    \* - признак необязательного исполнения управляющего слова [MANDATORY]
    \cw - управляющее слово [a-zA-Z], длиной до 32 символов [CW]
    params - параметр [0-9], длиной до 10 символов [PARAMS]
    <space> - пробельный разделитель \s+, может быть не пробелом, а переходом на новую строку
    PCDATA - кодированные данные в hexadecimal формате (см. спецификацию), длина неограниченна. При чтении требуется декодирование, при записи - кодирование данных согласно спецификации.[PCDATA]

Константы ошибок PARSER$RTF.ERR_* - задайте самостоятельно, я их взял из пакета с рабочей "быстрой" версией парсера.

Основной минус этого пакета разбора - невысокая скорость, связанная с чтением данных из CLOB-объекта и повсеместным использованием функций regexp_*, крайне упрощающих написание кода, но создающих проблемы с ростом времени разбора. Для написания "быстрой" версии парсера я использовал загрузку RTF-кода в память с разбиением на строки по 32кБт и считывание порциями по 64байта для анализа разделителей команд.

create or replace function rtf_parse$lab(p_rtf in clob) return number 
is
    -- позиционные параметры        
    l_pos_start number :=null; -- начало лексемы
    l_pos_end number := null; -- конец лексемы
    l_pos_current number := 1; -- текущая позиция в тексте RTF
    l_pos_data_separator number := 0; -- позиция разделителя управляющего слова и данных
    l_pos_cw_params number := 0; -- позиция параметров в имени команды
    -- параметры хранения данных
    l_chunk_clob clob; -- данные в CLOB (если длина более 32000)
    l_chunk_char varchar2(32000); -- данные в CHAR (если длина менее 32000)
    l_cnunk_pcdata_type number (1); -- тип data (константы c_PCDATA_TYPE_*)
    l_chunk_cw varchar2(64); -- управляющее слово
    l_chunk_cw_params varchar2(32); -- параметры управляющего слова
    l_chunk_mandatory number(1); -- флаг обязательного управляющего слова
    c_PCDATA_TYPE_CLOB constant number := 1; -- константа - данные в CLOB
    c_PCDATA_TYPE_CHAR constant number := 0; -- константа - данные в CHAR
begin
    -- проверяем входные параметры
    if p_rtf is null or dbms_lob.getlength(p_rtf) = 0 then return PARSER$RTF.ERR_INVALID_ARGUMENT; end if;
    if substr(p_rtf, 1, 5) != '{\rtf' then return PARSER$RTF.ERR_INVALID_FORMAT; end if;
    -- определяем структуры хранения данных
    dbms_lob.createtemporary(l_chunk_clob, true, dbms_lob.session);
    loop
        l_pos_current  := regexp_instr(srcstr => p_rtf, pattern => '({)|(})|(\\[a-zA-Z])|(\\\*\\[a-zA-Z])',position => l_pos_current, occurrence => 1, returnparam => 0);
        -- если достигнут конец строки - выходим  
        if nvl(l_pos_current,0) = 0 then return PARSER$RTF.ERR_NOERROR_EOF; end if;
        -- иначе устанавливаем текущую финишную границу лексемы
        l_pos_end := l_pos_current;
        -- если установлена стартовая граница лексемы, начинаем разбор
        if l_pos_start is not null then
            -- [CW, PCDATA]
            if l_pos_end - l_pos_start > 32000 then
                l_chunk_clob := substr(p_rtf, l_pos_start, l_pos_end - l_pos_start);
                l_pos_data_separator := regexp_instr(srcstr => l_chunk_clob, pattern =>  '{|}|(\s)+', position => 1, occurrence => 1, returnparam => 0);
                if l_pos_data_separator > 0 then 
                    l_chunk_cw := substr(l_chunk_clob, 1, l_pos_data_separator);
                    l_chunk_clob := substr(l_chunk_clob, l_pos_data_separator+1);
                else
                    l_chunk_cw := l_chunk_clob;
                    l_chunk_clob := null;
                end if;
                l_cnunk_pcdata_type := c_PCDATA_TYPE_CLOB;
            else
                l_chunk_char := substr(p_rtf, l_pos_start, l_pos_end - l_pos_start);
                l_pos_data_separator := regexp_instr(srcstr => l_chunk_char, pattern => '{|}|(\s)+', position => 1, occurrence => 1, returnparam => 0);
                if l_pos_data_separator > 0 then 
                    l_chunk_cw := substr(l_chunk_char, 1, l_pos_data_separator);
                    l_chunk_char := substr(l_chunk_char, l_pos_data_separator+1);
                else
                    l_chunk_cw := l_chunk_char;
                    l_chunk_char := null;
                end if;
                l_cnunk_pcdata_type := c_PCDATA_TYPE_CHAR;
            end if;
            -- [PARAMS] тут у нас есть l_chunk_cw и l_chunk_char/l_chunk_clob
            l_pos_cw_params := regexp_instr(srcstr => l_chunk_cw, pattern => '[0-1]+', position => 1, occurrence => 1, returnparam => 0);
            if nvl(l_pos_cw_params,0) > 0 then
                l_chunk_cw_params := substr(l_chunk_cw, l_pos_cw_params);
                l_chunk_cw := substr(l_chunk_cw, 1, l_pos_cw_params);
            else
                l_chunk_cw_params := null;
            end if;
            -- [MANDATORY]
            if substr(l_chunk_cw, 1, 3) = '\*\' then
                l_chunk_mandatory := 0;            
                l_chunk_cw := substr(l_chunk_cw, 4);            -- убираем \*\ начала оператора
            elsif substr(l_chunk_cw, 1, 1) = '\' then
                l_chunk_mandatory := 1;            
                l_chunk_cw := substr(l_chunk_cw, 2);            -- убираем \ начала оператора
            else
                l_chunk_mandatory := 1;
            end if;

            -- тут мы имеем [MANDATORY], [CW], [PARAMS], [PCDATA], можно выводить на экран
            dbms_output.put_line('>: cw='|| rpad(l_chunk_cw, 32, ' ') || chr(9) || 
                ' prm='|| nvl(l_chunk_cw_params, '<NO PARAMS>') || chr(9) || 
                ' cwtyp=' || case when l_chunk_mandatory = 1 then 'MANDATORY' else 'OPTIONAL' end || chr(9) || 
                ' datatyp=' || case when l_cnunk_pcdata_type = c_PCDATA_TYPE_CHAR then 'CHAR' else 'CLOB' end || chr(9) || 
                ' data=' || case when l_cnunk_pcdata_type = c_PCDATA_TYPE_CHAR then nvl(l_chunk_char,'<NO DATA>') else nvl(substr(l_chunk_clob,1,1024) || '<...next ' || dbms_lob.getlength(l_chunk_clob)|| ' symbols...>','<NO DATA>') end);
        end if;
        -- иначе устанавливаем текущую стартовую границу лексемы
        l_pos_start := l_pos_end;
        -- вычисляем необходимое смещение, согласно полученной инфе на стеке
        if substr(p_rtf, l_pos_current, 3) ='\*\' then --/*/[a-zA-Z]
            l_pos_current := l_pos_current+3;
        elsif substr(p_rtf, l_pos_current, 1) ='\' then --\[a-zA-Z]
            l_pos_current := l_pos_current + 1;
        elsif substr(p_rtf, l_pos_current, 1) ='}' then --}
            l_pos_current := l_pos_current+1;
        elsif substr(p_rtf, l_pos_current, 1) ='{' then --{
            l_pos_current := l_pos_current+1; 
        else -- вообще сюда попасть никак не должно
            return PARSER$RTF.ERR_INTERNAL_ERROR;
        end if;

    end loop;
    return PARSER$RTF.ERR_INTERNAL_ERROR;
end rtf_parse$lab;