From: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | Igor Neyman <ineyman(at)perceptron(dot)com>, PAWAN SHARMA <er(dot)pawanshr0963(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Oracle database into PostgreSQL using Ora2PG tool. |
Date: | 2017-06-02 13:52:46 |
Message-ID: | DM5PR07MB281042EA8D3121F1211A704EDAF70@DM5PR07MB2810.namprd07.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Igor Neyman
Sent: Friday, June 02, 2017 9:45 AM
To: PAWAN SHARMA <er(dot)pawanshr0963(at)gmail(dot)com>; pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org<mailto:pgsql-general-owner(at)postgresql(dot)org> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of PAWAN SHARMA
Sent: Friday, June 02, 2017 4:17 AM
To: pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)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';
From | Date | Subject | |
---|---|---|---|
Next Message | Juliano | 2017-06-02 14:03:13 | Re: repmgr cascade replication node delay |
Previous Message | Osahon Oduware | 2017-06-02 13:51:09 | ST_CreateOverview Function Generating Overview-Rasters With Black Grids |