search

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

Data validation through FDW #1712

Closed fljdin closed 4 months ago

fljdin commented 7 months ago

Hi Gilles,

Ora2pg version : 24.1

Syntax error occurs with performing data validation through FDW with a non-dba Oracle user and SCHEMA directive. Given this configuration:

# ora2pg.conf
ORACLE_DSN  dbi:Oracle://localhost:1521/test
ORACLE_USER     TEST
ORACLE_PWD      TEST
SCHEMA          TEST
USER_GRANTS     1

PG_DSN      dbi:Pg:host=localhost;dbname=test
PG_USER     test
PG_PWD      test

FDW_SERVER  orcl
PG_VERSION  15
$ ora2pg -c ora2pg.conf -t TEST_DATA
FATAL: ERROR:  syntax error at or near "FROM"
LINE 1: ...MA "TEST" FROM SERVER orcl INTO ora2pg_fdw_import FROM SERVE...
                                                             ^
SQL: IMPORT FOREIGN SCHEMA "TEST" FROM SERVER orcl  INTO ora2pg_fdw_import FROM SERVER orcl 
     INTO ora2pg_fdw_import  OPTIONS (case 'keep', readonly 'true')
Aborting export...

Another syntax error is raised when SCHEMA is omitted:

$ ora2pg -c ora2pg.conf -t TEST_DATA
FATAL: ERROR:  zero-length delimited identifier at or near """"
LINE 1: IMPORT FOREIGN SCHEMA "" FROM SERVER orcl INTO ora2pg_fdw_im...
                              ^
SQL: IMPORT FOREIGN SCHEMA "" FROM SERVER orcl  INTO ora2pg_fdw_import FROM SERVER orcl
     INTO ora2pg_fdw_import OPTIONS (case 'keep', readonly 'true')
Aborting export...

Regards, Florent

darold commented 4 months ago

Commit 0b57059 solves this issue.