Re: Oracle database into PostgreSQL using Ora2PG tool.

Hi

2017-06-02 10:16 GMT+02:00 PAWAN SHARMA <er(dot)pawanshr0963(at)gmail(dot)com>:

> 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
>
>
>
>
>