Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

I am migrating Oracle database into PostgreSQL using Ora2PG tool.

So, I am facing one issue with trigger after generating script output of Oracle database.

Oracle : 

CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY 

    AFTER UPDATE OF JOB_ID, DEPARTMENT_ID ON EMPLOYEES 

    FOR EACH ROW 

BEGIN

  add_job_history(:old.employee_id, :old.hire_date, sysdate,

                  :old.job_id, :old.department_id);

END; 

/ 

The script generated by Ora2PG tool.

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;

when I try to run the above-generated script it will show below error.

Please Suggest or help to resolve it.

-Pawan

When you call a function inside PlSQL code and don’t care about returned value, then you do: PERFORM function_name(…).

Otherwise you do: SELECT function_name(…) INTO your_variable;

So:

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;

CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS $BODY$

BEGIN

PERFORM  add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,

                  OLD.job_id, OLD.department_id);

RETURN NEW;

END

$BODY$

 LANGUAGE 'plpgsql';

Regards,

Igor Neyman

 P.S. Also you are missing semicolon (END;):

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;

CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS $BODY$

BEGIN

PERFORM  add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,

                  OLD.job_id, OLD.department_id);

RETURN NEW;

END;

$BODY$

 LANGUAGE 'plpgsql';