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.
When the source Oracle table has a NOT NULL constraint exclusive of those from primary keys, the Ora2Pg --type TEST option double counts them.
Example: creation of two Oracle source tables:
create table demo1 (col1 int, constraint demo1_pk primary key (col1));
create table demo2 (col1 int not null);
The result is that both tables have NOT NULL constraints:
SQL> desc demo1
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NOT NULL NUMBER(38)
SQL> desc demo2
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NOT NULL NUMBER(38)
SQL>
But the constraint definitions are different:
SQL> select table_name, constraint_type, search_condition_vc from all_constraints where table_name like 'DEMO_';
TABLE_NAME CONSTRAINT_TYPE SEARCH_CONDITION_VC
------------ -------------------- --------------------
DEMO2 C "COL1" IS NOT NULL
DEMO1 P
SQL>
After migrating these tables using Ora2Pg and then comparing using the --type TEST option shows:
...
[TEST NOT NULL CONSTRAINTS COUNT]
ORACLEDB:DEMO1:1
POSTGRES:scott.demo1:1
ORACLEDB:DEMO2:2
POSTGRES:scott.demo2:1
[ERRORS NOT NULL CONSTRAINTS COUNT]
Table scott.demo2 doesn't have the same number of not null constraints in source database (2) and in PostgreSQL (1).
...
Source of the issue is related to the following line in the Ora2Pg.pm file:
$nbnull += $nbnotnull{$t} if (exists $nbnotnull{$t}); # Append the CHECK not null constraints
On the surface, it appears as though simply commenting out that line resolves the issue. However, not completely convinced that solution is comprehensive enough as of yet. Additional testing of additional permutations is required.
When the source Oracle table has a NOT NULL constraint exclusive of those from primary keys, the Ora2Pg
--type TEST
option double counts them.Example: creation of two Oracle source tables:
The result is that both tables have NOT NULL constraints:
But the constraint definitions are different:
After migrating these tables using Ora2Pg and then comparing using the
--type TEST
option shows:Source of the issue is related to the following line in the
Ora2Pg.pm
file:On the surface, it appears as though simply commenting out that line resolves the issue. However, not completely convinced that solution is comprehensive enough as of yet. Additional testing of additional permutations is required.