0

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

0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.