Oracle存储过程动态SQL赋值及执行结果插入表的问题

你可以执行l_sel_sql := l_sel_query_rec.SQL_QUERY;这个赋值操作，但当前的代码和存储的SQL内容存在多处必须修复的问题，否则运行会报错：

1. 存储的SQL内容格式错误 #

你给出的sql_query字段示例外层多了一对不必要的单引号：

# 错误写法，多了外层单引号
'SELECT  MBB_NO AS mbb_no, clob_column AS CLOB1, JSON_OBJECT(MBB_NO RETURNING CLOB) AS DEL_DATA FROM A '

这个单引号是编写SQL字面量的时候才需要加的转义符号，实际存在字段里的SQL文本不需要带外层单引号，直接存SQL本身即可：

# 正确存储内容
SELECT  MBB_NO AS mbb_no, clob_column AS CLOB1, JSON_OBJECT(MBB_NO RETURNING CLOB) AS DEL_DATA FROM A 

如果带着外层单引号存储，赋值后执行动态SQL时，Oracle会把单引号也当作SQL语句的一部分，直接抛出语法错误。

2. 字段长度适配问题 #

你当前定义SQL_QUERY和l_sel_sql的类型都是VARCHAR2(2500)，如果后续SQL语句长度超过2500字节，会直接触发长度超限报错。建议将两个变量/字段的类型改为CLOB，Oracle动态SQL原生支持CLOB类型的语句文本，可以适配更长的查询需求。

3. 记录类型匹配问题 #

你定义的json_data_type记录中包含CLOB2字段，但你给出的SQL示例中没有返回对应的列，fetch的时候会因为返回列数、类型和记录不匹配抛出错误。你需要保证所有存储的动态SQL的返回列顺序、类型、数量都和json_data_type完全一致，或者删掉没用的CLOB2字段。

可选优化建议 #

• 建议在动态SQL执行逻辑处增加异常捕获，出错时可以记录对应的SQL语句和报错信息，避免整个存储过程直接终止，也方便排查问题
• 数据量大的场景下，建议把循环单条插入改为FORALL批量插入，性能会有明显提升

内容的提问来源于stack exchange，提问作者Eren Durmuş