Search Postgresql Archives

Re: Oracle database into PostgreSQL using Ora2PG tool.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



 

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Igor Neyman
Sent: Friday, June 02, 2017 9:45 AM
To: PAWAN SHARMA <er.pawanshr0963@xxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of PAWAN SHARMA
Sent: Friday, June 02, 2017 4:17 AM
To: pgsql-general@xxxxxxxxxxxxxx
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';

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux