Ora2pg переносит схему с Oracle на PostgreSQL, и в целом переносит хорошо. Интересное начинается там, где он чего-то не осилил: он не падает и не ругается, а молча делает не то.

Берем обычную процедуру. Такие в любой оракловой схеме лежат сотнями:

CREATE OR REPLACE PROCEDURE run_as_caller  AUTHID CURRENT_USER
IS
BEGIN  DELETE FROM staging;
END;
/

Прогоняем через ora2pg. Смотрим, что получилось:

-- Generated by Ora2Pg, the Oracle database Schema converter, version 25.0
-- Nothing found of type PROCEDURE

Процедуры нет. Код возврата ноль, в логе ни ошибки, ни предупреждения, ни даже DEBUG-строки unhandled line, которая появляется для того же CREATE CLUSTER. Просто тишина.

Дальше будет еще девятнадцать таких мест. Все проверены и по каждому написано чем чинить. Но сначала разберем это, потому что оно показательное.

Процедура с AUTHID

Первая мысль была, что я налажал в примере. Убираем одну строку с AUTHID, больше ничего не трогаем:

CREATE OR REPLACE PROCEDURE run_as_caller
IS
BEGIN  DELETE FROM staging;
END;
/

CREATE OR REPLACE PROCEDURE run_as_caller () AS $body$
BEGIN  DELETE FROM staging;
END;
$body$
LANGUAGE PLPGSQL
;

Конвертируется штатно. Значит, дело именно в оговорке прав выполнения. AUTHID DEFINER ведет себя так же, тоже проверял.

Насколько это частая история: в utPLSQL, открытом фреймворке тестирования для Oracle, AUTHID стоит в 50 местах. Практически каждый package spec:

create or replace noneditionable package ut_runner authid current_user is
create or replace noneditionable package ut_coverage_helper authid definer is

То есть при миграции такого проекта от публичного API не доедет почти ничего, и узнаете вы об этом, когда приложение первый раз позовет процедуру.

Чем чинить. Убрать оговорку из исходника до конвертации, иначе объекта в выводе не будет. В готовую функцию дописать: AUTHID DEFINER это SECURITY DEFINER, AUTHID CURRENT_USER это SECURITY INVOKER, и он же в PostgreSQL по умолчанию. Обходных путей изобретать не надо, аналог прямой.

Чтобы дальше было понятно, о каком масштабе речь: вот один пакет средних размеров, в котором я собрал типовые конструкции из живого проекта. Восемь находок на 45 строк:

Разбирать будем ровно это. От самых неприятных к более мелким, общее у них одно: ora2pg на них не падает и не ругается.

TO_DATE с RR дает 1 год до нашей эры

SELECT TO_DATE('85-06-01', 'RR-MM-DD') FROM dual;

RR это оракловый код двузначного года: 00-49 читается как двухтысячные, 50-99 как тысяча девятьсот. В Oracle тут 1985 год.

ora2pg оставляет формат как есть. Выполняем на PostgreSQL 16:

      d1       |      d2
---------------+--------------- 0001-06-01 BC | 0001-06-01 BC
(1 row)

Ошибки нет, запрос отработал, вернул результат. PostgreSQL кода RR не знает и молча делает свое.

В собственных примерах Oracle (db-sample-schemas) таких INSERT-ов 211 штук:

