create or replace PROCEDURE “proc_json_test”(
v_json1 in json,
v_json2 in VARCHAR2,
p_xx1 out sys_refcursor,
p_xx2 out sys_refcursor
)
as
v_varchar VARCHAR2(200);
BEGIN
– Enter your procedure code
SELECT CASE WHEN v_json1 IS JSON THEN 'Valid JSON' ELSE 'Invalid JSON' END
into v_varchar
from dual;
DBMS_OUTPUT.PUT_LINE(v_varchar);
open p_xx1 for
SELECT name, age, address
FROM JSON_TABLE(’[{“name”:“John”,“age”:30,“address”:{“city”:“New York”,“state”:“MY”},“hobbies”:“reading”},{“name”:“𧿹囊肿”,“age”:30,“address”:{“city”:“New York”,“state”:“MY”},“hobbies”:“reading”},{“name”:“测试”,“age”:30,“address”:{“city”:“New York”,“state”:“MY”},“hobbies”:“reading”}]’,
'$[*]' COLUMNS (name VARCHAR2(50) PATH '$.name',
age NUMBER PATH '$.age',
address VARCHAR2(500) FORMAT JSON PATH '$.address'));
open p_xx2 for
SELECT name, age, address
FROM JSON_TABLE(v_json2,
'$[*]' COLUMNS (name VARCHAR2(50) PATH '$.name',
age NUMBER PATH '$.age',
address VARCHAR2(500) FORMAT JSON PATH '$.address'));
END;
创建存储过程；
调用的时候 给参数
[{“name”:“John”,“age”:30,“address”:{“city”:“New York”,“state”:“MY”},“hobbies”:“reading”},{“name”:“𧿹囊肿”,“age”:30,“address”:{“city”:“New York”,“state”:“MY”},“hobbies”:“reading”},{“name”:“测试”,“age”:30,“address”:{“city”:“New York”,“state”:“MY”},“hobbies”:“reading”}]
报错：
