Перейти к основному содержимому

orafce. Совместимость логики Oracle для PL/pgSQL

Версия: 4.4.0.

В исходном дистрибутиве установлено по умолчанию: нет.

Связанные компоненты: отсутствуют.

Схема размещения: ext.

Модуль orafce реализует некоторые функции из СУБД Oracle, которые отсутствуют или ведут себя по-другому в СУБД PostgreSQL.

Модуль является функциональным расширением совместимости для логики, написанной под Oracle.

Объекты​

dbms_alert​

Пакет добавляет модель межсессионного взаимодействия.

ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Процедура_signalname::text,
message::text		–Internal
Триггерdeferred_signal–triggerInternal
Процедураregistername::text–Регистрация ipc с именем name
Процедураremovename::text–Удаление ipc с именем name
Процедураremoveall––Удаление всех ipc
Процедураset_defaultssensitivity::float8–Определение sensitivity
Процедураsignalevent::text,
_message::text		–Регистрация сигнала для ipc event с сообщением _message
Функцияwaitanytimeout::float8name::text,
message::text,
status::int		Ожидание сигналов в течение timeout секунд
Функцияwaitonename::text,
timeout::float8		message::text,
status::int		Ожидание сигнала в ipc name в течение timeout секунд

dbms_assert​

Пакет добавляет дополнительные проверки в целях защиты от SQL injection.

ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Функцияenquote_literalstr::varchar::varcharКвотирование строки; верификация двойного квотирования строки
Функцияenquote_namestr::varchar,
[lowercase::bool]		::varcharКвотирование имени объекта SQL. Опциональный параметр – приведение имени к нижнему регистру
Внимание!: поведение отличается от Oracle, где имя приводится к верхнему регистру
Функцияnoopstr::varchar::varcharФункция-заглушка. Изменений не производится.
Функцияqualified_sql_namestr::varchar::varcharПроверка того, что входной параметр является правильным именем объекта SQL
Функцияschema_name str::varchar::varcharПроверка существования в БД определенной схемы
Функцияsimple_sql_namestr::varchar::varcharПроверка применимости входного параметра для использования в качестве идентификатора SQL
Функцияobject_namestr::varchar::varcharПроверка существования нефункционального объекта в БД с именем входного параметра

dbms_output​

Пакет добавляет консольный вывод сообщений.

В Pangolin используется RAISE, однако поведение функций пакета отличается от принятого в Pangolin порядка выдачи сообщений. Функции пакета представляют собой очередь сообщений и могут быть прочитаны внутри сеанса.

ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Процедураdisable––Отключение вывода сообщений
Процедураenable[buffer_size::int]–Включение вывода сообщений. Опциональный параметр указывает размер буфера в байтах
Функцияget_line–line::text,
status::int		Получение сообщений
Функцияget_linesnumlines::intlines::text[],
numlines::int		Получение блока последних сообщений
Процедураnew_line––Добавление нового пустого сообщения
Функцияputa::text–Добавление нового сообщения (блок)
Функцияput_linea::text–Добавление нового сообщения (строка)
Процедураserveroutput::bool–Переключение вывода сообщений в консоль

dbms_pipe​

Пакет добавляет эмуляцию каналов Oracle. Реализация основана на использовании shared memory.

  • Максимальное количество каналов - 50;
  • Длина канала определяется не в байтах, а в количестве элементов;
  • Возможна отправка сообщений без ожидания;
  • Возможна отправка пустых сообщений;
  • Тип timestamp для next_item_type = 13;
  • СУБД Pangolin не поддерживает тип RAW. Используйте тип bytea.
ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Представлениеdb_pipes–name::varchar;
items::int4;
size::int4;
limit::int4;
private::bool;
owner::varchar		Список каналов
Процедураcreate_pipe::text;
[::int4,[::bool]]		–Передача параметров (имени, размера, признака) при создании канала
Функцияnext_item_type::int4Определение формата сообщения в канале:
0 - канал пуст;
9 - numeric/int4/int8;
11 - text;
12 - date;
13 - timestamptz;
23 - byte;
24 - record
Процедураpack_message::bytea;
::int4;
::int8;
::numeric;
::text;
::date;
::timestamptz;
::bytea;
::record		–Добавление сообщения в канал
Процедураpurge::text–Очистка канала.
Параметр - имя канала
Функцияreceive_message::text [::int4]::int4Прием сообщения. Копирование сообщения в локальный буфер.
Параметры:
- имя канала;
- время ожидания в секундах.
Результат: код возврата.
0 - успех;
1 - тайм-аут;
2 - Ошибка: размер сообщения превышает размер буфера;
3 - Прерывание;
? - Недостаточно привилегий
Процедураremove_pipe::text–Удаление канала.
Параметр - имя канала
Процедураreset_buffer––Очистка буфера
Функцияsend_message::text[::int4 [::int4]]::int4Передача сообщения.
Параметры:
- имя канала;
- тайм-аут в секундах;
- максимальный размер канала.
Канал, созданный этой функцией, будет удален после передачи сообщения (в отличие от канала, созданного функцией create_pipe).
Код возврата совпадает с кодами функции receive_message
Функцияunique_session_name–::varcharВозвращает уникальное имя сессии, в которой создан канал
Функцияunpack_message_bytea–::byteaРаспаковка сообщения bytea
Функцияunpack_message_date–::dateРаспаковка сообщения date
Функцияunpack_message_number–::numericРаспаковка сообщения number
Функцияunpack_message_record–::recordРаспаковка сообщения record
Функцияunpack_message_timestamp–::timestamptzРаспаковка сообщения timestamp
Функцияunpack_message_text–::textРаспаковка сообщения text

