Issue migrating oracle package to postgres using ora2pg

Question

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

Answer 1

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.

• nakala: Thanks, do I have to rewrite all of the other statements? They are 62 but only 10 are failing
• Laurenz Albe: This and the COMMIT are the only ones I noticed. You'll have to test.
• nakala: What is wrong with the commit btw?
• Laurenz Albe: You are not allowed to use it in a function.
• nakala: I am marking it as an answer. All of the code will have to be rewritten but the PERFORM passed.