transpiler-features.md

Transpiler Features

The CYBERTEC Migrator is able to parse PL/SQL code, analyze it and transpile it to PL/pgSQL.
Each supported construct is represented by a corresponding rule.

This feature-set is under ongoing development, adding new rules wherever possible. If you have a suggestion for a new rule, please open a feature request.

Rules

CYAR-0001: AddParamlistParentheses

In Oracle, a procedure without parameters may omit parentheses in its definition, whereas PostgreSQL always requires them.

Example

-- PL/SQL
CREATE PROCEDURE proc   IS ...;
-- PL/pgSQL
CREATE PROCEDURE proc() IS ...;

CYAR-0002: ReplacePrologue

The bodies of Oracle's functions and procedures are initiated with IS|AS BEGIN ..., and have to be adapted to PostgreSQL's AS $$ ....

Example

-- PL/SQL
CREATE PROCEDURE proc IS    BEGIN ...;
-- PL/pgSQL
CREATE PROCEDURE proc AS $$ BEGIN ...;

CYAR-0003: ReplaceEpilogue

As the counterpart to CYAR-0002, this replaces the epilogue of Oracle functions and procedures, stripping the optional block identifier and specifying the LANGAGE.

Example

-- PL/SQL
CREATE PROCEDURE proc IS BEGIN ... END;
-- PL/pgSQL
CREATE PROCEDURE proc IS BEGIN ... END proc; $$ LANGUAGE plpgsql;

CYAR-0004: FixTrunc

Transpiles usages of Oracle's trunc() function based on the argument type.

Note: This rule is not yet implemented.

CYAR-0005: ReplaceSysdate

Transpiles Oracle's SYSDATE, which returns the current time of the operating system (as opposed to statement or transaction timestamp), to PostgreSQL's clock_timestamp().

Example

-- PL/SQL
SELECT sysdate           FROM dual;
-- PL/pgSQL
SELECT clock_timestamp() FROM dual;

CYAR-0006: ReplaceNvl

Replaces Oracle's NVL with PostgreSQL's coalesce.

Example

-- PL/SQL
SELECT nvl(dummy, 'null')      FROM dual;
-- PL/pgSQL
SELECT coalesce(dummy, 'null') FROM dual;

CYAR-0007: RemoveEditionable

As PostgreSQL does not have the concept of editioning, the EDITIONABLE and NONEDITIONABLE keywords of functions and procedures are omitted.

Example

-- PL/SQL
CREATE EDITIONABLE PROCEDURE proc ...;
-- PL/pgSQL
CREATE             PROCEDURE proc ...;

CYAR-0008: ReplaceNvl2

Replaces Oracle's NVL2 with a CASE statement.

Example

-- PL/SQL
SELECT NVL2(dummy, 1, 2)                             FROM dual;
-- PL/pgSQL
SELECT CASE WHEN dummy IS NOT NULL THEN 1 ELSE 2 END FROM dual;