dbms_random​

Пакет добавляет псеводслучайные числа Oracle.

ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Процедураinitialize::int4–Инициализация генератора псевдослучайных чисел c заданным зерном (seed)
Функцияnormal–::floatГенерация числа в нормальном распределении
Функцияrandom–::int4Генерация числа в полном диапазоне int4 (-2^31..2^31)
Процедураseed::int4–Передача зерна (seed) генератору
Процедураseed::text–Передача зерна (seed) генератору
Функцияstringopt::text;
len::int4		textГенерация случайной строки длиной len.
Параметры:
'u','U' - UPPERCASE ALPHA;
'l','L' - lowercase alpha;
'a','A' - MiXeD AlPhA;
'x','X' – UPPERCASE ALPHANUMERIC;
'p','P' - Any printable characters
Процедураterminate––Окончание работы пакета
Функцияvalue[ low::float, high::float]–Генерация псевдослучайного номера из диапазона (с нижней границей low включительно и верхней границей high не включительно)

dbms_utility​

Пакет добавляет просмотр стека вызовов.

ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Функцияformat_call_stack[ ::text ]textВозвращает стек вызовов внутри блока PL/pgSQL

utl_file​

Пакет добавляет операции с файловой системой.

В каждой сессии допускается до 10 открытых файловых дескрипторов. Длина строки ограничена 32 Кбайта.

ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Таблицаutl_file_dirdir::text;
dirname::text		–Таблица алиасов
Доменfile_typefile_type::integer–Домен для хранения файлового дескриптора
Функцияfclosefile::utl_file.file_type::utl_file.file_typeЗакрытие файлового дескриптора
Процедураfclose_all––Закрытие всех открытых файловых дескрипторов
Процедураfcopysrc_location::text;
src_filename::text;
dest_location::text;
dest_filename::text;
[ start_line::int4 ];
[[ end_line::int4]]		–Копирование файла.
Передаваемые параметры:
- исходный каталог;
- исходное имя файла;
- каталог назначения;
- имя файла назначения.
Опционально:
- начальная строка;
- конечная строка
Процедураfflushfile::utl_file.file_type–Сброс буфера на диск
Функцияfgetattrlocation::text;
filename::text		fexist::boolean;
file_length::bigint;
block size::int4		Получение атрибутов файла
Функцияfopenlocation::text;
filename::text;
open_mode::text;
[ max_linesize::int4 ];
[[ encoding::name ]]		::utl_file.file_typeОткрытие файлового дескриптора.
Параметр open_mode стандартный (r, rw, a, ...)
Процедураfremovelocation::text;
filename::text		–Удаление файла
Процедураfrenamelocation::text;
filename::text;
[ dest_dir::text ];
[ dest_file::text ];
[[ overwrite::bool ]]		–Переименование/перемещение файла
Функцияget_linefile::utl_file.file_type;
len::int4		buffer::textПолучение строки из открытого файла
Функцияget_nextlinefile::utl_file.file_typebuffer::textПолучение строки из открытого файла
Функцияis_openfile::utl_file.file_type::booleanПроверка валидности файлового дескриптора
Функцияnew_linefile::utl_file.file_type;
[ lines::int4]		::booleanДобавление новой строки в открытый файл
Функцияputfile::utl_file.file_type;
`(buffer::text \		buffer::anyelement )`::boolean
Функцияput_linefile::utl_file.file_type;
`(buffer::text \		buffer::anyelement ); <br /> [ autoflush::boolean ]`::boolean
Функцияputffile::utl_file.file_type;
format::text;
[ arg1::text ];
[[ arg2::text ]];
[[[ arg3::text ]]];
[[[[ arg4::text ]]]];
[[[[[ arg5::text ]]]]]		::booleanФорматированный вывод в открытый файл
Функцияtmpdir–::textВывод значения системной переменной $TEMP

