Oracle REST Handler批量JSON按行拆分插入单个CLOB列方案咨询

按照Oracle-base指南完成Oracle DB、ORDS安装及全部Handler、关联存储过程创建后，存在待优化逻辑：批量提交的JSON报文为每行一条、末尾自带换行符，现有逻辑会将全部报文整体存入单条CLOB字段，需要实现按行拆分单条JSON，分别插入到独立的CLOB列中。

• Handler代码

BEGIN
      create_kayit(p_kayit => :body_text);
END;

• 原有存储过程代码

create or replace PROCEDURE create_kayit (
p_kayit IN CLOB
)
AS
BEGIN
    INSERT INTO uemtest (kayit) VALUES (p_kayit);
EXCEPTION
    WHEN OTHERS THEN
        HTP.print(SQLERRM);
END;

无需修改现有Handler逻辑，仅调整存储过程的插入逻辑即可，通过Oracle正则多行匹配+层级查询实现按行拆分CLOB内容批量插入：

CREATE OR REPLACE PROCEDURE create_kayit (p_kayit IN CLOB) AS
BEGIN
        INSERT INTO UEMTEST (KAYIT)
        SELECT
        REGEXP_SUBSTR(p_kayit, '.+', 1, level, 'm') KAYIT
        FROM DUAL connect by level <= length(REGEXP_REPLACE(p_kayit,'.+'));
EXCEPTION
    WHEN OTHERS THEN
        HTP.print(SQLERRM);
END;

逻辑说明 #

• REGEXP_SUBSTR的第5个参数传入'm'开启多行匹配模式，.默认不会匹配换行符，可逐行提取非空内容
• 层级查询connect by level <= length(REGEXP_REPLACE(p_kayit,'.+'))控制循环次数等于报文总行数，逐行提取内容分别插入，实现一行JSON对应一条表记录

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