INSERT INTO orders VALUES (2458	,TO_TIMESTAMP('16-AUG-07 02.34.12.234359 PM'	,'DD-MON-RR HH.MI.SS.FF AM'	,'NLS_DATE_LANGUAGE=American')

Загрузятся все, без единой жалобы.

Чем чинить, и тут внимательно. Первый порыв заменить RR на YY, потому что выглядит как то же самое. Я так и написал в первой версии подсказки. Потом проверил:

SELECT to_date('49-06-01','YY-MM-DD') AS yy49,       to_date('50-06-01','YY-MM-DD') AS yy50,       to_date('69-06-01','YY-MM-DD') AS yy69,       to_date('70-06-01','YY-MM-DD') AS yy70;

    yy49    |    yy50    |    yy69    |    yy70
------------+------------+------------+------------ 2049-06-01 | 2050-06-01 | 2069-06-01 | 1970-06-01

Порог у PostgreSQL 69/70, у оракловского RR порог 49/50. Совпадают на 00-49 и 70-99, а на 50-69 расходятся ровно на сто лет: '65' в Oracle это 1965, а через YY в PostgreSQL это 2065. Ровно диапазон дат рождения и почти любых данных середины прошлого века.

То есть такая замена меняет громкую поломку на тихую, и это хуже. Правильно приводить входные данные к четырехзначному году и использовать YYYY.

Отдельно забавно, что ora2pg вообще-то умеет обрабатывать RR: в TO_CHAR он честно меняет его на YY. А в TO_DATE не трогает. Чинит там, где не надо (на выводе RR и YY дают одно и то же), и не чинит там, где надо.

LONG RAW уезжает в text вместо bytea

CREATE TABLE binstuff (    id       NUMBER PRIMARY KEY,    a_raw    RAW(200),    a_long   LONG,    a_lraw   LONG RAW,    a_blob   BLOB,    a_clob   CLOB,    a_bfile  BFILE
);

Свалил все типы в один пример специально, чтобы сравнивать в одном прогоне:

CREATE TABLE binstuff (	id bigint,	a_raw bytea,	a_long text,	a_lraw text,	a_blob bytea,	a_clob text,	a_bfile bytea
) ;

RAW(200), BLOB, BFILE в bytea. А LONG RAW, который тоже двоичный, в text.

Полез в исходники ora2pg, lib/Ora2Pg/Oracle.pm, строка 45:

	'LONG RAW' => 'bytea',

И в документации то же самое, в значении по умолчанию для DATA_TYPE. То есть это не выбор и не компромисс, а расхождение конвертера с собственной документацией. Похоже, парсер DDL режет по пробелу и матчит сначала LONG.

CREATE TABLE проходит чисто, так что на этапе схемы не заметите. Прилетит на переносе данных, потому что в text произвольные байты не лезут:

ERROR:  invalid byte sequence for encoding "UTF8": 0x00

Чем чинить. Поправить тип на bytea руками. Это ровно то, что сам ora2pg и декларирует.

Обработчик исключений, который не сработает никогда

CREATE OR REPLACE PROCEDURE ins_one IS  dup_key EXCEPTION;  PRAGMA EXCEPTION_INIT(dup_key, -1);
BEGIN  INSERT INTO uniq_t (id) VALUES (1);
EXCEPTION  WHEN dup_key THEN    DBMS_OUTPUT.PUT_LINE('handled duplicate');
END;
/

ORA-00001 это нарушение уникальности, в Oracle процедура напечатает handled duplicate. После конвертации:

CREATE OR REPLACE PROCEDURE ins_one () AS $body$
BEGIN  INSERT INTO uniq_t(id) VALUES (1);
EXCEPTION  WHEN SQLSTATE '50001' THEN    RAISE NOTICE 'handled duplicate';
END;
$body$

PRAGMA выброшен, обработчик переписан на SQLSTATE '50001'. Проверил на -1 и на -60 (взаимоблокировка), в обоих случаях 50001. Это константа-заглушка, номер ORA в нее не заезжает вообще.

Процедура создается без ошибок. Вызываем на живой базе с настоящим ограничением уникальности:

ERROR:  duplicate key value violates unique constraint "uniq_t_pkey"
CONTEXT:  SQL statement "INSERT INTO uniq_t(id) VALUES (1)"
PL/pgSQL function ins_one() line 3 at SQL statement

Не сработал. Настоящий код PostgreSQL для этой ошибки 23505, а 50001 он не возбуждает никогда. Обработчик стал мертвым кодом, и ошибка, которую в Oracle аккуратно глотали, теперь летит наружу.

В utPLSQL и Logger таких мест 44.

Чем чинить. Сопоставить каждый номер ORA с настоящим кодом PostgreSQL и заменить 50001. Читаемее через именованные условия, они же в PL/pgSQL пишутся прямо в WHEN.

Вот карта для того, что встречается чаще всего. Правую половину я снял с живого PostgreSQL 16, возбуждая каждую ошибку по очереди и печатая SQLSTATE. Левая половина, номера ORA, взята из документации Oracle: базы под рукой у меня нет, так что за нее ручаюсь слабее.

И сразу предупреждение, ради которого эту таблицу стоило собирать: соответствие не взаимно однозначное, массовой заменой по номеру не отделаетесь. ORA-02291 и ORA-02292 в Oracle это две разные ситуации (нет родителя против «есть потомок, не дам удалить»), а PostgreSQL на обе отвечает одним 23503, я это проверил отдельно. В обратную сторону хуже: ORA-06502 (numeric or value error) в зависимости от того, что именно случилось, превращается то в 22001, то в 22003. Так что каждый обработчик придется читать глазами и решать, что он вообще ловил.

Еще пять, которые стоит знать

FOLLOWS у триггеров ломает не порядок, а всю таблицу.

В Oracle это оговорка «срабатывай после такого-то триггера». ora2pg ее не теряет, а роняет внутрь тела сгенерированной функции, между AS $BODY$ и BEGIN:

CREATE OR REPLACE FUNCTION trigger_fct_trg_b() RETURNS trigger AS $BODY$
FOLLOWS trg_a
BEGIN  NEW.audited := 'Y';
RETURN NEW;
END
$BODY$

CREATE FUNCTION и CREATE TRIGGER проходят без единой ошибки, потому что в дампе ora2pg стоит check_function_bodies = false и тело на загрузке не разбирается. А на первом же INSERT:

ERROR:  syntax error at or near "FOLLOWS"
LINE 2: FOLLOWS trg_a

То есть отвалилась не приоритезация триггеров, а вообще любая запись в таблицу. Это, кстати, общая черта всего, что попадает внутрь тела функции: до прода доезжает молча.

Чинить: в PostgreSQL порядка «после такого-то» нет вообще, триггеры на одном событии идут по алфавиту имен. Проверял специально: t10_first отработал раньше t20_second, хотя создан был позже. Так что оговорку выкидываем, а порядок обеспечиваем именованием.

WITH READ ONLY у представлений исчезает бесследно.

CREATE OR REPLACE VIEW v_emp AS  SELECT emp_id, name FROM employees  WITH READ ONLY;

На выходе:

CREATE OR REPLACE VIEW v_emp AS SELECT emp_id, name FROM employees;

Оговорки нет. И вот это тот случай, когда ошибки не будет вообще никогда: простое представление в PostgreSQL по умолчанию обновляемое, поэтому запись через него спокойно проходит.

INSERT 0 1 emp_id |               name
--------+----------------------------------    999 | written through a READ ONLY view
(1 row)

Строка правда легла в базовую таблицу. В Oracle тут было бы ORA-42399. Защита, объявленная в определении самого объекта, после миграции просто перестает существовать, и узнать об этом можно только когда кто-то что-то запишет.

Чинить: правами (REVOKE INSERT, UPDATE, DELETE ON <view>, проверял, дает permission denied for view) либо триггером INSTEAD OF, который бросает исключение.

ROWNUM в UPDATE и DELETE.

ora2pg переписывает WHERE ROWNUM <= 10 в LIMIT 10. Для SELECT это ровно то, что нужно. Но у UPDATE и DELETE в PostgreSQL никакого LIMIT нет:

ERROR:  syntax error at or near "LIMIT"
LINE 1: UPDATE employees SET bonus = 0 LIMIT 10;

Хорошая новость: паниковать по каждому вхождению ROWNUM не надо. Во вложенном подзапросе все конвертируется корректно и работает, проверял отдельно:

DELETE FROM employees WHERE emp_id IN (SELECT emp_id FROM staff LIMIT 5);

Это нормальный PostgreSQL. Ломается только когда ROWNUM стоит прямо в DML.

Чинить: подзапросом по первичному ключу. И туда обязательно дописать ORDER BY.

Тут стоит остановиться, потому что момент неочевидный. В Oracle ROWNUM присваивается до сортировки, а не после. То есть если в исходнике было WHERE ROWNUM <= 10 ORDER BY created_at, то Oracle брал десять произвольных строк и только потом сортировал эту десятку. Не десять самых старых, как обычно думают читающие такой код. Поэтому явный ORDER BY внутри подзапроса в PostgreSQL не просто «ближе к оригиналу», он честнее оригинала: делает выбор строк детерминированным там, где в Oracle его не было.

Из этого следует неприятное: переписывая такое место, вы можете нечаянно починить давнюю плавающую логику, на которую кто-то мог опереться. Стоит хотя бы посмотреть, что этот UPDATE вообще делал.

Системные триггеры превращаются в таблицу с именем database.

Триггер на событие базы, не на таблицу:

CREATE OR REPLACE TRIGGER trg_logon
AFTER LOGON ON DATABASE
BEGIN  INSERT INTO login_audit (who, when_) VALUES (USER, SYSDATE);
END;
/

ora2pg честно пытается сделать из этого обычный табличный триггер, подставив слово database туда, где должно быть имя таблицы:

CREATE TRIGGER trg_logon	AFTER LOGON ON database FOR EACH ROW	EXECUTE PROCEDURE trigger_fct_trg_logon();

ERROR:  syntax error at or near "LOGON"

То же самое с ON SCHEMA и с любым событием: BEFORE DDL, AFTER SERVERERROR, STARTUP.

Чинить: единого рецепта нет, зависит от события. DDL-события переводятся на событийные триггеры PostgreSQL (CREATE EVENT TRIGGER ... ON ddl_command_end, работает, проверял).

А LOGON, LOGOFF и SERVERERROR триггерами не покрываются вообще, там другой инструмент. Если LOGON-триггер писал аудит подключений, в PostgreSQL это делается настройками сервера: log_connections и log_disconnections (оба по умолчанию off, я проверил на чистой 16-й) плюс разбор лога, а если нужен аудит побогаче, то расширение pgaudit. Для SERVERERROR аналогично: log_min_error_statement и парсинг лога. Общий смысл в том, что из базы это переезжает наружу, в сбор логов, и если аудит подключений у вас был обязательным по требованиям, закладывайте это отдельной задачей, а не строчкой в чеклисте миграции.

Альтернативные кавычки q’[…]'.

Оракловый способ написать строку с апострофами, не удваивая их. Копируется как есть, и PostgreSQL читает q как отдельный идентификатор, после чего разбор уезжает:

ERROR:  mismatched parentheses at or near "]"
LINE 4:   msg varchar(100) := q'[it's a test]';