plunit​

Пакет добавляет функции проверок.

ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Процедураassert_equalsexpected::anyelement;
actual::anyelement;
[ message::varchar]		–Проверка условия expected = actual
Процедураassert_equalsexpected::float8;
actual::float8;
range::float8;
[ message::varchar]		–Проверка условия expected = actual в пределах range
Процедураassert_falsecondition::bool;
[ message::varchar]		–Проверка логического условия FALSE
Процедураassert_truecondition::bool;
[ message::varchar]		–Проверка логического условия TRUE
Процедураassert_not_equalsexpected::anyelement;
actual::anyelement;
[ message::varchar].		–Проверка условия expected != actual
Процедураassert_not_equalsexpected::float8;
actual::float8;
range::float8;
[ message::varchar]		–Проверка условия expected != actual в пределах range
Процедураassert_not_nullactual::anyelement;
[ message::varchar]		–Проверка входного параметра на присутствие значения NOT NULL
Процедураassert_nullactual::anyelement;
[ message::varchar]		–Проверка входного параметра на отсутствие значения IS NULL
Процедураfail[ message::varchar]–Безусловный возврат с ошибкой

plvchr​

Пакет добавляет специфичные для Oracle функции при работе с текстом.

ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Функция_is_kindstr::text,kind::int4;
c::int4, kind::int4		booleanСкрытый траппер для функций is_%
Функцияchar_namec::textvarcharВозвращает код символа в кодировке ASCII
Функцияfirststr::textvarcharВозвращает первый символ в строке
Функцияlaststr::textvarcharВозвращает последний символ в строке
Функцияnthstr::text;
n::int4		textВозвращает n-ый символ в строке
Функцияquoted1str::textvarcharВозвращает текст, заключенный в апострофы
Функцияquoted2str::textvarcharВозвращает текст, заключенный в кавычки
Функцияstrippedstr::text;
char_in::text		varcharУдаление символов подстроки char_in из str с учетом регистра символов
Функцияis_blankstr::text;
c::int4		booleanПроверка значения параметра на заполненность
Функцияis_digitstr::text;
c::int4		booleanПроверка значения параметра на цифровой формат
Функцияis_letterstr::text;
c::int4		booleanПроверка значения параметра на текстовый формат
Функцияis_otherstr::text;
c::int4		booleanПроверка значения параметра на несоответствие ни цифровому, ни текстовому формату
Функцияis_quotestr::text;
c::int4		booleanПроверка значения текстового параметра на квотирование (кавычки или апострофы)

plvdate​

Пакет добавляет специфичные для Oracle функции при работе с датами.

ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Функцияadd_bizdays::date;
::int4		dateПолучение рабочей даты, спустя <n> рабочих дней от заданной
Функцияbizdays_between::date;
::date		int4Количество рабочих дней между двумя датами
Функцияdays_inmonth::dateint4Количество дней в месяце
Процедураdefault_holidays::text–Загрузка рабочего календаря.
Принимаемые конфигурации:
– Czech;
– German;
– Austria;
– Poland;
– Slovakia;
– Russia;
– GB;
– USA
Функцияinclude_start[ ::boolean ]booleanВключение первой даты в расчет
Функцияnoinclude_start–booleanИсключение первой даты из расчета
Функцияisbizday::datebooleanПроверить, является ли дата рабочим днем
Функцияisleapyear::datebooleanПроверить, является ли год високосным
Функцияnearest_bizday::datedateПолучить ближайшую дату рабочего дня
Функцияnext_bizday::datedateПолучить дату следующего рабочего дня от заданного
Функцияprev_bizday::datedateПолучить дату предыдущего рабочего дня относительно заданного
Функцияset_nonbizday::datebooleanЗадать дату как нерабочий день
Процедураset_nonbizday::text

::date,::boolean		–Задать день недели как нерабочий.

Задать день как нерабочий. Второй параметр - рекурсия (каждый год)
Функцияunset_nonbizday::datebooleanОпределение рабочего дня. Возвращаемый параметр - рекурсия (каждый год)
Процедураunset_nonbizday::text

::date,::boolean		–Задать день недели как рабочий.

