Updated May 8, 2024


If you accidentally delete an Element Occurrence, you can recover your data from the deleted schema and insert it back into the source schema.

The attached zip file contains multiple documents:
1. Instructions for performing the restore.
2. A script which is used during a step in the instructions to restore the tabular data from the deleted schema to the source schema.

3. Additional scripts which may be required should you run into errors, as indicated in the Troubleshooting section of the document.

The attached instructions assume the following scenario:
You have deleted the entire EO as well as its associated EO and Source Features from the Map.

The procedures are designed to restore one EO at a time, and will restore multiple Source Features if the EO had multiple source features before it was deleted. Make sure to read through the instructions carefully, before executing. It is also recommended that you read through the script to make sure that you understand the steps that are run within the script.

If for some reason, your spatial features still exist and you only need to restore the tabular data, the SCRIPT will need to be altered a bit. Since procedures do not exist yet for this particular scenario, please submit an issue to the Biotics 5 Help Desk if you would like help making the appropriate alterations.


The webinar and recorded session covering this topic can be found within the Training Webinars transfer site.


Troubleshooting:

Following are two examples of errors encountered while running the process, what causes them, and how to resolve them.

  1. To determine the source of the problem(s), Find (CTRL-F) ORA- in the log file. In this instance, there is a unique constraint which means multiple records returned for the same SOURCE_FEATURE: SQL Error: ORA-00001: unique constraint (BIOTICS_USER.EO_SOURCE_FEATURE_AK1) violated

  2. From the problematic statement, copy the select statement so that you can review the data causing the error: select
    EO_SOURCE_FEATURE_ID, EO_SHAPE_ID, EO_ID, SOURCE_FEATURE_ID, SOURCE_FEATURE_SHAPE_ID, REC_LAST_MOD_USER, REC_LAST_MOD_DATE,
    REC_CREATE_DATE, REC_CREATE_USER, DISPLAY_ORDER
    from biotics_del.eo_source_feature_del
    where biotics_del.eo_source_feature_del.eo_shape_id =
    (select shape_id from biotics_del.eo_del where eo_id = &v_eo_id)

  3. Replace &_v_eo_id with the actual EO ID: 9404

  4. Run the query and notice that there are more than one entry for a given SOURCE_FEATURE_ID, which is the cause of the error:

  5. Delete one (or more, depending on how many there are) of the records so that only ONE record exists for a given SOURCE_FEATURE_ID, as shown in the following example:
    DELETE biotics_del.eo_source_feature_del where eo_source_feature_id = 99289;
    commit;

  6. The other error received results from the referenced Site record: SQL Error: ORA-02291: integrity constraint (BIOTICS_USER.SITE_EO_SITE) violated - parent key not found

  7. Again, from the problematic statement, copy the select statement so that you can review the data causing the error: select
    EO_SITE_ID, EO_ID, CONSERVATION_SITE_ID, REC_LAST_MOD_USER, REC_LAST_MOD_DATE, REC_CREATE_DATE,
    REC_CREATE_USER, DISPLAY_ORDER, AUTO_CALC_IND
    from biotics_del.eo_site_del
    where biotics_del.eo_site_del.eo_id = &v_eo_id

  8. Replace &_v_eo_id with the actual EO ID: 4253

  9. Run the query and notice that there are more than one entry for a given SOURCE_FEATURE_ID, which is the cause of the error:

  10. Verify that the Site does not exist by running this query: select * from conservation_site where conservation_site_id=645

  11. Because no record is returned, delete the reference to it within the deleted schema:
    DELETE biotics_del.eo_site_del where eo_site_id = 3388;
    commit;