Библиотека atop-plsql для разработки в СУБД Oracle

Недавно я оформил и выложил на GitHub "труд долгих лет" atop-plsql - набор PL/SQL пакетов и сопутствующих им типов, таблиц и некоторых других объектов схемы БД.

Эти пакеты развивались и использовались в течение нескольких лет для разработки на их основе конечных решений в СУБД Oracle 11gR2.

Библиотека atop-plsql предлагает разработчику следующие средства и возможности:

• Спецификация пакета at_env (тела у этого пакета нет) с константами - параметрами конфигурации, используемыми другими пакетами из коллекции atop-plsql. Разработчик, использующий atop-plsql, настраивает эти параметры под себя.

• Несколько типов, используемых пакетами atop-plsql и доступных разработчику, включая (вложенные) table of varchar2(4000), table of number, table of date и ассоциативные массивы в пакете at_type, а также функции для конвертации между вложенными таблицами и ассоциативными массивами.

• Несколько исключений, используемых пакетами atop-plsql и доступных разработчику, включая assertion_error, invalid_argument и другие, определенные в пакете at_exc, а также процедуры, проверяющие некоторое условие и возбуждающие исключение при его невыполнении.

• Полезные функции для разбиения строки varchar2 с разделителями на элементы, помещаемые во вложенную таблицу или ассоциативный массив; для соединения элементов в строку varchar2; для zip-сжатия данных из внешнего файла (доступного через объект-директорию Oracle), ассоциативного массива или blob; см. пакет at_util.

• Файловые утилиты в пакете at_file для создания, записи в, чтения из и переименования внешних файлов, использующие кодировку по умолчанию, а также объекты-директории по умолчанию, заданные в спецификации пакета at_env.

• Pipeline-функции, читающие .csv файл (внешний, blob или clob) и возвращающие его строки как строки таблицы; см. функции csv_table в пакете at_file.

  На тему работы с внешними .csv файлами как с таблицами см. мою статью Загрузка данных в БД Oracle из csv-файла.

• Процедуры для загрузки .csv файла (внешнего, blob или clob) в таблицу базы данных, строки которой представляют строки файла, а столбцы содержат индивидуальные значения; см. процедуры load_csv в пакете at_file.

• Процедуры, получающие на вход открытый динамический курсор (sys_refcursor), извлекающие с его помощью данные, форматирующие их как csv, html-таблицу или json, и выводящие их в dbms_output буфер, OWA буфер, ассоциативный массив или внешний файл; см. пакет at_out.

• Утилиты для создания, управления и удаления журнальных таблиц для регистрации изменений в указанной таблице БД вместе с метаинформацией о пользовательской сессии, внесшей изменения; см. пакет at_jour. Префикс или суффикс для имени журнальных таблиц, префикс имен столбцов с метаинформацией, а также имя схемы для журнальных таблиц задаются в спецификации пакета at_env.

• Процедуры в пакете at_log для логирования информационных сообщений, предупреждений, ошибок и отладочных сообщений в таблицу базы данных at_log_; со временем старые сообщения автоматически удаляются из таблицы.

• Утилиты в пакете at_ldap для выполнения запросов и извлечения данных из LDAP сервера. Извлеченные данные возвращаются как ассоциативный массив или как таблица - с помощью pipeline-функции. Функция user_mail запрашивает у LDAP сервера и возвращает email адрес по имени пользователя. Адрес LDAP сервера, номер порта, а также имя и пароль пользователя указываются в спецификации пакета at_env.

  На тему работы с LDAP сервером из PL/SQL см. мои статьи Как работать с LDAP в PL/SQL.

• Хранилище для параметров конфигурации, устанавливаемых и извлекаемых владельцами (owners) с помощью пакета at_conf. Значения параметров, в зависимости от природы параметров, возвращаются "как есть" или предварительно динамически оцениваются с помощью execute immediate.

• Утилиты в пакете at_task для запуска блоков кода PL/SQL по расписанию, причем расписание (в духе crontab) задается регулярным выражением. Задачи выполняются по расписанию посредством заданий (jobs) dbms_scheduler.

  На эту тему см. мою статью Запуск задач в БД Oracle по расписанию, заданному регулярным выражением.

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

• Процедуры в пакете at_mail для отправки по электронной почте сообщений, используя приветствие, подпись и отправителя по умолчанию, заданные в спецификации пакета at_env.

• Процедуры в пакете at_mail для отправки данных, полученных с помощью динамического курсора (sys_refcursor), по электронной почте в виде вложенного .csv файла (опционально сжатого zip) или в виде html-таблицы в теле сообщения; получатели, тема, текст сообщения и его приоритет также передаются через параметры.

• Пакет at_delta для создания журналов регистрации изменений в указанных таблицах и создания сервисов, предоставляющих клиентам эти изменения (дельты); это инфраструктура для создания интерфейсов с внешними системами, а также внутренних решений по анализу и обработке изменения данных за период времени.

  На эту тему см. мою статью Захват и обработка изменений в БД Oracle (at_delta).

Для того, чтобы установить atop-plsql, пользователю Oracle, помимо стандартных ролей connect и resource, нужно выдать следующие привилегии:

grant create table to <user>;
grant create view to <user>;
grant create trigger to <user>;
grant create job to <user>;

grant execute on utl_file to <user>;
grant execute on utl_tcp to <user>;
grant execute on utl_smtp to <user>;

grant select on sys.v_$database to <user>;
grant select on sys.v_$session to <user>;

Для того, чтобы работать с LDAP сервером с помощью PL/SQL и, в частности, с помощью пакета at_ldap, нужно создать ACL, разрешающий доступ пользователя Oracle, владельца пакета, к серверу LDAP:

begin
    dbms_network_acl_admin.create_acl (
        acl          => 'ldap.xml',
        description  => 'ACL to grant access to LDAP server',
        principal    => '<user>',
        is_grant     => true, 
        privilege    => 'connect'
    );
end;
/

begin
    dbms_network_acl_admin.assign_acl (
        acl         => 'ldap.xml',
        host        => '<ldsp server ip-address>',
        lower_port  => <ldap server port>,
        upper_port  => <ldap server port>
    );
end;
/

Аналогично, для того, чтобы пользователь Oracle мог отправлять почту с помощью PL/SQL и, в частности, с помощью пакетов at_smtp и at_mail, нужно создать ACL, разрешающий доступ пользователя к серверу SMTP:

begin
    dbms_network_acl_admin.create_acl (
        acl          => 'mailer.xml',
        description  => 'ACL to grant access to smtp server',
        principal    => '<user>',
        is_grant     => true, 
        privilege    => 'connect'
    );
end;
/

begin
    dbms_network_acl_admin.assign_acl (
        acl         => 'mailer.xml',
        host        => '<smtp server ip-address>',
        lower_port => <smtp server port>,
        upper_port => <smtp server port>
    ); 
end;
/

В папке demo репозитория на GitHub имеется пакет at_rep, использующий средства atop-plsql для формирования и отправки по электронной почте трех отчетов. Пакет одновременно демонстрирует возможности atop-plsql и предоставляет практически полезные отчеты.