Oracle12c读取输入参数中的JSON，由于JSON内容换行，读不到JSON中的值如何处理？

[非著名魔兽主持人]

大家好，Oracle12c读取输入参数中的JSON，由于JSON内容换行，读不到JSON中的值如何处理？
JSON内容为：

 { "total": 5,  
      "rows": [  
        {  
            "ID":1201,  
            "NAME": "Peter",  
            "AGE": 23,  
            "SE": "male",  
  "FLAG":"delete"  
        },  
         {  
            "ID":1568,  
            "NAME": "Tom",  
            "AGE": 32,  
            "SE": "male",  
  "FLAG":"insert"  
        },  
  {  
            "ID":6695,  
            "NAME": "Lucy",  
            "AGE": 13,  
            "SE": "female",  
  "FLAG":"update"  
        },  
  {  
            "ID":6720,  
            "NAME": "Kate",  
            "AGE": 15,  
            "SE": "female",  
  "FLAG":"update"  
        },  
  {  
            "ID":2336,  
            "NAME": "Jack",  
            "AGE": 13,  
            "SE": "male",  
  "FLAG":"insert"  
        }  
  ]  
  }  

存储过程读取JSON的代码为：

SELECT JSON_VALUE(V_JSON_STR, '$.total') INTO V_COUNT FROM dual;  
  
  if V_COUNT>0 then   
  
  
  for V_I in 0..(V_COUNT-1)   
  loop  
  execute immediate 'select  JSON_VALUE(''' || V_JSON_STR || ''', ''$.rows[' || V_I || '].ID''),  
                                   JSON_VALUE(''' || V_JSON_STR || ''', ''$.rows[' || V_I || '].NAME''),  
                                   JSON_VALUE(''' || V_JSON_STR || ''', ''$.rows[' || V_I || '].AGE''),  
                                   JSON_VALUE(''' || V_JSON_STR || ''', ''$.rows[' || V_I || '].SE''),  
                                   JSON_VALUE(''' || V_JSON_STR || ''', ''$.rows[' || V_I || '].FLAG'')  
                             from  dual' into V_ID,V_NAME,V_AGE,V_SE,V_FLAG;  
  
  
  DBMS_OUTPUT.PUT_LINE(  
                             V_ID || ' , ' || V_NAME || ' , ' || V_AGE ||  
                             ' , ' || V_SE || ' , ' || V_FLAG  
                             );  
  
  if V_FLAG = 'insert' then   
  insert into TEST_INS_UPD_DEL(ID,NAME,AGE,SE)values(V_ID,V_NAME,V_AGE,V_SE);  
  end if;  
  
  
  if V_FLAG = 'delete' then  
  delete from TEST_INS_UPD_DEL where ID = V_ID;  
  end if;  
  
  
  if V_FLAG = 'update' then  
  update TEST_INS_UPD_DEL   
  set NAME = V_NAME,  
  AGE = V_AGE,  
  SE = V_SE  
  where ID = V_ID;  
  end if;  
  end loop;   
  end if;  
  
  V_OUT_RETURN := 'OK';  
  RETURN;  
   END IF;  

输出结果为：
, , , ,
, , , ,
, , , ,
, , , ,
, , , ,
V_OUT_RETURN = OK
表中的数据没有改变。

如果将JSON修改为一行，如下：
{"total": 5,"rows": [{"ID":1201,"NAME":"Peter","AGE": 23,"SEX":"male","FLAG":"delete"},{"ID":1568,"NAME":"Tom","AGE":32,"SEX":"male","FLAG":"insert"},{"ID":6695,"NAME":"Lucy","AGE":13,"SEX":"female","FLAG":"update"},{"ID":6720,"NAME":"Kate","AGE":15,"SEX":"female","FLAG":"update"},{"ID":2336,"NAME":"Jack","AGE": 13,"SEX":"male","FLAG":"insert"}]}

则输出的结果为：
1201 , Peter , 23 , male , delete
1568 , Tom , 32 , male , insert
6695 , Lucy , 13 , female , update
6720 , Kate , 15 , female , update
2336 , Jack , 13 , male , insert
V_OUT_RETURN = OK
表中的数据发生了改变。
请问如何处理能让JSON存在空格换行，制表符的情况下也能读取出来？谢谢？

[ghx287524027]

你把 id，name，age，flag，sex上的引号去了呢？

[ghx287524027]

经检测，是环境导致的问题。为避免类似情况，采取将空白符虑去的方法： select regexp_replace(v_json_str,'\s') into v_json_str from dual;

[非著名魔兽主持人]

引用 2 楼 ghx287524027 的回复:

经检测，是环境导致的问题。为避免类似情况，采取将空白符虑去的方法： select regexp_replace(v_json_str,'\s') into v_json_str from dual;

[ghx287524027]

CREATE OR REPLACE 
PROCEDURE "PRO_TEST_DML"  (
	V_PAGE_NO    IN  VARCHAR2,--页面Number
	V_FUN_ID     IN    VARCHAR2,--页面Number,对应的功能方法ID
	V_SRC_JSON_STR   IN  varchar2,--前端传到后台的JSON字符串
	V_OUT_RETURN OUT VARCHAR2--输出参数
	--V_OUT_JSON   OUT CLOB --生成json，返回给前端调用者                         
)
IS
	V_OU_MSG VARCHAR(3000);
	V_COUNT NUMBER;
	V_JSON_STR varchar2(4000);
	
BEGIN
 
--执行存储过程输入参数
--输入参数1填写： 999
--输入参数2填写： dml
--输入参数3填写：  
/*
   { "total": 5,
     "rows": [
        {
            "ID":1201,
            "NAME": "Peter",
            "AGE": 23,
            "SEX": "male",
            "FLAG":"delete"
        },
         {
            "ID":1568,
            "NAME": "Tom",
            "AGE": 32,
            "SEX": "male",
            "FLAG":"insert"
        },
        {
            "ID":6695,
            "NAME": "Lucy",
            "AGE": 13,
            "SEX": "female",
            "FLAG":"update"
        },
        {
            "ID":6720,
            "NAME": "Kate",
            "AGE": 15,
            "SEX": "female",
            "FLAG":"update"
        },
        {
            "ID":2336,
            "NAME": "Jack",
            "AGE": 13,
            "SEX": "male",
            "FLAG":"insert"
        }
      ]
    }*/
   
 
        V_OUT_RETURN := '';   
 
        IF V_PAGE_NO ='' OR V_PAGE_NO IS NULL OR V_PAGE_NO !='999' OR 
		   V_FUN_ID ='' OR V_FUN_ID IS NULL OR V_FUN_ID !='dml' OR 
		   V_SRC_JSON_STR ='' OR V_SRC_JSON_STR IS NULL  
		THEN
			V_OUT_RETURN := 'ERROR';
			RETURN;
         
        ELSE 
			SELECT JSON_VALUE(V_SRC_JSON_STR, '$.total') INTO V_COUNT FROM dual;--一共有几条数据要处理
			select regexp_replace(V_SRC_JSON_STR,'\s') into V_JSON_STR from dual;--过滤空白字符

			
			if V_COUNT>0 then --有数据时执行下面的操作
				for i in 0..(V_COUNT-1) /* 此处可以用V_OU_ARRAY来代表输入参数中的数组吗？*/
				loop
					execute immediate 'select  JSON_VALUE(''' || V_JSON_STR || ''', ''$.rows[' || i || '].ID''),
                                   JSON_VALUE(''' || V_JSON_STR || ''', ''$.rows[' || i || '].NAME''),
                                   JSON_VALUE(''' || V_JSON_STR || ''', ''$.rows[' || i || '].AGE''),
                                   JSON_VALUE(''' || V_JSON_STR || ''', ''$.rows[' || i || '].SEX''),
                                   JSON_VALUE(''' || V_JSON_STR || ''', ''$.rows[' || i || '].FLAG'')
                             from  dual' into v_id,v_name,v_age,v_sex,v_flag;
					--在 JSON_VALUE()中区分大小写，所以以上标红的地方都使用了大写，与json数据中保持一致
					if v_flag = 'insert' then /*i这个变量怎么关联到V_COUNT去循环？*/
						insert into TEST_INS_UPD_DEL(ID,NAME,AGE,SEX)values(v_id,v_name,v_age,v_sex);
					end if;
					if v_flag = 'delete' then
						delete from TEST_INS_UPD_DEL where ID=v_id;
					end if;
					if v_flag = 'delete' then
						update TEST_INS_UPD_DEL 
						set NAME = v_name,
							AGE = v_age,
							SEX = v_sex
							where ID = v_id;
					end if;
				end loop; 
			end if;
			V_OUT_RETURN := 'message:增加，删除，修改成功';
			RETURN;
        END IF;
     
        EXCEPTION
        WHEN OTHERS THEN
        V_OUT_RETURN := 'ERROR';
        V_OU_MSG    := '失败，原因是：' || SQLERRM;
        ROLLBACK;
        INSERT INTO SYS_ERROR_LOG
          (ROW_ID, PR_NAME, ERROR_DESC, INSDT)
        VALUES
          (SYS_ERROR_LOG_SEQ.NEXTVAL, 'PRO_TEST_DML', V_OU_MSG, SYSDATE);
        COMMIT;
END PRO_TEST_DML;

[西门呀在吹雪]

格式就破坏了啊。