Extract data query errorring out

[rjm-smokey]

Hello,

I have worked with the ora2pg tool to perform a full schema export, and have run the provided import_all.sh script which successfully loads the various schema elements into Postgres. When it comes time to load the data from Oracle to Postgres, I run into an error

The import all script initially succeeds, and goes on to run the ora2pg command that transfers data from Oracle to postgres, but fails, with an error message for ORA-00923

./import_all.sh -U <uname>@<db> -d <db_name> -p 5432 -o <uname>@<db> -h <hostname> -y
...
...
GRANT
Running: ora2pg -c config/ora2pg.conf -t COPY --pg_dsn "dbi:Pg:dbname=<db_name>;host=<host_str>" --pg_user <user@db>
...
...
SET client_encoding TO 'UTF8';
SET synchronous_commit TO off;

FATAL: _extract_data() ORA-00923: FROM keyword not found where expected (DBD ERROR: error possibly near <*> indicator at char 45 in 'SELECT trim(BOTH ' ' FROM a."D_TYPE") AS a<*>."D_TYPE",a."DESCR",a."ACTIVE",trim(BOTH ' ' FROM a."SOURCE") AS a."SOURCE",a."OPERATOR",a."ASOFDATE" FROM "MY_DB"."DTYPELKUP" a')

I've been looking for solutions, but I have not been able to find the exact issue encountered by others. Some forums have suggested setting the TRANSACTION parameter to readonly, which I have set to in config/ora2pg.conf

Has anyone else run into this? I've been troubleshooting for a bit and thought I'd open an issue to get more insight.

I've also been reading through the script in lib/Ora2Pg.pm , specifically the _howto_get_data to make sure the query I'm seeing is intended in https://github.com/darold/ora2pg/blob/2a005df3cf3077aca04a0cd35d3e5edb80834f1a/lib/Ora2Pg.pm#L11965C4-L11966C108

Additional context: I noticed that I had specified a datatype conversion from DATA_TYPE char:varchar, I removed this and that error was gone, so this might be an issue specific to this configuration setting

[darold]

This problem might be fixed by PR #1791, can you test with latest development code to see if the problem persist?

[rjm-smokey]

Awesome! I've got a long running migration still going but I should be able to update to the latest ora2pg version and run one table afterwards, around Thursday or Friday

[rjm-smokey]

Ok, actually its been more than 1 day that the fix_function_call has been running, and I'm not sure if that's because its still doing work.

I'm going to terminate the process tomorrow, but I assume this shouldn't affect the import process? Looking at the latest version of the code in this repo, that function is supposed to be called by the export function and not the import function so I don't know why its running after I ran the import script.

[darold]

This is because DDL files used by the import script are generated by the export script.

[rjm-smokey]

Gotcha, Ok I attempted to rerun the script with the map set, but maybe I'm not doing it correctly, because I'm still running into the same issue. Feel free to close this issue, because it seems like the syntax issue is definitely the cause and that was corrected in source.

Here was my process, feel free to let me know in case it's not the right way - what I did was replace the lib/Ora2Pg.pm with the same file in the master file in this codebase. I confirmed the change, then built using sudo perl Makefile.PL sudo make && sudo make install

cp lib/Ora2Pg.pm blib/lib/Ora2Pg.pm Skip blib/lib/Ora2Pg/GEOM.pm (unchanged) Skip blib/lib/Ora2Pg/Oracle.pm (unchanged) Skip blib/lib/Ora2Pg/PLSQL.pm (unchanged) Skip blib/lib/Ora2Pg/MSSQL.pm (unchanged) Skip blib/lib/Ora2Pg/MySQL.pm (unchanged) cp scripts/ora2pg blib/script/ora2pg /usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora2pg cp scripts/ora2pg_scanner blib/script/ora2pg_scanner /usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora2pg_scanner Manifying blib/man3/ora2pg.3 Installing default configuration file (ora2pg.conf.dist) to /etc/ora2pg Appending installation info to /usr/lib64/perl5/perllocal.pod

ora2pg -c config/ora2pg.conf -t COPY ... ... ... ... [2024-06-10 01:39:38] [========================>] 1/1 tables (100.0%) scanning t [2024-06-10 01:39:38] [========================>] 1/1 tables (100.0%) end of scanning.

SET client_encoding TO 'UTF8'; SET synchronous_commit TO off;

FATAL: _extract_data() ORA-00923: FROM keyword not found where expected (DBD ERR OR: error possibly near <> indicator at char 64 in 'SELECT a."APP_NAME",a."LINK ",trim(BOTH ' ' FROM a."ACTIVE") AS a<>."ACTIVE",a."ASOFDATE" FROM "DB"."APP _LINKS" a')

Aborting export...

[darold]

Normally it has been fixed but just in case commit 8f773f3 make sure that there is no alias.