Oracle database into PostgreSQL using Ora2PG tool.

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

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
) ;

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

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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.

From: pgsql-general-owner@postgresql.org<mailto: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<mailto: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

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';

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

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?

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

____________________________________________________________________

At runtime.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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';