Oracle SODA默认集合添加自定义列失败求助

需求说明 #

需要为Oracle SODA创建的默认集合添加一个自定义列，用于标识存储的JSON_DOCUMENT类型。当前集合包含ID、CREATED_ON、LAST_MODIFIED、VERSION和JSON_DOCUMENT5列。

尝试过程 #

默认集合元数据 #

{ "schemaName" : "mySchemaName", "tableName" : "myTableName", "keyColumn" : { "name" : "ID", "sqlType" : "VARCHAR2", "maxLength" : 255, "assignmentMethod" : "UUID" }, "contentColumn" : { "name" : "JSON_DOCUMENT", "sqlType" : "BLOB", "compress" : "NONE", "cache" : true, "encrypt" : "NONE", "validation" : "STANDARD" }, "versionColumn" : { "name" : "VERSION", "method" : "SHA256" }, "lastModifiedColumn" : { "name" : "LAST_MODIFIED" }, "creationTimeColumn" : { "name" : "CREATED_ON" }, "readOnly" : false }

成功精简列的元数据 #

{ "keyColumn":{ "assignmentMethod": "CLIENT" }, "contentColumn": { "sqlType": "CLOB" } }

失败的自定义列元数据（创建后仍只有默认5列） #

{ "schemaName" : "mySchemaName", "tableName" : "myTableName", "keyColumn" : { "name" : "ID", "sqlType" : "VARCHAR2", "maxLength" : 255, "assignmentMethod" : "UUID" }, "contentColumn" : { "name" : "JSON_DOCUMENT", "sqlType" : "BLOB", "compress" : "NONE", "cache" : true, "encrypt" : "NONE", "validation" : "STANDARD" }, "contentColumn" : { "name" : "CUSTOM_COL", "sqlType" : "BLOB", "compress" : "NONE", "cache" : true, "encrypt" : "NONE", "validation" : "STANDARD" }, "versionColumn" : { "name" : "VERSION", "method" : "SHA256" }, "lastModifiedColumn" : { "name" : "LAST_MODIFIED" }, "creationTimeColumn" : { "name" : "CREATED_ON" }, "readOnly" : false }

创建集合的PL/SQL脚本 #

DECLARE 
  l_collection SODA_COLLECTION_T; 
  l_metadata VARCHAR2(32767); 
BEGIN 
  l_metadata := '{ "schemaName" : "schema_name", "tableName" : "table_name", "keyColumn" : { "name" : "ID", "sqlType" : "VARCHAR2", "maxLength" : 255, "assignmentMethod" : "UUID" }, "contentColumn" : { "name" : "JSON_DOCUMENT", "sqlType" : "BLOB", "compress" : "NONE", "cache" : true, "encrypt" : "NONE", "validation" : "STANDARD" }, "contentColumn" : { "name" : "CUSTOM_COL", "sqlType" : "BLOB", "compress" : "NONE", "cache" : true, "encrypt" : "NONE", "validation" : "STANDARD" }, "versionColumn" : { "name" : "VERSION", "method" : "SHA256" }, "lastModifiedColumn" : { "name" : "LAST_MODIFIED" }, "creationTimeColumn" : { "name" : "CREATED_ON" }, "readOnly" : false }'; 
  l_collection := DBMS_SODA.create_collection('TestCollection2', l_metadata); 
  IF l_collection IS NOT NULL THEN 
    DBMS_OUTPUT.put_line('Collection ID : ' || l_collection.get_name()); 
  ELSE 
    DBMS_OUTPUT.put_line('Collection does not exist.'); 
  END IF; 
END;

问题原因 #

1. JSON重复键被覆盖：元数据中重复定义了contentColumn键，JSON解析器会自动保留最后一个重复键的值，前一个contentColumn（对应JSON_DOCUMENT）会被覆盖，但SODA默认仍会创建主JSON文档列，导致自定义列未被识别。
2. 自定义列定义方式错误：Oracle SODA中，主JSON文档列由contentColumn定义，而自定义列需要通过columns数组来声明，不能复用contentColumn字段。

解决方法 #

在集合元数据中添加columns数组，用于定义自定义列的属性（名称、SQL类型、长度等）。修正后的元数据需确保：

• 仅保留一个contentColumn定义主JSON文档列
• 通过columns数组添加自定义列

修正后的元数据 #

{ 
  "schemaName" : "mySchemaName", 
  "tableName" : "myTableName", 
  "keyColumn" : { 
    "name" : "ID", 
    "sqlType" : "VARCHAR2", 
    "maxLength" : 255, 
    "assignmentMethod" : "UUID" 
  }, 
  "contentColumn" : { 
    "name" : "JSON_DOCUMENT", 
    "sqlType" : "BLOB", 
    "compress" : "NONE", 
    "cache" : true, 
    "encrypt" : "NONE", 
    "validation" : "STANDARD" 
  }, 
  "columns": [
    {
      "name": "CUSTOM_COL",
      "sqlType": "VARCHAR2",
      "maxLength": 100,
      "nullable": true
    }
  ],
  "versionColumn" : { 
    "name" : "VERSION", 
    "method" : "SHA256" 
  }, 
  "lastModifiedColumn" : { 
    "name" : "LAST_MODIFIED" 
  }, 
  "creationTimeColumn" : { 
    "name" : "CREATED_ON" 
  }, 
  "readOnly" : false 
}

注：自定义列的sqlType可根据需求调整，比如用VARCHAR2存储类型标识更合适，无需用BLOB。

修正后的PL/SQL创建脚本 #

DECLARE 
  l_collection SODA_COLLECTION_T; 
  l_metadata VARCHAR2(32767); 
BEGIN 
  l_metadata := '{ 
    "schemaName" : "schema_name", 
    "tableName" : "table_name", 
    "keyColumn" : { 
      "name" : "ID", 
      "sqlType" : "VARCHAR2", 
      "maxLength" : 255, 
      "assignmentMethod" : "UUID" 
    }, 
    "contentColumn" : { 
      "name" : "JSON_DOCUMENT", 
      "sqlType" : "BLOB", 
      "compress" : "NONE", 
      "cache" : true, 
      "encrypt" : "NONE", 
      "validation" : "STANDARD" 
    }, 
    "columns": [
      {
        "name": "CUSTOM_COL",
        "sqlType": "VARCHAR2",
        "maxLength": 100,
        "nullable": true
      }
    ],
    "versionColumn" : { 
      "name" : "VERSION", 
      "method" : "SHA256" 
    }, 
    "lastModifiedColumn" : { 
      "name" : "LAST_MODIFIED" 
    }, 
    "creationTimeColumn" : { 
      "name" : "CREATED_ON" 
    }, 
    "readOnly" : false 
  }'; 
  l_collection := DBMS_SODA.create_collection('TestCollection2', l_metadata); 
  IF l_collection IS NOT NULL THEN 
    DBMS_OUTPUT.put_line('Collection ID : ' || l_collection.get_name()); 
  ELSE 
    DBMS_OUTPUT.put_line('Collection does not exist.'); 
  END IF; 
END;
/

验证 #

创建集合后，可通过查询表结构确认自定义列是否存在：

DESC myTableName;

内容的提问来源于stack exchange，提问作者Yogesh Sudheer Modak