How to fix missing collection and received event fields?

It has been observed that when the OpenSpecimen database is upgraded to versions v6.2 or higher, both the API and UI display blank values for some fields of the collection and received events. This issue is observed on legacy databases (i.e. databases upgraded from caTissue or OpenSpecimen v1.1) using Oracle.

To fix the problem, follow the below steps:

1. Create convenience functions to convert a column from BLOB to CLOB and vice-versa. These functions are needed to update the specimen events form metadata. Execute the below SQLs:

   CREATE OR REPLACE FUNCTION convert_to_clob(l_blob BLOB) return CLOB IS
     l_clob         CLOB;
     l_dest_offset  NUMBER := 1;
     l_src_offset   NUMBER := 1;
     l_lang_context NUMBER := dbms_lob.default_lang_ctx;
     l_warning      NUMBER;
   BEGIN
     dbms_lob.createtemporary(l_clob, TRUE);
     dbms_lob.converttoclob(
       dest_lob     => l_clob,
       src_blob     => l_blob,
       amount       => dbms_lob.lobmaxsize,
       dest_offset  => l_dest_offset,
       src_offset   => l_src_offset,
       blob_csid    => nls_charset_id('AL32UTF8'),
       lang_context => l_lang_context,
       warning      => l_warning
     );
     RETURN l_clob;
   END convert_to_clob;

   CREATE OR REPLACE FUNCTION convert_to_blob(l_clob CLOB) RETURN BLOB IS
     l_blob         BLOB;
     l_dest_offset  NUMBER := 1;
     l_src_offset   NUMBER := 1;
     l_lang_context NUMBER := dbms_lob.default_lang_ctx;
     l_warning      NUMBER;
   BEGIN
     dbms_lob.createtemporary(l_blob, TRUE);
     dbms_lob.converttoblob(
       dest_lob     => l_blob,
       src_clob     => l_clob,
       amount       => dbms_lob.lobmaxsize,
       dest_offset  => l_dest_offset,
       src_offset   => l_src_offset,
       blob_csid    => nls_charset_id('AL32UTF8'),
       lang_context => l_lang_context,
       warning      => l_warning
     );
     RETURN l_blob;
   END convert_to_blob;

2. Update the specimen events form metadata by executing the following DML:

   UPDATE 
     dyextn_containers
   SET 
     xml = convert_to_blob(
       replace(
         convert_to_clob(xml),
         '<managedTables>false</managedTables>',
         '<managedTables>true</managedTables>'
       )
     )
   WHERE
     name in (
       'SpecimenCollectionEvent', 
       'SpecimenReceivedEvent', 
       'SpecimenTransferEvent', 
       'SpecimenDisposalEvent'
     );

3. Copy the collection and received event field values to the correct table columns (as expected by the app):

   UPDATE
     catissue_coll_event_param
   SET
     collection_procedure_id = de_a_6
   WHERE
     de_a_6 is not null;

   UPDATE
     catissue_coll_event_param
   SET
     collection_container_id = de_a_7
   WHERE
     de_a_7 is not null;

   UPDATE
     catissue_received_event_param
   SET
     received_quality_id = de_a_5
   WHERE
     de_a_5 is not null;

4. Manipulate the forms import log to allow OpenSpecimen re-import the events form metadata:

   UPDATE
     os_import_forms_log
   SET
     md5_digest = concat(md5_digest, '_reload')
   WHERE
     form_id in (
       SELECT
         identifier
       FROM
         dyextn_containers
       WHERE
         name in (
           'SpecimenCollectionEvent',
           'SpecimenReceivedEvent',
           'SpecimenDisposalEvent',
           'SpecimenTransferEvent'
         )
     );

5. Restart OpenSpecimen

6. On successful restart, the collection and received event field values should appear in both the API response and on the UI pages.