Oracle 12C中JSON_TABLE列使用%TYPE属性报错问题咨询

你所描述的场景下，Oracle的JSON_TABLE语法不支持使用%TYPE属性定义列类型，同时该语法对可使用的列数据类型有明确限制。

• %TYPE是PL/SQL专属特性，仅可在存储过程、函数、匿名块等PL/SQL上下文的变量/参数声明场景使用，你当前编写的INSERT语句属于纯SQL执行上下文，SQL语法本身不识别%TYPE标记，因此哪怕引用的字段类型是JSON_TABLE支持的DATE，也会触发ORA-40484报错。
• JSON_TABLE的COLUMNS子句对返回列类型有固定的支持范围：字符串类型仅支持VARCHAR2、NVARCHAR2、CLOB、NCLOB，不支持CHAR、NCHAR类型，这也是你无法使用CHAR(36)定义uuid列的原因。

如果需要和表字段类型保持对齐、避免硬编码类型，可以使用以下两种方案：

1. 使用PL/SQL动态SQL：在PL/SQL块中先通过%TYPE获取对应字段的类型定义，再拼接组装成完整的INSERT语句执行
2. 外层SELECT显式转换类型：JSON_TABLE内部先用兼容的通用类型定义列，在外层查询时做类型转换，示例代码如下：

INSERT INTO log (
   "uuid",
   "date",
   "msg",
   "level"
)
WITH t ( log ) AS (
   SELECT
      JSON_QUERY('[{"uuid": "20000000-0000-0000-0000-000000000000", "date": "2021-10-17", "msg":"aaaa", "level": "debug" },
               {"uuid": "20000000-0000-0000-0000-000000000000", "date": "2021-10-17", "msg":"bbbb", "level": "debug" }]'
      , '$')
   FROM
      dual
)
SELECT
   CAST(uuid AS log.uuid%TYPE) AS "uuid",
   CAST(date_col AS log.date%TYPE) AS "date",
   CAST(msg AS log.msg%TYPE) AS "msg",
   CAST(level AS log.level%TYPE) AS "level"
FROM
   t
   CROSS JOIN
      JSON_TABLE ( log, '$'
         COLUMNS (
            NESTED PATH '$[*]'
               COLUMNS (
                  uuid VARCHAR2 ( 36 ) PATH '$.uuid',
                  date_col DATE PATH '$.date',
                  msg VARCHAR2 ( 1024 ) PATH '$.msg',
                  level VARCHAR2 ( 5 ) PATH '$.level'
               )
         )
      )

注意：CAST转换时外层SELECT的上下文如果是PL/SQL，%TYPE可以正常识别，如果是纯SQL执行场景，还是需要写死具体类型。

内容的提问来源于stack exchange，提问作者user5507535