Oracle database into PostgreSQL using Ora2PG tool.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Oracle database into PostgreSQL using Ora2PG tool.

Pawan Sharma
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



 
Reply | Threaded
Open this post in threaded view
|

Re: Oracle database into PostgreSQL using Ora2PG tool.

Pavel Stehule
Hi

2017-06-02 10:16 GMT+02:00 PAWAN SHARMA <[hidden email]>:
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



 

Reply | Threaded
Open this post in threaded view
|

Re: Oracle database into PostgreSQL using Ora2PG tool.

Pawan Sharma
In reply to this post by Pawan Sharma

On Fri, Jun 2, 2017 at 2:20 PM, Andrew Sullivan <[hidden email]> 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 <[hidden email]> 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
) ;





Reply | Threaded
Open this post in threaded view
|

Re: Oracle database into PostgreSQL using Ora2PG tool.

Pawan Sharma
In reply to this post by Pavel Stehule

On Fri, Jun 2, 2017 at 2:13 PM, Pavel Stehule <[hidden email]> wrote:
Hi

2017-06-02 10:16 GMT+02:00 PAWAN SHARMA <[hidden email]>:
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

Reply | Threaded
Open this post in threaded view
|

Re: Oracle database into PostgreSQL using Ora2PG tool.

Pavel Stehule


2017-06-02 11:15 GMT+02:00 PAWAN SHARMA <[hidden email]>:

On Fri, Jun 2, 2017 at 2:13 PM, Pavel Stehule <[hidden email]> wrote:
Hi

2017-06-02 10:16 GMT+02:00 PAWAN SHARMA <[hidden email]>:
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
Reply | Threaded
Open this post in threaded view
|

Re: Oracle database into PostgreSQL using Ora2PG tool.

Neil Anderson-2
In reply to this post by Pawan Sharma
On 2 June 2017 at 04:16, PAWAN SHARMA <[hidden email]> 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
[hidden email]
https://www.postgrescompare.com



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Oracle database into PostgreSQL using Ora2PG tool.

Igor Neyman
In reply to this post by Pawan Sharma

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of PAWAN SHARMA
Sent: Friday, June 02, 2017 4:17 AM
To: [hidden email]
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

 

 

Reply | Threaded
Open this post in threaded view
|

Re: Oracle database into PostgreSQL using Ora2PG tool.

Igor Neyman

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Igor Neyman
Sent: Friday, June 02, 2017 9:45 AM
To: PAWAN SHARMA <[hidden email]>; [hidden email]
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: [hidden email] [[hidden email]] On Behalf Of PAWAN SHARMA
Sent: Friday, June 02, 2017 4:17 AM
To: [hidden email]
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';

 

Reply | Threaded
Open this post in threaded view
|

Re: Oracle database into PostgreSQL using Ora2PG tool.

Steven Chang
In reply to this post by Pawan Sharma


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 <[hidden email]>
日期: 2017/6/2 16:16 (GMT+08:00)
主旨: [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



 
Reply | Threaded
Open this post in threaded view
|

Re: Oracle database into PostgreSQL using Ora2PG tool.

Neil Anderson-2
On 2 June 2017 at 11:57, stevenchang1213 <[hidden email]> 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
>


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

contacts (1).vcf (228 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Oracle database into PostgreSQL using Ora2PG tool.

Igor Neyman

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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Oracle database into PostgreSQL using Ora2PG tool.

Pawan Sharma


On Sat, Jun 3, 2017 at 1:48 AM, Igor Neyman <[hidden email]> 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';