Re: Oracle database into PostgreSQL using Ora2PG tool.

On Fri, Jun 2, 2017 at 2:13 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

> 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
>>
>>
>>
>>
>>
>
> Hi Pavel,

Thanks for the response, I am using.

c:\ora2pg>ora2pg -c ora2pg.conf -v
Ora2Pg v18.1