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

问题描述

我创建了以下脚本，以便从Oracle SQL Developer读取来自Mobile App DB(基于MongoDB)的数据:

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;
/

响应(l_response_text)是类似于JSON的字符串.例如:

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", ..... ]

代码工作正常，并将响应插入一个称为 appery 的列表中.但是，我需要解析此响应，以便每个数组进入名为 appery_test 的表中的特定列.表 appery_test 的列数与JSON对数相同，并且顺序相同.

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.

我进行了搜索，发现有关将Oracle表解析为JSON的大多数结果，而并非相反.不过，我发现了此链接，该链接与我的问题有些相似.但是，答案中建议的库没有有关如何使用它通过PL/SQL将JSON插入常规表的任何示例.

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.

NB:我使用的是11g而不是12c.因此内置函数对我不可用.

N.B.: I'm using 11g and not 12c. So the built in functions are not available for me.

推荐答案

我使用了 PL/JSON库.具体来说，JSON_EXT包功能是为了对其进行解析.

I used PL/JSON library. Specifically, JSON_EXT package functions in order to parse it.

以下受Oracle社区答案启发的脚本为我工作了

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 .....

请注意， json_ext.get_string 仅重新调整最大为32767的VARCHAR2.为了对更大的json_list和json_values(> 32KB)使用相同的软件包，请检查

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.

如果您拥有APEX 5.0及更高版本，则可以通过 APEX_JSON 软件包.有关详细信息，请参见@ Olafur Tryggvason 的答案

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屋！