Code Transpilation

The CYBERTEC Migrator is able to parse, analyze and transpile certain PL/SQL constructs to PL/pgSQL automatically. 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.

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

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

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

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

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

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

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

This rule has yet to be implemented.

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().

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

Replaces Oracle’s NVL with PostgreSQL’s coalesce.

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

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

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

Replaces Oracle’s NVL2 with a CASE statement.

-- 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;