使用 PL/SQL 将 JSON 解析为 Oracle 表 [英] Parse JSON into Oracle table using PL/SQL
问题描述
为了从 Oracle SQL Developer 的 Mobile App DB(基于 MongoDB)读取数据,我创建了以下脚本:
<前>宣布l_param_list VARCHAR2(512);l_http_request UTL_HTTP.req;l_http_response UTL_HTTP.resp;l_response_text VARCHAR2(32767);开始-- 服务的输入参数-- 准备请求...l_http_request := UTL_HTTP.begin_request ('https://api.appery.io/rest/1/db/collections/Photos?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D', '得到', 'HTTP/1.1');-- ...设置标题的属性UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');--UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list));-- ...设置输入参数-- UTL_HTTP.write_text(l_http_request, l_param_list);-- 获取响应并获取接收值l_http_response := UTL_HTTP.get_response(l_http_request);UTL_HTTP.read_text(l_http_response, l_response_text);DBMS_OUTPUT.put_line(l_response_text);插入 appery 值(l_response_text);——定稿UTL_HTTP.end_response(l_http_response);例外当 UTL_HTTP.end_of_body 时然后 UTL_HTTP.end_response(l_http_response);结尾;/响应 (l_response_text) 是一个类似 JSON 的字符串.例如:
[{"Postcode":"47100","OutletCode":"128039251","MobileNumber":"0123071303","_createdAt":"2014-11-10 06:12:49.837","_updatedAt":"2014-11-10 06:12:49.837"}, {"Postcode":"32100","OutletCode":"118034251", ..... ]
代码运行良好,并将响应插入到一个名为 appery 的列表中.但是,我需要解析此响应,以便每个数组都进入名为 appery_test 的表中的特定列.表 appery_test 的列数与 JSON 对的数量相同且顺序相同.
我搜索并找到了大部分关于将 Oracle 表解析为 JSON 的结果,而不是相反.不过,我发现 this 链接与我的问题有些相似.但是,答案中建议的库没有关于如何使用它使用 PL/SQL 将 JSON 插入到常规表中的任何示例.
注意:我使用的是 11g 而不是 12c.所以内置函数对我不可用.
我使用了 PL/JSON 库.具体来说,JSON_EXT 包函数以解析它.
受 Oracle 社区答案启发的以下脚本对我有用
<前>宣布l_param_list VARCHAR2(512);l_http_request UTL_HTTP.req;l_http_response UTL_HTTP.resp;l_response_text VARCHAR2(32767);l_list json_list;A_id VARCHAR2(200);用户 ID VARCHAR2(100);用户名 VARCHAR2(100);出口代码 VARCHAR2(100);出口名称 VARCHAR2(100);手机号码 VARCHAR2(100);电话号码 VARCHAR2(100);地址 VARCHAR2(100);城市 VARCHAR2(100);状态 VARCHAR2(100);邮政编码 VARCHAR2(100);电子邮件 VARCHAR2(100);更新计数 VARCHAR2(100);loginCount VARCHAR2(100);参考图片 VARCHAR2(100);更新 VARCHAR2(100);帐户锁定 VARCHAR2(100);Oracle_Flag VARCHAR2(100);acl VARCHAR2(100);开始-- 服务的输入参数-- 准备请求...l_http_request := UTL_HTTP.begin_request('https://api.appery.io/rest/1/db/collections/Outlet_Details?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D', '得到', 'HTTP/1.1');-- ...设置标题的属性UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');--UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list));-- ...设置输入参数-- UTL_HTTP.write_text(l_http_request, l_param_list);-- 获取响应并获取接收值l_http_response := UTL_HTTP.get_response(l_http_request);UTL_HTTP.read_text(l_http_response, l_response_text);DBMS_OUTPUT.put_line(l_response_text);l_list := json_list(l_response_text);FOR i IN 1..l_list.count环形A_id := json_ext.get_string(json(l_list.get(i)),'_id');UserId := json_ext.get_string(json(l_list.get(i)),'UserId');用户名 := json_ext.get_string(json(l_list.get(i)),'用户名');OutletCode := json_ext.get_string(json(l_list.get(i)),'OutletCode');OutletName := json_ext.get_string(json(l_list.get(i)),'OutletName');MobileNumber := json_ext.get_string(json(l_list.get(i)),'MobileNumber');PhoneNumber := json_ext.get_string(json(l_list.get(i)),'PhoneNumber');地址 := json_ext.get_string(json(l_list.get(i)),'地址');城市:= json_ext.get_string(json(l_list.get(i)),'City');状态:= json_ext.get_string(json(l_list.get(i)),'State');邮政编码:= json_ext.get_string(json(l_list.get(i)),'邮政编码');电子邮件:= json_ext.get_string(json(l_list.get(i)),'Email');UpdateCount := json_ext.get_string(json(l_list.get(i)),'UpdateCount');loginCount := json_ext.get_string(json(l_list.get(i)),'loginCount');ReferencePhoto := json_ext.get_string(json(l_list.get(i)),'ReferencePhoto');更新:= json_ext.get_string(json(l_list.get(i)),'更新');AccountLocked := json_ext.get_string(json(l_list.get(i)),'AccountLocked');Oracle_Flag := json_ext.get_string(json(l_list.get(i)),'Oracle_Flag');acl := json_ext.get_string(json(l_list.get(i)),'acl');插入 .....请注意,json_ext.get_string 仅返回限制为 32767 最大值的 VARCHAR2.为了使用具有更大 json_list 和 json_values (>32KB) 的相同包,请检查 此处.
如果您有 APEX 5.0 及更高版本,则可以通过 APEX_JSON 包.详情请参阅@Olafur Tryggvason 的回答
I have created the following script in order to read data from Mobile App DB (which is based on MongoDB) from Oracle SQL Developer:
DECLARE l_param_list VARCHAR2(512); l_http_request UTL_HTTP.req; l_http_response UTL_HTTP.resp; l_response_text VARCHAR2(32767); BEGIN -- service's input parameters -- preparing Request... l_http_request := UTL_HTTP.begin_request ('https://api.appery.io/rest/1/db/collections/Photos?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D' , 'GET' , 'HTTP/1.1'); -- ...set header's attributes UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe'); --UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list)); -- ...set input parameters -- UTL_HTTP.write_text(l_http_request, l_param_list); -- get Response and obtain received value l_http_response := UTL_HTTP.get_response(l_http_request); UTL_HTTP.read_text(l_http_response, l_response_text); DBMS_OUTPUT.put_line(l_response_text); insert into appery values(l_response_text); -- finalizing UTL_HTTP.end_response(l_http_response); EXCEPTION WHEN UTL_HTTP.end_of_body THEN UTL_HTTP.end_response(l_http_response); END; /
The response (l_response_text) is a JSON-like string. For example:
[{"Postcode":"47100","OutletCode":"128039251","MobileNumber":"0123071303","_createdAt":"2014-11-10 06:12:49.837","_updatedAt":"2014-11-10 06:12:49.837"}, {"Postcode":"32100","OutletCode":"118034251", ..... ]
The code works fine, and inserts the response into one column-table called appery. However, I need to parse this response such that each array goes into into its specific column in a table called appery_test. The table appery_test has a number of columns same as the number of JSON pairs and in the same order.
I searched and I found most of the results about parsing Oracle table into JSON and not the opposite. I found, though, this link which is somewhat similar to my issue. However, the suggested library in the answer does not have any example on how to use it to insert JSON into conventional table using PL/SQL.
N.B.: I'm using 11g and not 12c. So the built in functions are not available for me.
I used PL/JSON library. Specifically, JSON_EXT package functions in order to parse it.
The following script inspired by Oracle Community answer worked for me
DECLARE l_param_list VARCHAR2(512); l_http_request UTL_HTTP.req; l_http_response UTL_HTTP.resp; l_response_text VARCHAR2(32767); l_list json_list; A_id VARCHAR2(200); UserId VARCHAR2(100); UserName VARCHAR2(100); OutletCode VARCHAR2(100); OutletName VARCHAR2(100); MobileNumber VARCHAR2(100); PhoneNumber VARCHAR2(100); Address VARCHAR2(100); City VARCHAR2(100); State VARCHAR2(100); Postcode VARCHAR2(100); Email VARCHAR2(100); UpdateCount VARCHAR2(100); loginCount VARCHAR2(100); ReferencePhoto VARCHAR2(100); Updates VARCHAR2(100); AccountLocked VARCHAR2(100); Oracle_Flag VARCHAR2(100); acl VARCHAR2(100); BEGIN -- service's input parameters -- preparing Request... l_http_request := UTL_HTTP.begin_request('https://api.appery.io/rest/1/db/collections/Outlet_Details?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D' , 'GET' , 'HTTP/1.1'); -- ...set header's attributes UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe'); --UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list)); -- ...set input parameters -- UTL_HTTP.write_text(l_http_request, l_param_list); -- get Response and obtain received value l_http_response := UTL_HTTP.get_response(l_http_request); UTL_HTTP.read_text(l_http_response, l_response_text); DBMS_OUTPUT.put_line(l_response_text); l_list := json_list(l_response_text); FOR i IN 1..l_list.count LOOP A_id := json_ext.get_string(json(l_list.get(i)),'_id'); UserId := json_ext.get_string(json(l_list.get(i)),'UserId'); UserName := json_ext.get_string(json(l_list.get(i)),'UserName'); OutletCode := json_ext.get_string(json(l_list.get(i)),'OutletCode'); OutletName := json_ext.get_string(json(l_list.get(i)),'OutletName'); MobileNumber := json_ext.get_string(json(l_list.get(i)),'MobileNumber'); PhoneNumber := json_ext.get_string(json(l_list.get(i)),'PhoneNumber'); Address := json_ext.get_string(json(l_list.get(i)),'Address'); City := json_ext.get_string(json(l_list.get(i)),'City'); State := json_ext.get_string(json(l_list.get(i)),'State'); Postcode := json_ext.get_string(json(l_list.get(i)),'Postcode'); Email := json_ext.get_string(json(l_list.get(i)),'Email'); UpdateCount := json_ext.get_string(json(l_list.get(i)),'UpdateCount'); loginCount := json_ext.get_string(json(l_list.get(i)),'loginCount'); ReferencePhoto := json_ext.get_string(json(l_list.get(i)),'ReferencePhoto'); Updates := json_ext.get_string(json(l_list.get(i)),'Updates'); AccountLocked := json_ext.get_string(json(l_list.get(i)),'AccountLocked'); Oracle_Flag := json_ext.get_string(json(l_list.get(i)),'Oracle_Flag'); acl := json_ext.get_string(json(l_list.get(i)),'acl'); insert .....
Notice that json_ext.get_string retuns only VARCHAR2 limited to 32767 max. In order to use the same package with larger json_list and json_values (>32KB) check here.
If you have APEX 5.0 and above, better option and much better performance via APEX_JSON package. See @Olafur Tryggvason's answer for details
这篇关于使用 PL/SQL 将 JSON 解析为 Oracle 表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!