darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
978 stars 341 forks source link

SQL queries selecting NEXVAL from the sequence in the trigger are not translated #1701

Closed JosefMachytkaNetApp closed 8 months ago

JosefMachytkaNetApp commented 8 months ago

Hello, I have a problem with ora2pg tool when I try to migrate app from Oracle to PostgreSQL. Original app in Oracle uses triggers to generate NEXTVAL for ID columns. Trigger function and creation of trigger are translated into pl/pgsql but the select from the sequence itself is untouched. I presume it could be by design, but is there some workaround for it?

Here is an example how it looks like in export file:

DROP TRIGGER IF EXISTS "...." ON "..." CASCADE;
CREATE OR REPLACE FUNCTION "trigger_....."() RETURNS trigger AS $BODY$
BEGIN
    SELECT ".....".NEXTVAL INTO STRICT NEW."ID";
RETURN NEW;
END
$BODY$
 LANGUAGE 'plpgsql';

CREATE TRIGGER "...."
        BEFORE INSERT ON "...." FOR EACH ROW
        EXECUTE PROCEDURE "trigger_...."();

Trigger is in PostgreSQL successfully created but of course fails when runs. Can I configure ora2pg to make it translate these selects from sequences into proper PostgreSQL version?

Thank you very much.

JosefMachytkaNetApp commented 8 months ago

Never mind, I found some other way.