Migration of PL/SQL Code from Oracle to PostgreSQL

All the issues addressing the Oracle to PostgreSQL migration of packages, stored procedures, functions, and triggers are explored in this section.

Packages. When migrating packages from Oracle to PostgreSQL, it’s important to note that PostgreSQL does not have a built-in equivalent feature for packages. However, this functionality can be emulated by organizing all related components belonging to a package within a PostgreSQL schema that shares the same name. In this approach, global variables can be stored in a dedicated service table within the schema. This allows for logical grouping and organization of code similar to packages in Oracle, despite the absence of a direct package concept in PostgreSQL.

Types casting. In PostgreSQL, strict type casting is required when invoking functions, operators, or when inserting and updating data in tables using the results of expressions. To address this requirement, a workaround is available using the pseudo-type ‘anyelement’. This pseudo-type allows for more flexible handling of different data types within the context of function calls and data manipulation operations in PostgreSQL. By utilizing ‘anyelement’, the strict type casting requirement can be bypassed, providing a more versatile approach to handling data with varying types:

create or replace function my_concat(str1 anyelement, str2 anyelement)

returns varchar

language plpgsql

as $$

begin

return str1::varchar || str2::varchar;

end;

$$;

Stored procedure/function must have either one argument of anyelement type or all parameters of the same type.

Sequences. Oracle and PostgreSQL have similar syntax of declaring sequences. For example:

CREATE SEQUENCE SEQ1 START WITH 10 INCREMENT BY 2 MAXVALUE 1000000 CACHE 20 NO CYCLE;

However, there is a difference in referencing the next value of a sequence. In Oracle, the syntax is ‘sequence.nextval’, whereas in PostgreSQL, it is ‘nextval(‘sequence’)’.

Autonomous Transactions. In Oracle, autonomous transactions enable a subprogram to independently commit or rollback SQL operations without affecting the main transaction. This functionality allows certain operations, such as inserting data into a table within an insert-trigger, to succeed even if the main transaction fails. To achieve this, the corresponding INSERT statement must be enclosed within an autonomous transaction. This ensures that the insert operation is handled separately from the main transaction, providing the desired level of independence and allowing for specific operations to proceed regardless of the outcome of the main transaction:

CREATE OR REPLACE PROCEDURE insert_critical_data(v_data varchar2)

IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

    INSERT INTO info_table(data) VALUES (v_data);

    commit;

END;

PostgreSQL does not provide direct support for autonomous transactions. However, there are workarounds available to achieve similar functionality. One approach is to refactor the code when converting from Oracle to PostgreSQL, so that autonomous transactions are not required. Alternatively, you can utilize the ‘dblink’ module in PostgreSQL. With dblink, you can establish a new connection and execute a query using that connection, which is immediately committed regardless of the main transaction’s outcome. This allows you to perform operations, such as inserting a row into a table, that will be committed even if the calling transaction is rolled back:

CREATE OR REPLACE FUNCTION insert_critical_data(v_data TEXT)

RETURNS void

AS

$BODY$

DECLARE

v_sql text;

BEGIN

    PERFORM dblink_connect(‘myconn’,

         ‘dbname=mydbname user=… password=… host=… port=…’);

v_sql := format(‘INSERT INTO info_table (data)

         VALUES (%L)’, v_data);

    PERFORM dblink_exec(‘myconn’, v_sql);

    PERFORM dblink_disconnect(‘myconn’);

END;

$BODY$

LANGUAGE  plpgsql;

Triggers. In Oracle, the source code of a trigger’s body is included within the CREATE TRIGGER statement itself. However, in PostgreSQL, the trigger source code must be composed as a separate standalone function, which is then referenced from the CREATE TRIGGER statement. This particularity allows PostgreSQL to maintain a clearer separation between the trigger’s definition and its associated logic:

CREATE FUNCTION UpdateTranscriptionistID_proc() RETURNS trigger AS $$

BEGIN

     UPDATE Study

     SET AssignedTranscriptionist = NEW.CreatedBy

     WHERE StudyUID = NEW.StudyUID AND ReportCount = 0;

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER UpdateAssignedTranscriptionistID

     AFTER INSERT OR UPDATE ON Reports

     FOR EACH ROW EXECUTE PROCEDURE UpdateAssignedTranscriptionistID_proc();

Built-in Functions. Oracle and PostgreSQL offer similar sets of built-in functions, although they are not identical. The following table presents functions that need to be ported to their equivalent counterparts during Oracle to PostgreSQL migration:

Oracle	PostgreSQL
ADD_MONTH($date,$n_month)	$date + $n_month  * interval ‘1 month’
DECODE($exp, $when, $then, …)	CASE $exp WHEN $when THEN $then … END
INSTR($str1, $str2)	POSITION($str2 in $str1)
ROWNUM	*row_number() over ()
SYSDATE	CURRENT_DATE
SYS_GUID()	uuid_generate_v1()

* Oracle condition ‘where rownum< N’ must be converted into ‘limit N’ in PostgreSQL

For Oracle to PostgreSQL migration projects Intelligent Converters software company released two products: Oracle-to-PostgreSQL and the Code Converter. First tool completely automates migration of schema, data, indexes, constraints and foreign keys. Second tool designed to partially automate processing triggers, stored procedures, functions and views. This tool converts most of Oracle built-in functions and PL/SQL patterns into PostgreSQL format. Although manual post-processing of the output source code was required, the converter reduced total duration of Oracle to PostgreSQL migration project at 40%.