Опять же внутри тела функции, то есть загрузка чистая, падает при первом вызове.

Штука неочевидно частая. Когда я гонял детекторы по открытому коду, q'[...]' дал 706 срабатываний, больше всех остальных вместе взятых. В одном utPLSQL их сотни, там на них построены целые скрипты установки.

Чинить: долларовые кавычки PostgreSQL, $q$it's a test$q$. Внутри них экранировать не нужно ничего, то есть замена один в один по смыслу.

Что еще в списке

Чтобы не растягивать: тем же способом подтверждены и разобраны IGNORE NULLS у аналитических функций, NLSSORT, SYS.ANYDATA как тип столбца, оператор TABLE(...) во FROM, курсорное выражение CURSOR(SELECT ...), FOR UPDATE ... WAIT n, SUBTYPE ... RANGE, SDO_GEOMETRY, GOTO, <курсор>%ROWTYPE и WM_CONCAT. По каждому лежит разбор с минимальным примером и фактическим выводом обеих команд, ссылка в конце.

Отдельно отмечу WM_CONCAT: недокументированный оракловый агрегат, официально не поддерживался никогда и убран с 12c, но в легаси попадается регулярно. ora2pg копирует его как есть, хотя документированный LISTAGG тот же ora2pg честно переписывает в string_agg. Если встретите, меняйте на string_agg(col, ',' ORDER BY col) и порядок дописывайте сразу: WM_CONCAT его не гарантировал, так что «как было» все равно не воспроизвести.

