ora2pg table.sql ERROR: syntax error at or near "char"

[avmindestroy]

Good afternoon!!! I ask you how to solve the problem during migration from an oracle database to postgres psql -d db -U postgres < schema/tables/table.sql ERROR: syntax error at or near "char" LINE 14: primary char(1) NOT NULL DEFAULT '0'

after exporting the table in the table.sql file itself the line is like this primary char(1) NOT NULL DEFAULT '0',

[darold]

Look like primary is a reserved word in the create table DDL. If you want to preserve it you must enclosed it with double quote.

If you enable USE_RESERVED_WORDS in ora2pg.conf Ora2Pg will do that for you.

[avmindestroy]

thank you ERROR: function ora_hash(text) does not exist LINE 15: responsecs numeric GENERATED ALWAYS AS (ORA_HASH(BulkEmailR... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

[avmindestroy]

Thank you We have OS Centos postgresSql v14 oracle v11 ora2pg v24 Installed ora2pg where the postgres database total Oracle database volume 300 GB Our task is to switch from the oracle database entirely to the postgressql database.

First time we encountered database migration from oracle to postgres there is a lot written in the documentation I'm lost here, how can I migrate an oracle database to Postrgess? after installing ora2pg I checked all connections to the database ora2pg -t SHOW_VERSION -c config/ora2pg.conf ora2pg.conf default settings done DBI connect specified then I run the command 1) ./export_schema.sh 2) psql -d test_db -U postgres < schema/tables/table.sql 3) ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf 4) psql -d test_db -U postgres < schema/data/data.sql 5) ./import_all.sh Can you check that I am using the commands in the correct order for database migrations? If you can, on ora2pg.conf what settings should you use so that everything is automatically migrated?

[avmindestroy]

Sorry, I have different errors on different tables, what can I do to fix it myself? ERROR: function hextoraw(integer) does not exist HINT: No function matches the given name and argument types. You might need to add explicit type casts

[darold]

You can not migrate automatically if you have errors when importing SQL file to PostgreSQL. This is the case most of the time unless you don't have stored procedure and basic objects in your oracle database.

Ora2Pg do most of the conversion job but you will have to fix all that have not be converted or the order of the SQL files import. for example ERROR: function ora_hash(text) does not exist means that this function must be imported before.

You should use the psql command to import the file individually and fix the errors encountered.

[avmindestroy]

thank you let's try now the database is working, there is stored data

[avmindestroy]

During export, this error appears, is this normal?

[darold]

I don't know, see your Oracle DBA.

[avmindestroy]

Thank you

[avmindestroy]

HI, [2024-03-11 08:58:07] [========================>] 2756/2756 tables (100.0%) end of scanning. FATAL: ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. Aborting export...

[darold]

The memory used by Ora2Pg is controlled by 3 parameters:

• DATA_LIMIT (or option -L)
• ORACLE_COPIES (option -J)
• PARALLEL_TABLES (option -P)

Depending on the number of CPU and RAM you have you might adjust these values.