Задать день как рабочий. Второй параметр - рекурсия (каждый год)
Функцияuse_easter–booleanЗадать Пасху как нерабочий день. Возвращаемый параметр - рекурсия (каждый год)
Процедураuse_easter::boolean–Задать Пасху как нерабочий день
Функцияunuse_easter–booleanЗадать Пасху как рабочий день. Возвращаемый параметр - рекурсия (каждый год)
Процедураunuse_easter::boolean–Задать Пасху как рабочий день
Функцияuse_great_friday–booleanЗадать Страстную пятницу как нерабочий день. Возвращаемый параметр - рекурсия (каждый год)
Процедураuse_great_friday::boolean–Задать Страстную пятницу как нерабочий день
Функцияusing_easter–booleanПроверить, является ли Пасха рабочим днем
Функцияusing_great_friday–booleanПроверить, является ли Страстная пятница рабочим днем
Функцияversion–cstringВерсия схем

plvlex​

Пакет основан на оригинальном PL/Vision LEXical analysis и добавляет специфичные для Oracle функции при работе с лексемами.

примечание

Данный пакет основан на ключевых словах PostgreSQL и не является полностью совместимым с Oracle.

ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Функцияtokensstr::text;
skip_spaces::boolean;
qualified_names::boolean		SETOF record;
pos::integer;
token::text;
code::int4;
class::text;
separator::text;
mod::text		Лексический парсер.

Возвращаемые параметры:
– pos: позиция лексемы;
– token: лексема;
– code: порядковый номер в классе для лексем, являющихся ключевыми словами и идентификаторами в Pangolin;
– class: класс лексемы;
– separator: разделитель;
– mod: модификатор

plvstr​

Пакет добавляет специфичные для Oracle функции при работе со строками и текстовыми данными.

ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Функцияbetwnstr::text,start::int4,_end::int4[,inclusive:boolean]
str::text,start::text,_end::text[,startnth::int4,endth::int4[,inclusive::boolean,gotoend::boolean]]		textПоиск подстроки в пределах от start до _end символа
Функцияinstrstr::text,patt::text[,start::int4[,nth::int4]]int4Поиск позиции подстроки
Функцияis_prefixstr::int8,prefix::int8
str::int4,prefix::int4
str::text,prefix::text[,cs::boolean]		booleanПроверка, начинается ли искомая строка с определенного префикса
Функцияleftstr::text,n::int4varcharВозвращает n символов с начала строки
Функцияrightstr::text,n::int4varcharВозвращает n символов с конца строки
Функцияlpartstr::text,div::text[,start::int4[,nth::int4[,allifnotfound::boolean]]]textВозвращает подстроку, находящуюся до строки поиска
Функцияrpartstr::text,div::text[,start::int4[,nth::int4[,allifnotfound::boolean]]]textВозвращает подстроку, находящуюся после первого символа строки поиска
Функцияlstripstr::text,substr::text[,num::int4]textУсекает строку слева, если строка начинается с поисковой строки
Функцияrstripstr::text,substr::text[,num::int4]textУсекает строку справа, если строка заканчивается на поисковую строку
Функцияrvrsstr::text,start::int4[,_end::int4]textРеверсирует порядок символов в строке
Функцияsubstr str::text,start::int4[,len::int4]varcharВозвращает подстроку, начиная с позиции start и длиной len
Функцияswapstr::text,replace::text[,start::int4,length::int4]textПоиск и замена подстроки replace в строке, начиная с позиции start, длиной length

plvsubst​

Пакет добавляет специфичные для Oracle функции форматирования текста.

ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Процедураsetsubst[ str::text]–Задать маску поиска.
Маска по умолчанию - %s
Функцияsubst–textПолучить маску поиска
Функцияstringtemplate_in::text[,vals_in::text[,delim_in::text[,substr-in::text]]]
template_in::text[,values_in::text[][,subst::text]]		textПрименение форматирования по шаблону

Изменения уровня базы данных​

Новые обьекты:

ТипИмяВходные переменные функцииВыходные переменные функцииОписание
Представлениеdual–dummy::varcharСпецифичное для Oracle представление, необходимое для поддержки стандарта SQL
Типdummy––Специфичный для Oracle тип фиктивных данных, служащий для формирования корректного по форме запроса со всеми необходимыми полями и значениями
Типvarchar2––Специфичный для Oracle тип текстовых данных single-byte
Типnvarchar2––Специфичный для Oracle тип текстовых данных multi-byte

Доработка​

Доработка:

  1. В скриптах установки схема public заменена на ext.
  2. Ограничены привилегии.

Версия: 4.4.0.

В настоящее время используется нативный PL/pgSQL. При миграции с Oracle процедуры переписываются вручную разработчиками. Расширение orafce представляет собой портирование части функциональных пакетов Oracle в базу данных PostgreSQL, а так же типов данных Oracle и функций для работы с этими типами данных.

