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

ALTER SEQUENCE DDLs do not appear in the output file after exporting data #1740

Closed priyanshi-yb closed 4 months ago

priyanshi-yb commented 4 months ago

Hi Team,

While exporting the Sequences from Oracle the ALTER SEQUENCE ... DDLs do not appear in the output file after the data export and hence sequence values can't be resumed on PG after loading data. Example if we use this command to export data

ora2pg -q -t COPY -P 4 -o data.sql -b <data_dir> -c <conf_file_path> --no_header -T <temp_dir>

so, data.sql will have these SQLs for all the sequences with this commit https://github.com/darold/ora2pg/commit/f08d47a4e5c20ff7d348e535ed05ab571b4bb350

....
ALTER SEQUENCE IF EXISTS iseq$$_122526 RESTART WITH 2;
ALTER SEQUENCE IF EXISTS iseq$$_122517 RESTART WITH 122;
ALTER SEQUENCE IF EXISTS iseq$$_122529 RESTART WITH 2;
ALTER SEQUENCE IF EXISTS iseq$$_122520 RESTART WITH 2122;
....

But the latest commit doesn't seem to be putting these in the data.sql. Example of schema -

CREATE TABLE identity_demo_generated_always (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    description VARCHAR2(100) NOT NULL
);
INSERT INTO identity_demo_generated_always(description)
VALUES('Random description for the test');

INSERT INTO identity_demo_generated_always(description)
VALUES('Random description for the test');

Can someone please let me know if I am missing anything or is this any regression?

Thanks! cc: @darold

darold commented 4 months ago

Hi @priyanshi-yb , there is a special action for that. Using action SEQUENCE_VALUES it will generate the ALTER comment to set the sequence start value, for example:

ora2pg -c config/ora2pg.conf -t SEQUENCE_VALUES -o alter_sequences.sql