Посмотрите у себя

Это все проверяется на своей схеме за минуту, без всяких инструментов. Гребем по исходникам:

E='--include=*.sql --include=*.pks --include=*.pkb --include=*.trg'
# процедуры и пакеты, которые пропадут целиком
grep -rilE $E "authid[[:space:]]+(current_user|definer)" .
# обработчики исключений, которые перестанут срабатывать
grep -rinE $E "pragma[[:space:]]+exception_init" .
# даты, которые уедут в 1 год до нашей эры
grep -rinE $E "'[^']*(DD|MM|MON|HH)[^']*\bRR+\b[^']*'" .
# альтернативные кавычки (тут только один вид скобок, их бывает больше)
grep -rn $E "q'\[" .
# двоичные столбцы, которые станут text
grep -rinE $E "\blong[[:space:]]+raw\b" .
# представления, которые перестанут быть только для чтения
grep -rin $E "with read only" .

Грубо, но для первой прикидки хватает: на том открытом коде, что я гонял, pragma exception_init этот греп нашел 43 раза против 44 у нормального парсера, а даты с RR 213 против 211. Если хоть один из них что-то выдал, у вас после миграции будет ровно то, что описано выше.

А как проверить, что после миграции все верно

Греп выше это «до». Про «после» скажу коротко, потому что универсального рецепта у меня нет, а выдумывать не хочу.