Использование этого расширения позволяет сократить время для миграции баз данных с Oracle на Pangolin.

orafce принципиально не меняет модель работы с данными в PostgreSQL и не добавляет новых уровней поведения в модель PostgreSQL, в частности:

  • добавление пакетов Oracle не позволяет использовать переменные пакета; функциональный класс переменных отсутствует и не может быть портирован;
  • портирована модель Oracle 10g1.

В Oracle существует возможность объединить группу функций в один мета-объект – пакет. В Pangolin такая возможность отсутствует, а для объединения функций используются отдельные схемы базы данных.

В целях сокращения затрат при портировании БД Oracle, разработчикам предоставляются схемы данных:

Схема данныхОписание
dbms_alertМежсессионное взаимодействие, message queue
dbms_assertПроверки, дополнительные тесты
dbms_outputЛогирование
dbms_pipeМежсессионное взаимодействие, каналы
dbms_randomГенератор псевдослучайных значений
dbms_utilityПросмотр стека вызовов
utl_fileРабота с файловой системой
plunitAssert-функции
plvchr
plvstr
plvsubst		Функции по работе с текстом
plvdateФункции по работе с датами
plvlexСемантический анализ запроса

Влияние на обеспечение безопасности хранимых данных:

  1. Решение добавляет функции для работы с файловой системой (пакет utl_file). При эксплуатации решения следует явно разграничить права пользователей, имеющих право на работу с файловой системой. Добавляемые функции аналогичны существующим встроенным функциям СУБД Pangolin:
  • pg_ls_dir();
  • pg_stat_file();
  • pg_read_file();
  • pg_read_binary_file().

  1. Решение добавляет функции межсессионного взаимодействия (пакет dbms_pipe, dbms_alert). При эксплуатации решения следует явно разграничить права пользователей, имеющих право на работу с механизмом межсессионного взаимодействия.

  2. В целях обеспечения безопасности отозваны права PUBLIC на пакеты, нарушающие периметр БД:

  • dbms_alert, dbms_pipe (межсессионное взаимодействие);
  • dbms_output (вывод информации в консоль);
  • dbms_utility (трассировка запросов);
  • utl_file (работа с файловой системой).

Для использования этой функциональности следует явно указать права на использование схем для необходимых ролей.

Ограничения​

  1. Для эксплуатации решения необходимо придерживаться принципа установки одинаковых версий расширения на всех узлах кластера высокой доступности, используемых в потоковой и логической репликации.
  2. Схему ext следует добавить в параметр search_path последней в порядке поиска.
  3. Все дополнительные пакеты и функции являются дополнительной функциональностью, не меняют поведения продукта в целом и требуют дополнительного тестирования в рамках миграции БД.

Установка​

Модуль считается «доверенным», поэтому его могут устанавливать пользователи, имеющие право CREATE в текущей базе данных:

CREATE EXTENSION IF NOT EXISTS orafce SCHEMA ext;

Настройка​

Настройка не требуется.

Использование модуля​

dbms_alert – модель межсессионного взаимодействия​

Сессия 1Сессия 2Сессия 3Комментарий
SELECT dbms_alert.register('alert1');SELECT dbms_alert.register('alert1');–Регистрация очереди событий alert1
SELECT * from dbms_alert.waitany(10);SELECT * from dbms_alert.waitany(10);–Ожидание событий в течение 10 секунд
––SELECT dbms_alert.signal('alert1','Alert 1');Добавление события Alert 1 в очереди alert1

dbms_assert – дополнительные проверки в целях защиты от SQL injection​

Сессия 1Результат
SELECT dbms_assert.enquote_literal(E'O\'Reilly');'O''Reilly'
SELECT dbms_assert.enquote_name(E'O\'Reilly');"o'reilly"
SELECT dbms_assert.enquote_name(E'O\'Reilly',false);"O'Reilly"
SELECT dbms_assert.noop(E'O\'Reilly');O'Reilly
SELECT dbms_assert.qualified_sql_name(E'O\'Reilly');ERROR: string is not qualified SQL name
SELECT dbms_assert.qualified_sql_name(E'noop');noop
SELECT dbms_assert.qualified_sql_name(E'noop1');noop1
SELECT dbms_assert.qualified_sql_name(E'1noop');1noop
SELECT dbms_assert.qualified_sql_name(E'noOP');noOP
SELECT dbms_assert.schema_name(E'public');public
SELECT dbms_assert.schema_name(E'noop');ERROR: invalid schema name
SELECT dbms_assert.simple_sql_name(E'public');public
SELECT dbms_assert.simple_sql_name(E'O\'Reilly');ERROR: string is not simple SQL name
SELECT dbms_assert.object_name(E'object_name');ERROR: invalid object name
SELECT dbms_assert.object_name(E'information_schema');ERROR: invalid object name
SELECT dbms_assert.object_name(E'pg_class');pg_class

