Re: Oracle database into PostgreSQL using Ora2PG tool.

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

>
> 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
>
>
try to use master from github

Regards

Pavel