使用 PL/SQL 将 JSON 解析为 Oracle 表 [英] Parse JSON into Oracle table using PL/SQL

查看:42
本文介绍了使用 PL/SQL 将 JSON 解析为 Oracle 表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

为了从 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