How to Migrate PL/SQL from Oracle to PostgreSQL
One of the most complicated part of Oracle to PostgreSQL database migration is store procedures, functions and triggers since conversion of PL/SQL code can hardly be automated. This guide explores main differences between dialects of Oracle and PostgreSQL procedure languages. Although these dialects of procedure language are similar, there are important differences that should be handled carefully while migrating between the two DBMS:
- If there are variable and column with the same name used in a SQL query, Oracle treats it as a column name and PostgreSQL – as a variable name. The good coding practice is to avoid such ambiguities.
- While Oracle uses packages to organize functions into semantic groups, PostgreSQL uses schema for the same purpose.
- PostgreSQL does not have package-level variables, it should be emulated as data of temporary service table.
- PostgreSQL considers the function body as a string, so it is necessary to enclose it in dollar quotes.
- PostgreSQL requires ‘LANGUAGE’ specification at the end of function since PL/PgSQL is not the only possible language there.
- Unlike Oracle, PostgreSQL does not allow ‘COMMIT’ statement inside function.
- There is no equivalent of ‘show errors’ statement in PostgreSQL, however it does not cause an issue since the DBMS reports errors automatically.
- Built-in functions must be converted according to rules described on this page: https://www.convert-in.com/docs/ora2pgs/views.htm
The examples below illustrate porting stored functions from Oracle to PostgreSQL. First function is simple one concatenating two strings. In Oracle:
CREATE OR REPLACE FUNCTION custom_concat(str1 varchar, str2 varchar)
RETURN varchar IS
BEGIN
IF str2 IS NULL THEN
RETURN str1;
END IF;
RETURN str1 || ‘*’ || str2;
END;
/
PostgreSQL equivalent is:
CREATE OR REPLACE FUNCTION custom_concat(str1 varchar, str2 varchar)
RETURNS varchar AS $$
BEGIN
IF str2 IS NULL THEN
RETURN str1;
END IF;
RETURN str1 || ‘*’ || str2;
END;
$$ LANGUAGE plpgsql;
The second example is more complicated, it is a function extracting data from table and processing it before returning via OUT-parameter:
CREATE OR REPLACE PROCEDURE get_balance(
v_ID IN VARCHAR,
v_balance OUT NUMBER)
BEGIN
SELECT SUM(
DECODE(
BALANCE_SIGN, ‘+’, BALANCE_AMOUNT,
-1 * BALANCE_AMOUNT)) INTO v_balance
FROM ACCOUNTING
WHERE
ID LIKE v_ID AND
months_between(CURRENT_TIMESTAMP, p_date) < 2
GROUP BY ID;
END;
/
Porting this function to PostgreSQL requires intermediate function to implement Oracle months_between:
CREATE OR REPLACE FUNCTION pgs_months_between(interval)
RETURNS int AS $$
SELECT
EXTRACT(years from $1)::int * 12 +
EXTRACT(years from $1)::int;
$$ LANGUAGE plpgsql;
And PostgreSQL equivalent of the original function is:
CREATE OR REPLACE FUNCTION get_balance(
v_ID IN VARCHAR,
v_balance OUT NUMERIC)
AS $$
BEGIN
SELECT SUM(
(CASE
WHEN BALANCE_SIGN=’+’ THEN BALANCE_AMOUNT
ELSE -1 * BALANCE_AMOUNT
END) INTO v_balance
FROM ACCOUNTING
WHERE
ID LIKE v_ID AND
abs(pgs_months_between(age(CURRENT_TIMESTAMP, p_date))) < 2
GROUP BY ID;
END;
$$ LANGUAGE plpgsql;
The third example demonstrates using cursors to fetch more than one row of data. Oracle style:
CREATE OR REPLACE PROCEDURE cursor_demo IS
CURSOR cursor1 IS SELECT * FROM table1;
BEGIN
FOR row1 IN cursor1 LOOP
dbms_output.put_line(row1.col1 || row1.col2);
END LOOP;
END;
/
The same procedure in PostgreSQL should look like:
CREATE OR REPLACE PROCEDURE cursor_demo()
AS $$
DECLARE
cursor1 CURSOR IS SELECT * from table1;
BEGIN
FOR row1 IN cursor1 LOOP
raise notice ‘% %’,row1.col1,row1.col2;
END LOOP;
END;
$$ LANGUAGE plpgsql;
The given examples prove that migration of stored procedures and functions from Oracle to PostgreSQL can be a tricky process involving many challenges. But if you have necessary skills and the list of steps to implement, it becomes much easier and smoother. This article described just a few main aspects to consider when migrating source code of procedures and functions from Oracle to PostgreSQL.
