This is to notify you of upcoming changes to values within two domain tables: D_NAME_CATEGORY & D_CLASSIFICATION_FRAMEWORK. 


D_NAME_CATEGORY: 


  • The NAME_CATEGORY_DESC and DISPLAY_VALUE values will change from Alga to Alga (Plant) for the existing entry with D_NAME_CATEGORY_ID=19 .
  • 3 new values will be added within the Botanical category: Chromist, Protozoan, and Bacterium


As part of these changes, the legend entries used for the Source Feature and EO map services will also be changed.


Currently, there's a single legend entry for elements which use two name categories: Alga/Fungus (name_category_ids=19, 6). This will be replaced by two legend entries:

  • one for Fungus (6)
  • and a separate one for Alga(Plant)/Chromist/Protozoan/Bacterium (19 and the 3 new IDs)


D_CLASSIFICATION_FRAMEWORK: 

  • The CLASSIFICATION_FRAMEWORK_DESC value will change from Plant/Fungus to Plant for the existing entry with D_CLASSIFICATION_FRAMEWORK_ID=1. 

You may need to update any queries, views, functions, stored procedures, or reports which rely on a value of "Alga" within the D_NAME_CATEGORYNAME_CATEGORY_DESC column or "Plant/Fungus" within the D_CLASSIFICATION_FRAMEWORK.CLASSIFICATION_FRAMEWORK_DESC column. 

In order to figure out what tables/views may be affected, you can run the following queries or refer to this  solution.


SELECT table_name, column_name FROM all_tab_columns where column_name='NAME_CATEGORY_DESC' AND table_name not like'%UVW%' and table_name not like '%DVW%' and table_name not like '%HVW%' order by table_name asc


SELECT table_name, column_name FROM all_tab_columns where column_name='CLASSIFICATION_FRAMEWORK_DESC' AND table_name not like'%UVW%' and table_name not like '%DVW%' and table_name not like '%HVW%' order by table_name asc

NOTE: These queries exclude the NatureServe views that end in HVW, DVW, and UVW. Those were a legacy of the Biotics 4 Data Exchange days and will eventually go away. You are free to modify them on your own, however, we highly recommend that you recreate these views with a different name going forward. Don’t worry, we don’t plan on removing these anytime soon and we would give everyone PLENTY of notice! However, we are not maintaining these views.


You can also use the query below to find all Invalid Objects, including broken views, in your database:

SELECT owner,object_name,subobject_name,object_type FROM all_objects WHERE status='INVALID' and object_name not like '%UVW' and object_name not like '%DVW' and object_name not like '%HVW'