Re: Oracle database into PostgreSQL using Ora2PG tool.

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

2017-06-02 10:16 GMT+02:00 PAWAN SHARMA <er.pawanshr0963@xxxxxxxxx>:

Hi All,

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;

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

BEGIN

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

                  OLD.job_id, OLD.department_id);

RETURN NEW;

END

$BODY$

 LANGUAGE 'plpgsql';

CREATE TRIGGER update_job_history

AFTER UPDATE ON employees FOR EACH ROW

EXECUTE PROCEDURE trigger_fct_update_job_history();

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

ERROR:  syntax error at or near "add_job_history"

LINE 4:   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMES...

          ^

NOTICE:  relation "employees" does not exist, skipping

maybe you have too old version of ora2pg. Master ora2pg is able to emulate procedure call by SELECT command.

Regards

Pavel

 

Please Suggest or help to resolve it.

-Pawan