Re: Oracle database into PostgreSQL using Ora2PG tool.

On 2 June 2017 at 04:16, PAWAN SHARMA <er(dot)pawanshr0963(at)gmail(dot)com> wrote:
> 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';

The examples here
https://www.postgresql.org/docs/9.5/static/sql-createfunction.html
have a semi colon after 'END', is that the syntax error?

> 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

Since this is a NOTICE maybe it's a red herring? the results of a
CREATE IF NOT EXISTS or similar?

--
Neil Anderson
neil(at)postgrescompare(dot)com
https://www.postgrescompare.com