Главная ловушка в том, что большинство описанного выше не падает на загрузке схемы. Значит критерий «схема развернулась без ошибок» ничего не проверяет, и на него опираться нельзя. Работает другое:

• прогнать функции и процедуры, а не только загрузить. Все, что попадает внутрь тела (GOTO, q'[...]', %ROWTYPE от курсора, FOLLOWS), лежит тихо до первого вызова, потому что ora2pg ставит в дампе check_function_bodies = false. Хоть какой-то смоук-тест, дергающий каждую процедуру, ловит этот класс целиком

• сравнить данные, а не только их наличие. RR-даты и LONG RAW дадут одинаковое число строк и разное содержимое, так что count(*) тут бесполезен, нужна сверка значений хотя бы по контрольным суммам колонок

• отдельно проверить то, что вообще не должно работать: попробовать записать в бывшее READ ONLY представление и убедиться, что не пустило

• пересчитать объекты. Пакеты с AUTHID до целевой базы не доедут, и единственный способ это заметить - сверить список объектов в исходнике со списком в PostgreSQL

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

Что проверил и оказалось нормально

Не менее полезный список, чтобы вы не тратили на это время. Все конвертируется корректно:

• Старый синтаксис внешнего соединения (+), нормально переписывается в LEFT OUTER JOIN

• ORDER SIBLINGS BY, уезжает в рекурсивный CTE с массивом-иерархией, порядок братьев на реальных данных верный (проверял с данными, а не на глаз)

• SYS_GUID(), и расширение подключает само

• NUMTODSINTERVAL / NUMTOYMINTERVAL, значения на выходе правильные

• SELECT UNIQUE (оракловый синоним DISTINCT), SYS_REFCURSOR, NOCOPY, обычный LONG, INTERVAL YEAR TO MONTH, ENABLE ROW MOVEMENT, ROW ARCHIVAL, SCALE/ORDER у последовательности

Как это проверялось

Стенд простой: ora2pg 25.0, PostgreSQL 16, на каждый кандидат минимальный пример на Oracle, прогон, заливка результата в базу. Если не упало и ведет себя как в Oracle, кандидат идет в отказ, независимо от того, что написано в документации.

Отдельно прогнал детекторы по реальному открытому коду: utPLSQL, alexandria-plsql-utils, оракловые db-sample-schemas и Logger, 766 файлов, 229787 строк. Все находки прошел глазами по исходникам. Отсюда, собственно, и цифры вроде «50 раз в utPLSQL» выше, это не оценка, а посчитанное.

Оттуда же привычка проверять не только сами находки, но и советы по ним: на прошлом заходе прогон по корпусу нашел настоящий баг в моем коде, который синтетические тесты не увидели. А проверка советов нашла ту самую ошибку с RR и YY, про которую я написал выше. Так что если у вас есть чеклист миграции, где написано «RR меняем на YY», проверьте.

Инструмент

Гребы выше ловят пять штук из двадцати. Остальное я собрал в открытый сканер, который гоняется по исходникам до конвертации, подключение к базе не нужно: pip install ora2pg-gap-report.

По каждой находке он говорит, на чем она подтверждена и на каком этапе рванет:

Репозиторий: https://github.com/Lunch418/ora2pg-gap-report, Apache 2.0. Там же разборы по всем находкам, с минимальным примером и фактическим выводом обеих команд.

Если у вас есть оракловая схема и вы гоняли ее через ora2pg, мне интересны кейсы, которые я не покрыл. Заводите issue с минимальным примером, я прогоню через тот же стенд и либо добавлю, либо напишу, что все конвертируется нормально.