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

Problem with properly boolean export from oracle number(1,0) to PostgreSQL with direct insert PG_DSN #1770

Closed twiti7 closed 2 months ago

twiti7 commented 2 months ago

I'm trying to move table from Oracle to Pgsql with boolean conversion.

In oracle table looks like below NAME VARCHAR2(50 BYTE) BOOLEAN_VALUE NUMBER(1,0)

in PostgreSQL create table setting ( name varchar(50) not null, boolean_value boolean );

When I set conversion in pgconfig 'MODIFY_TYPE SETTING:BOOLEAN_VALUE:boolean' or ' REPLACE_AS_BOOLEAN SETTING:BOOLEAN_VALUE BOOLEAN_VALUES true:false '

in std output or to file I'm getting proper result ora2pg | INSERT INTO setting (name,boolean_value) VALUES (E'LAST_ACTIVATION',NULL); ora2pg | INSERT INTO setting (name,boolean_value) VALUES (E'ANOTHER','t');

but when I made direct import to pgsql all boolean are true on version 24 and 24.3 too

twiti7 commented 2 months ago

I found where is problem:

So where I have config which contain TYPE INSERT REPLACE_AS_BOOLEAN SETTING:BOOLEAN_VALUE BOOLEAN_VALUES true:false It produce good file with inserts but when it transfer data directly from oracle to pgsq oll boolean are true

If I comment all bool configs it works as expected.

darold commented 2 months ago

You can disable BOOLEAN_VALUES in ora2pg.conf if you only have 0/1 values in this column, it is handled automatically.

twiti7 commented 2 months ago

Disabling solves a problem but, it looks like a bug. The same config file generates a file with inserts which is ok, but export directly to DB in all boolean fields have true values.

darold commented 2 months ago

Commit 0217c3f might solve this issue.