Re: Oracle database into PostgreSQL using Ora2PG tool.

On Fri, Jun 2, 2017 at 2:20 PM, Andrew Sullivan <ajs(at)crankycanuck(dot)ca> wrote:

> (Offlist: phone)
>
> It's saying the table isn't there. Is it? Is it maybe spelled
> "EMPLOYEES" (all caps) instead? You need double quotes if so.
>
> A
>
> --
> Andrew Sullivan
> Please excuse my clumbsy thums.
>
> On Jun 2, 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';
>
> 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
>
>
>
> Please Suggest or help to resolve it.
>
> -Pawan
>
>
>
>
>
> Hi Andrew,

employees table is exited.

*Oracle Database*

CREATE TABLE EMPLOYEES
(
EMPLOYEE_ID NUMBER (6) NOT NULL ,
FIRST_NAME VARCHAR2 (20 BYTE) ,
LAST_NAME VARCHAR2 (25 BYTE) NOT NULL ,
EMAIL VARCHAR2 (25 BYTE) NOT NULL ,
PHONE_NUMBER VARCHAR2 (20 BYTE) ,
HIRE_DATE DATE NOT NULL ,
JOB_ID VARCHAR2 (10 BYTE) NOT NULL ,
SALARY NUMBER (8,2) ,
COMMISSION_PCT NUMBER (2,2) ,
MANAGER_ID NUMBER (6) ,
DEPARTMENT_ID NUMBER (4)
) LOGGING
;

The script generated by the tool.

CREATE TABLE employees (
employee_id integer NOT NULL,
first_name varchar(20),
last_name varchar(25) NOT NULL,
email varchar(25) NOT NULL,
phone_number varchar(20),
hire_date timestamp NOT NULL,
job_id varchar(10) NOT NULL,
salary decimal(8,2),
commission_pct decimal(2,2),
manager_id integer,
department_id smallint
) ;