Generating offline migration scripts from MySQL to Oracle results in a master.sql file that contains DDL that cannot be run in sqlplus. The first two observed errors when trying to execute the master.sql are related to what appears to be extra single quotes (1st example), and unnecessary single quotes (2nd example).
I've searched thru the preferences/settings but haven't found anything that could explain this behaviour.
First example:
SQL> ALTER TABLE MY_TABLE_NAME MODIFY (MY_FIELD_NAME DEFAULT ''foo'');
ALTER TABLE MY_TABLE_NAME MODIFY (MY_FIELD_NAME DEFAULT ''foo'')
*
ERROR at line 1:
ORA-00907: missing right parenthesis
In this next example, the source table definition has 'foo' as the column default, so I'm not clear as to why the tool thought it needed to quote it. This is the field definition in the source database:
`MY_FIELD_NAME` varchar(255) NOT NULL DEFAULT 'foo',
Second example:
SQL> ALTER TABLE MY_TABLE_NAME MODIFY (MY_OTHER_FIELD_NAME DEFAULT 'NULL');
ALTER TABLE MY_TABLE_NAME MODIFY (MY_OTHER_FIELD_NAME DEFAULT 'NULL')
*
ERROR at line 1:
ORA-01722: invalid number
In this example, the source table definition has NULL as the default value which, based on the generated DDL in master.sql, seems to be interpreted as a string 'NULL' instead of a true NULL or (null) value. This is the column definition:
`MY_OTHER_FIELD_NAME` decimal(13,6) DEFAULT NULL,
SQL Developer version: Oracle IDE 23.1.1.345.2114
MySQL connection: mysql-connector-j-8.2.0.jar