Once programs have submitted the Custom Objects Ready to Convert Form, we will run a check on your database based on the answers you provided in your spreadsheet and provide you with a report that lists any potential problems that were found, as well as re-create your CSV's again, retaining your original Y/N values. The CSV would not include any objects you might have dropped since your last CSV run and would include any new objects that were created as well.

Use the guidance below to address these potential issues. Once you have addressed the issues, fill out the Custom Objects Ready to Convert Form again, and we will re-run the CSV's and report again. This process will continue until no issues are found. We encourage programs to complete their CSV's and submit the Custom Objects Ready to Convert Form as soon as possible so we can work through this well in advance of your conversion.

Migration Analysis Troubleshooting

Duplicate table/view/function/procedure names

This is reporting one of two situations which the client will need to investigate themselves:

  • Two objects share the same name, differing only by case
  • Two objects share the same name but exist in different schemas; this is typically a table that matches the same name as a table within the deleted schema

Resolution: Rename or drop one of the underlying Oracle objects involved in this situation. All object names in the database must be totally unique (ignoring case) in order for the migration analysis to be reliable.

Missing migration decision

This could be because the program failed to make a decision or because the object has been created in their DB since the time that their original CSVs were generated.

Resolution: A decision of Y or N must be set in the CSV's Migrate column.

Unexpected migration decision

A value other than Y/N was found. Definitive decisions must be made — “soft” decisions such as N? are not allowed.

Resolution: A decision of Y or N must be set in the CSV's Migrate column.

Non-migrated table referenced by extensible table registration

The report will indicate if the table is referenced by dynamic_object.table_name (which means it is registered as an extensible table) or dynamic_attribute.domain_table (which means it is registered as an extensible domain table).

Resolution: The table must either be migrated (set Migrate=Y in the CSV file) or removed from the extensible table's registration data within Biotics 5.

Note: If they decide not to migrate a table used as a domain table, they should either remove the database column which is a foreign key reference to the table or ensure it allows null values, as column values for new rows will not be populated in Biotics 6.

Foreign keys referencing tables that are not being migrated

Resolution — three possible solutions:

  1. Don't migrate the table which refers to the missing table — set Migrate=N in the CSV file.
  2. Drop the foreign key reference (and possibly the column) from the Biotics 5 database.
  3. Migrate the referenced table — set Migrate=Y in the CSV for the referenced table, if it is a custom table which already exists in the CSV file.

If this is reported due to a reference to an obsolete Biotics 5 table that is not being migrated, the obsolete table can be migrated as a custom table if desired by adding a new row to custom_tables.csv for the obsolete table and setting Migrate=Y. No other information needs to be entered.

NatureServe will need to regenerate the CSVs again so that custom_numeric_fields.csv and custom_date_fields.csv contain rows for the obsolete table, which should subsequently be reviewed by the client for accuracy.

Numeric column typed as BIGINT despite containing decimal values

A numeric column will be converted as a BIGINT data type, but the current values include decimal values. The decimal component will be truncated during migration, not rounded.

Resolution: This isn't necessarily a blocker, but is an unusual situation which should be confirmed.

  • The datatype can be changed to DOUBLE in the client's CSV if they decide that is a more appropriate data type.
  • Alternatively, they can fix their data so this situation no longer occurs if the values should not contain decimal values.

They can use a query similar to the following to find rows containing decimal values:

select * from some_table where mod(some_column, 1) != 0;

Invalid view selected for migration

A view which is not currently valid within Oracle has been selected for migration. The view cannot be processed by the AWS Schema Conversion Tool. This will cause failures during conversion for any other objects that depend upon the view.

Resolution: The view should either be fixed within Oracle or excluded from conversion by setting Migrate=N in the CSV.

Objects that cannot be migrated due to missing dependencies

Resolution — a few possible solutions:

  1. Do not migrate the object — set Migrate=N in the CSV.
  2. Migrate the dependency which has not been selected for migration — set Migrate=Y for the referenced object in the appropriate CSV.
  3. Alter the object in Oracle to no longer depend upon the missing dependency.

Views containing problematic outer join — ( + )

Oracle supports the following syntax for left outer joins:

select ... from foo, bar, baz
where foo.id = bar.id and bar.id(+)=baz.id;

However, if (+) is instead written as ( + ), the conversion tool encounters problems, and will stop processing the where clause at the location where this symbol occurs. It doesn't throw any errors, and it is entirely possible that it will produce a Postgres view that is syntactically valid but logically wrong — a mistake that might not be caught until much later.

Resolution: The client should modify their Oracle view to instead use (+).