oracle varchar处理json,使用PL / SQL将JSON解析为Oracle表

小编典典

我使用PL /JSON库。具体来说，JSON_EXT包用于解析它。

以下受Oracle Community Answer启发的脚本对我有用

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)使用相同的包，请在此处检查。

如果您拥有APEX5.0及更高版本，则可以通过APEX_JSON软件包获得更好的选择和更好的性能。

2020-07-27