Home > mailing lists

Thread: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

[GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

From

PAWAN SHARMA

Date:

02 June 2017, 14:16:57

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

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

From

Pavel Stehule

Date:

02 June 2017, 14:43:37

2017-06-02 10:16 GMT+02:00 PAWAN SHARMA <er.pawanshr0963@gmail.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

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

From

PAWAN SHARMA

Date:

02 June 2017, 15:03:47

On Fri, Jun 2, 2017 at 2:20 PM, Andrew Sullivan <ajs@crankycanuck.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.pawanshr0963@gmail.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

) ;

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

From

PAWAN SHARMA

Date:

02 June 2017, 15:15:15

On Fri, Jun 2, 2017 at 2:13 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

2017-06-02 10:16 GMT+02:00 PAWAN SHARMA <er.pawanshr0963@gmail.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

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

From

Pavel Stehule

Date:

02 June 2017, 15:33:52

2017-06-02 11:15 GMT+02:00 PAWAN SHARMA <er.pawanshr0963@gmail.com>:

On Fri, Jun 2, 2017 at 2:13 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

2017-06-02 10:16 GMT+02:00 PAWAN SHARMA <er.pawanshr0963@gmail.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

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

From

Neil Anderson

Date:

02 June 2017, 18:19:57

On 2 June 2017 at 04:16, PAWAN SHARMA <er.pawanshr0963@gmail.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@postgrescompare.com
https://www.postgrescompare.com

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

From

Igor Neyman

Date:

02 June 2017, 19:45:13

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of PAWAN SHARMA
Sent: Friday, June 02, 2017 4:17 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

Attention: This email was sent from someone outside of Perceptron. Always exercise caution when opening attachments or clicking links from unknown senders or when receiving unexpected emails.

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

When you call a function inside PlSQL code and don’t care about returned value, then you do: PERFORM function_name(…).

Otherwise you do: SELECT function_name(…) INTO your_variable;

So:

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;

CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS $BODY$

BEGIN

PERFORM add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,

OLD.job_id, OLD.department_id);

RETURN NEW;

END

$BODY$

LANGUAGE 'plpgsql';

Regards,

Igor Neyman

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

From

Igor Neyman

Date:

02 June 2017, 19:52:46

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Igor Neyman
Sent: Friday, June 02, 2017 9:45 AM
To: PAWAN SHARMA <er.pawanshr0963@gmail.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

Attention: This email was sent from someone outside of Perceptron. Always exercise caution when opening attachments or clicking links from unknown senders or when receiving unexpected emails.

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

When you call a function inside PlSQL code and don’t care about returned value, then you do: PERFORM function_name(…).

Otherwise you do: SELECT function_name(…) INTO your_variable;

So:

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;

CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS $BODY$

BEGIN

PERFORM add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,

OLD.job_id, OLD.department_id);

RETURN NEW;

END

$BODY$

LANGUAGE 'plpgsql';

Regards,

Igor Neyman

P.S. Also you are missing semicolon (END;):

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;

CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS $BODY$

BEGIN

PERFORM add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,

OLD.job_id, OLD.department_id);

RETURN NEW;

END;

$BODY$

LANGUAGE 'plpgsql';

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

From

stevenchang1213

Date:

02 June 2017, 21:57:24

tell me where this function add_job_history() is?

Actually, I don't think you can count on ora2pg to transform your pl/sql code to plpgsql or other (un)trusted procedural language code. It's not that simple!

you can type "\df add_job_history" in psql session to check it's existence if it belongs to public schema or access it using fully qualified name scheme.

Steven

從我的 Samsung Galaxy 智慧型手機傳送。

-------- 原始訊息 --------

自： PAWAN SHARMA <er.pawanshr0963@gmail.com>

日期： 2017/6/2 16:16 (GMT+08:00)

至： pgsql-general@postgresql.org

主旨： [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

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

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

From

Neil Anderson

Date:

03 June 2017, 02:10:33

On 2 June 2017 at 11:57, stevenchang1213 <stevenchang1213@gmail.com> wrote:
>
>
> tell me where this function add_job_history() is?
> Actually, I don't think you can count on ora2pg to transform your pl/sql
> code to plpgsql or other (un)trusted procedural language code. It's not that
> simple!

I wonder, does plpgsql compilation check for existence of the
add_job_history function or is that a runtime check?

> you can type "\df  add_job_history"  in psql session to check it's existence
> if it belongs to public schema or access it using fully qualified name
> scheme.
>
>
> Steven
>

Attachment

contacts (1).vcf

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

From

Igor Neyman

Date:

03 June 2017, 02:18:22

I wonder, does plpgsql compilation check for existence of the add_job_history function or is that a runtime check?

____________________________________________________________________

At runtime.

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

From

PAWAN SHARMA

Date:

05 June 2017, 15:08:54

On Sat, Jun 3, 2017 at 1:48 AM, Igor Neyman <ineyman@perceptron.com> wrote:

I wonder, does plpgsql compilation check for existence of the add_job_history function or is that a runtime check?

____________________________________________________________________

At runtime.

Hi Neyman,

Thanks it's done by adding PERFORM.

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;

CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS $BODY$

BEGIN

PERFORM add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,

OLD.job_id, OLD.department_id);

RETURN NEW;

END

$BODY$

LANGUAGE 'plpgsql';