Oracle to PostgreSQL Migration – Issue with ora2pg

migrationoraclepostgresql

I was migrating an oracle(12c) package to postgres(Enterprise db 10.5) using ora2pg.
The migration run successfully for most of the functions. However, one function referenced multiple times in the package is causing errors.
I am giving both versions of the code below:
oracle

create or replace package body sms_package as
--- Other functions
PROCEDURE LOG_MESSAGE (LogType varchar2, LogDetails varchar2) IS
    BEGIN
        INSERT INTO SMS_TABLE (REF_DATE, LOG_TYPE, LOG_DETAILS)
        VALUES (SYSDATE, LogType, LogDetails);
        COMMIT;
    END;

ora2pg

CREATE OR REPLACE FUNCTION log_message (LogType text, LogDetails text) RETURNS VOID AS $body$
BEGIN
        INSERT INTO SMS_TABLE(REF_DATE, LOG_TYPE, LOG_DETAILS)
        VALUES (clock_timestamp(), LogType, LogDetails);
        COMMIT;
    END;
$body$
LANGUAGE PLPGSQL
;

An example of the references:
oracle

procedure sendtest (dash_type varchar2, sms_message varchar2) IS
        cursor x is
        SELECT ISDN FROM  oracle_store;
    begin
        dbms_output.put_line('1');
            if sms_message is not null then
                for i in x loop
                    insert into s_table(message_text, received_time, DELIVERY_STATUS, retry_attempts) values (..);
                    sms_package.LOG_MESSAGE (dash_type, 'sending to '||i.ISDN||' message =>'||sms_message);
                    dbms_output.put_line('notification_type='||dash_type||' sms='||sms_message);
                end loop;
                commit;
            end if;
    end;

ora2pg

CREATE OR REPLACE FUNCTION sendtest (dash_type text, sms_message text) RETURNS VOID AS $body$
DECLARE
        x CURSOR FOR
        SELECT ISDN FROM  postgres_store;
BEGIN
        RAISE NOTICE '1';
            if sms_message is not null then
                for i in x loop
                    insert into s_table(message_text, received_time, DELIVERY_STATUS, retry_attempts) values ();
                    sms_package.LOG_MESSAGE(dash_type, 'sending to '||i.ISDN||' message =>'||sms_message);
                    RAISE NOTICE 'notification_type=% sms=%', dash_type, sms_message;
                end loop;
                commit;
            end if;
    end;
$body$
LANGUAGE PLPGSQL

There appears to be a problem in calling the function from within another function as am getting this error

[42601] ERROR: syntax error at or near "sms_package"

How can I execute another function from within a function in postgres. How can I call that function from within the another function

Best Answer

Since sms_package.LOG_MESSAGE is a function in PostgreSQL, you have to call it using PERFORM in PL/pgSQL:

PERFORM sms_package.LOG_MESSAGE(...);

But there are other problems in your code. At least the COMMIT in the finction definition will cause an error.

You may have to redesign and rewrite your code.