dbms_output – консольный вывод сообщений​

SELECT dbms_output.enable()

 enable
--------

(1 row)
SELECT dbms_output.put(E'One\nTwo');

 put
-----

(1 row)
SELECT dbms_output.get_lines(2);

 get_lines
------------
 ("{""One  +
 Two""}",1)
(1 row)
SELECT dbms_output.get_line();

 get_line
----------
 (,1)
(1 row)
SELECT dbms_output.put_line(E'One\nTwo');

 put_line
----------

(1 row)
SELECT dbms_output.get_line();

 get_line
----------
 ("One   +
 Two",0)
(1 row)
SELECT dbms_output.serveroutput(true);

 serveroutput
--------------

(1 row)
SELECT dbms_output.put(E'One\nTwo');

 put
-----

(1 row)
SELECT dbms_output.get_line();

 get_line
----------
 ("One   +
 Two",0)
(1 row)
SELECT dbms_output.disable();

 disable
---------

(1 row)

dbms_pipe – эмуляция каналов Oracle​

Сессия 1

Сессия 2

Комментарий

SELECT dbms_pipe.create_pipe('pipe1',10,true);

Создание частного канала с именем pipe1

SELECT * from dbms_pipe.db_pipes;

Список:

name  | items | size | limit | private | owner
------+-------+------+-------+---------+-------
pipe1 |   0   |   0  |   10  |    t    | pguser
(1 row)

SELECT * from dbms_pipe.pack_message(timestamp 'epoch'+interval '2 days');

SELECT * from dbms_pipe.pack_message((date 'epoch'+interval '2 days')::date);

SELECT * from dbms_pipe.pack_message(2::int4);

SELECT * from dbms_pipe.pack_message(2::int8);

SELECT * from dbms_pipe.pack_message(2::numeric);

SELECT * from dbms_pipe.pack_message(2::text);

SELECT * from dbms_pipe.send_message('pipe1',20,0);

Вывод кода возврата: 0

SELECT dbms_pipe.receive_message('pipe1',1);

Вывод кода возврата: 0

SELECT dbms_pipe.next_item_type();

Вывод 13 (timestamp)

SELECT dbms_pipe.unpack_message_timestamp();

Вывод 1970-01-03 00:00:00+03 (для таймзоны MSK)

SELECT dbms_pipe.next_item_type();

Вывод 12 (date)

SELECT dbms_pipe.unpack_message_timestamp();

«ERROR: datatype mismatch»

SELECT dbms_pipe.unpack_message_date();

Вывод: 1970-01-03

SELECT dbms_pipe.next_item_type();

Вывод: 9 (number)

SELECT dbms_pipe.unpack_message_number();

Вывод: 2

SELECT dbms_pipe.next_item_type();

Вывод 9 (number)

SELECT dbms_pipe.unpack_message_number();

Вывод: 2

SELECT dbms_pipe.next_item_type();

Вывод 9 (number)

SELECT dbms_pipe.unpack_message_number();

Вывод: 2

SELECT dbms_pipe.next_item_type();

Вывод 11 (text)

SELECT dbms_pipe.unpack_message_text();

Вывод: 2

SELECT dbms_pipe.next_item_type();

Вывод: 0 (конец канала)

SELECT * from dbms_pipe.remove_pipe('pipe1');

dbms_random – псевдослучайные числа Oracle​

Сессия 1Результат
SELECT dbms_random.initialize(ceil(random()*1000)::int4);
SELECT dbms_random.normal();-0.0777241069451229
SELECT dbms_random.normal();0.498490513945213
SELECT dbms_random.string('u',10);UREURVOTTQ
SELECT dbms_random.string('l',10);jjrfoxiqrz
SELECT dbms_random.string('a',15);hjNFMsoOWNyKvGz
SELECT dbms_random.string('x',10);2G1T7O6KCD
SELECT dbms_random.string('p',15);0]I{VU"0m."HAW}
SELECT dbms_random.value(-10,10);0.17908088862896
SELECT dbms_random.terminate();

dbms_utility – просмотр стека вызовов​

do
$$
    declare
        res text;
    begin
        SELECT dbms_utility.format_call_stack() into res;
        raise notice 'Call stack: %',res;
    end
$$;

Пример результата запроса:

NOTICE:  Call stack: ----- PL/pgSQL Call Stack -----
  object     line  object
  handle   number  name
       0           function anonymous object
       0        5  function anonymous object
DO

utl_file – операции с файловой системой​

INSERT INTO utl_file.utl_file_dir(dir,dirname) VALUES ('temp','/tmp');
COPY (SELECT * FROM pg_settings) TO '/tmp/pg_settings.csv';

Содержимое файла /tmp/pg_settings.csv:

do
$$
declare
    f utl_file.file_type;
begin
    if (SELECT fexists from utl_file.fgetattr('temp','pg_settings.csv')) then
        f := utl_file.fopen('temp', 'pg_settings.csv', 'r');
        <<readl>>
        loop
           begin
              raise notice '%', utl_file.get_line(f);
           exception
              when no_data_found then
                 exit readl;
           end;
        end loop;
        f := utl_file.fclose(f);
    end if;
end;
$$;
SELECT utl_file.fremove('temp','pg_settings.csv');
SELECT utl_file.fclose_all();

plunit – функции проверок​

SELECT plunit.assert_equals(clock_timestamp(),current_timestamp,'Failed');

ERROR:  Failed
DETAIL:  Plunit.assertation fails (assert_equals).
SELECT plunit.assert_equals(clock_timestamp()::date,current_timestamp::date,'Failed');

 assert_equals
---------------

(1 row)
SELECT plunit.assert_not_equals(clock_timestamp(),current_timestamp,'Failed');

 assert_not_equals
-------------------

(1 row)
SELECT plunit.assert_not_equals(clock_timestamp()::date,current_timestamp::date,'Failed');

ERROR:  Failed
DETAIL:  Plunit.assertation fails (assert_not_equals).
SELECT plunit.assert_false(clock_timestamp()::date=current_timestamp::date,'Failed');

ERROR:  Failed
DETAIL:  Plunit.assertation fails (assert_false).
SELECT plunit.assert_true(clock_timestamp()=current_timestamp,'Failed');

ERROR:  Failed
DETAIL:  Plunit.assertation fails (assert_true).
SELECT plunit.assert_not_null(clock_timestamp(),'Failed');

 assert_not_null
-----------------

(1 row)
SELECT plunit.assert_null(clock_timestamp(),'Failed');

ERROR:  Failed
DETAIL:  Plunit.assertation fails (assert_null).
SELECT plunit.fail('Failed');

ERROR:  Failed
DETAIL:  Plunit.assertation (assert_fail).

plvchr – специфичные для Oracle функции при работе с текстом​

Сессия 1Результат
SELECT plvchr.char_name('Pangolin');P
SELECT plvchr.first('Pangolin');P
SELECT plvchr.last('Pangolin');n
SELECT plvchr.nth('Pangolin',2);a
SELECT plvchr.nth('Pangolin',-2);i
SELECT plvchr.quoted1('Pangolin');'Pangolin'
SELECT plvchr.quoted2('Pangolin');"Pangolin"
SELECT plvchr.stripped('Pangolin','Pango');li
SELECT plvchr.stripped('Pangolin','pango');Pli
SELECT plvchr.is_blank('Pangolin');f, false
SELECT plvchr.is_digit('Pangolin');f, false
SELECT plvchr.is_letter('Pangolin');t, true
SELECT plvchr.is_other('Pangolin');f, false
SELECT plvchr.is_quote('Pangolin');f, false
SELECT plvchr.is_quote('''Pangolin"');t, true

plvdate – специфичные для Oracle функции при работе с датами​

Сессия 1Результат
SELECT plvdate.default_holidays('Russia');
SELECT plvdate.isleapyear('2020-01-01');true
SELECT plvdate.isleapyear('2021-01-01');false
SELECT plvdate.isbizday('2023-03-08');false
SELECT plvdate.isbizday('2021-02-22');true
SELECT plvdate.add_bizdays('2021-02-22',15);2021-03-15
SELECT plvdate.bizdays_between('2021-04-30','2021-05-10');7
SELECT plvdate.days_inmonth('2023-02-01');28
SELECT plvdate.nearest_bizday('2023-02-22');2023-02-21
SELECT plvdate.next_bizday('2023-03-03');2023-03-06
SELECT plvdate.prev_bizday('2023-03-05');2023-03-03
SELECT plvdate.set_nonbizday('2021-02-22',false);
SELECT plvdate.unset_nonbizday('2021-02-20',false);ERROR: nonbizday unregisteration error
DETAIL: Nonbizday not found.
SELECT plvdate.using_easter();false
SELECT plvdate.using_greater_friday();false
SELECT plvdate.version();version
_____________
PostgreSQL PLVdate, version 3.7, October 2018
(1 row)

plvlex – специфичные для Oracle функции при работе с лексемами​

SELECT * from plvlex.tokens ('
    SELECT  n.nspname                   AS schemaname,
            c.relname                   AS viewname,
            pg_get_userbyid(c.relowner) AS viewowner,
            pg_get_viewdef(c.oid)       AS definition
    FROM pg_class c
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind = ''v''::"char";'
    ,true,true);

Пример результата выполнения запроса:

 pos |      token      | code |  class  | separator |   mod
-----+-----------------+------+---------+-----------+----------
   0 | SELECT          |  604 | KEYWORD |           |
   7 | n.nspname       |      | IDENT   |           |
  17 | as              |  290 | KEYWORD |           |
  20 | schemaname      |      | IDENT   |           |
  30 | ,               |   44 | OTHERS  |           | self
  32 | c.relname       |      | IDENT   |           |
  42 | as              |  290 | KEYWORD |           |
  45 | viewname        |      | IDENT   |           |
  53 | ,               |   44 | OTHERS  |           | self
  55 | pg_get_userbyid |      | IDENT   |           |
  70 | (               |   40 | OTHERS  |           | self
  71 | c.relowner      |      | IDENT   |           |
  81 | )               |   41 | OTHERS  |           | self
  83 | as              |  290 | KEYWORD |           |
  86 | viewowner       |      | IDENT   |           |
  95 | ,               |   44 | OTHERS  |           | self
  97 | pg_get_viewdef  |      | IDENT   |           |
 111 | (               |   40 | OTHERS  |           | self
 112 | c.oid           |      | IDENT   |           |
 117 | )               |   41 | OTHERS  |           | self
 119 | as              |  290 | KEYWORD |           |
 122 | definition      |      | IDENT   |           |
 133 | from            |  418 | KEYWORD |           |
 138 | pg_class        |      | IDENT   |           |
 147 | c               |      | IDENT   |           |
 149 | left            |  477 | KEYWORD |           |
 154 | join            |  467 | KEYWORD |           |
 159 | pg_namespace    |      | IDENT   |           |
 172 | n               |      | IDENT   |           |
 174 | on              |  524 | KEYWORD |           |
 177 | n.oid           |      | IDENT   |           |
 183 | =               |   61 | OTHERS  |           | self
 185 | c.relnamespace  |      | IDENT   |           |
 200 | where           |  689 | KEYWORD |           |
 206 | c.relkind       |      | IDENT   |           |
 216 | =               |   61 | OTHERS  |           | self
 218 | v               |      | SCONST  |           | qs
 221 | v               |  267 | OTHERS  |           | typecast
 223 | char            |      | IDENT   |           | dq
 229 | ;               |   59 | OTHERS  |           | self
(40 rows)

plvstr – специфичные для Oracle функции при работе со строками и текстовыми данными​

Сессия 1Результат
SELECT plvstr.betwn('Pangolin','go','i');goli
SELECT plvstr.instr('Pangolin','go');4
SELECT plvstr.is_prefix('Pangolin','Pan');true
SELECT plvstr.is_prefix('Pangolin','pan');false
SELECT plvstr.left('Pangolin',5);Pango
SELECT plvstr.right('Pangolin',5);golin
SELECT plvstr.lpart('Pangolin','go');Pan
SELECT plvstr.rpart('Pangolin','go');olin
SELECT plvstr.lstrip('Pangolin','go');Pangolin
SELECT plvstr.lstrip('Pangolin','Pan');golin
SELECT plvstr.rstrip('Pangolin','lin');Pango
SELECT plvstr.rvrs('Pangolin');nilognaP
SELECT plvstr.substr('Pangolin',5);olin
SELECT plvstr.swap('Pangolin','go')gongolin

plvsubst – специфичные для Oracle функции форматирования текста​

  1. Применить форматирования по шаблону:

    SELECT plvsubst.string('%s codename %s','Postgresql,Pangolin');

    или

    SELECT plvsubst.string('%s codename %s',ARRAY['Postgresql','Pangolin']);

    Результат выполнения запросов:

                string
    ------------------------------
     Postgresql codename Pangolin
    (1 row)

  2. Получить маску поиска:

    SELECT plvsubst.subst();

    Результат выполнения запросов:

     subst
    -------
     %s
    (1 row)

Ссылки на документацию разработчика​

Дополнительно поставляемый модуль orafce: https://github.com/orafce/orafce.