如何将Oracle CLOB中的列列表转换为JSON_TABLE兼容的列定义格式？

可以直接用REGEXP_REPLACE函数完成格式转换，无需额外拼接步骤，以下是具体实现：

核心转换语句 #

假设你的CLOB列存储的原始列列表格式为col_a,col_b,col_c，执行以下SQL即可直接生成JSON_TABLE所需的列定义：

SELECT REGEXP_REPLACE(
         your_clob_column,
         '(\w+)',
         '\1 NUMBER(22,3) PATH ''$.\1'''
       ) AS json_table_column_defs
FROM your_table;

代码说明 #

• (\w+)：正则表达式匹配每个列名（支持字母、数字、下划线组成的Oracle合法标识符）
• \1：引用匹配到的列名，替换为指定格式列名 NUMBER(22,3) PATH '$.列名'
• 单引号'在Oracle字符串中需要用两个单引号''转义

处理带空格的列列表 #

如果原始列列表包含空格（比如col_a , col_b），可以先通过REPLACE去除空格再转换：

SELECT REGEXP_REPLACE(
         REPLACE(your_clob_column, ' ', ''),
         '(\w+)',
         '\1 NUMBER(22,3) PATH ''$.\1'''
       ) AS json_table_column_defs
FROM your_table;

PL/SQL中直接使用示例 #

DECLARE
  v_raw_cols CLOB;
  v_json_cols CLOB;
  v_dynamic_sql VARCHAR2(32767);
BEGIN
  -- 获取存储在CLOB中的原始列列表
  SELECT column_list_clob INTO v_raw_cols FROM dynamic_columns WHERE id = 1;
  
  -- 转换为JSON_TABLE所需的列定义格式
  v_json_cols := REGEXP_REPLACE(v_raw_cols, '(\w+)', '\1 NUMBER(22,3) PATH ''$.\1''');
  
  -- 构建动态查询语句
  v_dynamic_sql := 'SELECT jt.* 
                    FROM your_json_data_table t,
                         JSON_TABLE(t.json_column, ''$'' COLUMNS (' || v_json_cols || ')) jt';
  
  -- 执行动态SQL（或打印验证）
  DBMS_OUTPUT.PUT_LINE(v_dynamic_sql);
END;
/

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