Pre-26ai Collections After Upgrade

Collections created prior to Oracle Database 26ai will continue to be supported after upgrading to 26ai. Future enhancements, however, will focus on collections based on 26ai JSON collection tables and views.

If your application creates pre-26ai collections without explicitly providing JSON metadata - that is, by relying on the default collection configuration - be aware that collections created after a database upgrade may differ from those already in use. For example, consider an application running on Oracle Database 19c that creates collections without passing JSON metadata to the create collection operation. If the database is then upgraded to 26ai and COMPATIBLE is set to 23 or higher, any new collection created without metadata will default to a 26ai JSON collection table - rather than a 19c-style collection matching the configuration of the application’s existing collections.

Using such a mix of collections within a specific application is not recommended, due to the significant differences between 26ai collections based on JSON collection tables and pre-26ai collections. These differences are outlined in Differences Between Pre-26ai and 26ai Collections.

Instead, you can create new pre-26ai collections that are configured identically to your existing ones, even on 26ai. To do this, retrieve the metadata of an existing collection using either a SODA metadata accessor or the JSON_DESCRIPTOR column of USER_SODA_COLLECTIONS, remove the tableName and schemaName fields, and use the resulting metadata when creating new collections.

The following SODA for PL/SQL example uses get_metadata() to obtain the metadata of an existing collection, removes tableName and schemaName, and uses the result to create a new collection with the same configuration.

DECLARE
    src_coll   SODA_COLLECTION_T;
    new_coll   SODA_COLLECTION_T;
    metadata   VARCHAR2(4000);
BEGIN
    src_coll := DBMS_SODA.open_collection('myOldCollection');

    IF src_coll IS NULL THEN
        raise_application_error(-20000,
                                'Collection myOldCollection does not exist');
    END IF;

    SELECT json_serialize(
               json_transform(src_coll.get_metadata,
                              REMOVE '$.tableName',
                              REMOVE '$.schemaName')
               RETURNING VARCHAR2(4000) PRETTY)
      INTO metadata
      FROM dual;

    new_coll := DBMS_SODA.create_collection('myNewCollection', metadata);
END;
/