Oracle动态为JSON_TABLE选择列报ORA-00904错误求排查

• 核心错误：你当前代码中j_keys变量存储的是查询列定义的SQL语句文本，而非该SQL执行后返回的拼接好的列定义字符串。你直接将整个SELECT子查询拼到了JSON_TABLE的COLUMNS子句中，SQL语法不支持COLUMNS后接子查询，因此触发ORA-00904标识符无效错误。
• 次要潜在问题：如果get_keys是你自定义的JSON键提取函数，需要确认该函数在当前schema下有权限访问、语法正确，否则也可能触发标识符报错。

你需要先执行获取动态列的查询，将返回的列定义结果赋值给j_keys，再拼接最终的动态查询语句，修复后的代码如下：

DECLARE
     rc sys_refcursor;         
     j_keys varchar2(2000);
     query_s varchar2(20000);

BEGIN
 -- 先执行查询拿到拼接好的列定义字符串，赋值给j_keys
 EXECUTE IMMEDIATE '
        SELECT 
                listagg(distinct k.COLUMN_VALUE || '' varchar(256) PATH ''$.'' ||  k.COLUMN_VALUE, '', '') as j_cols
        FROM   (select json_response as json_value from SOME_TABLE where param=''some_param'') t
               CROSS APPLY JSON_TABLE(
                 t.json_value,
                 ''$[*]''
                 COLUMNS (
                   idx FOR ORDINALITY,
                   json_obj VARCHAR2(4000) FORMAT JSON PATH ''$''
                 )
               ) jt
               CROSS APPLY get_keys( jt.json_obj ) k
 ' INTO j_keys;
                 
  -- 再拼接最终动态查询语句
  query_s := 'SELECT * FROM json_table((select json_response from SOME_TABLE where param=''some_param''), ''$[*]''
                 COLUMNS 
                        ' || j_keys || ')';
                        
  open rc for query_s;
              
dbms_sql.return_result(rc);

END;
/

补充注意事项 #

如果你的get_keys函数不可用，可以用Oracle原生方法替代，避免自定义函数依赖，将CROSS APPLY get_keys( jt.json_obj ) k替换为以下代码即可：

CROSS APPLY (
    SELECT t.COLUMN_VALUE 
    FROM JSON_OBJECT_T.parse(jt.json_obj).get_keys() t
) k

如果JSON键的数量较多，listagg返回的结果超过varchar2(2000)的长度限制，可以将j_keys的类型改为CLOB，同时使用listagg(... ON OVERFLOW TRUNCATE)语法处理长文本。

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