苏南生的CSDN博客

Oracle ERP Technology

PLSQL解析JSON,并保存到数据库

先安装JSON工具包

https://github.com/pljson/pljson

之后解析JSON并保存到数据库中

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 .....
阅读更多
版权声明:http://www.sunansheng.com/ https://blog.csdn.net/sunansheng/article/details/77719106
个人分类: Oracle PLSQL
上一篇Seafile Web API
下一篇HTTPS详细解析
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