【Oracle】plsql调用服务并解析json

 

 

Declare
req utl_http.req;
resp utl_http.resp;
v_line Varchar2(4000);
v_text Varchar2(4000);
v_param_length Number;
v_body_length Number;
buffer Varchar2(32767);
offset Number := 1;
amount Number := 32767;
l_req_body Varchar2(4000);
l_resp_body Varchar2(32767);
l_req_clob Clob;
l_resp_clob Clob;
l_soap_respond Clob;
--接口地址
l_ws_url Varchar2(150);
l_resp_json json;
page_Json json;
data_json json;
page_list json_list;
l_totalSize Number;
record_jsonList json_list;
record_json json;
l_inv_info_rec CUX_AP_GT_INV_PIC_IFACE%Rowtype;
g_User_Id Number := Fnd_Global.User_Id;
g_Login_Id Number := Fnd_Global.Conc_Login_Id;


l_cnt Number := 0;
strLog Varchar2(2000); --单次输出的临时变量
loopCount Number(5);


Cursor csr_doc Is
Select statements xxx;


Begin
--获取服务地址
l_ws_url := 'http://10.1.50.21:8089/INS/com/bj/afx/wtocr/getInvoice/getBXInvMes';

For doc_rec In csr_doc Loop
l_cnt := l_cnt + 1;
Begin
dbms_lob.createtemporary(l_req_clob, True);
l_req_body := '{"pageNo":1,"pageSize":100,"bxdbh":"' ||
doc_rec.expense_number ||
'","cjrq_from":null,"cjrq_to":null}';
dbms_lob.writeappend(lob_loc => l_req_clob,
amount => length(l_req_body),
buffer => l_req_body);

v_body_length := dbms_lob.getlength(l_req_clob);
req := utl_http.begin_request(url => l_ws_url,
method => 'POST');

utl_http.set_body_charset('UTF-8');
utl_http.set_header(r => req,
Name => 'Content-Type',
Value => 'application/json;charset=utf-8');
utl_http.set_header(r => req,
Name => 'Content-Length',
Value => v_body_length);
--分段输出请求参数，这个header设置很重要
utl_http.set_header(req, 'Transfer-Encoding', 'chunked');
offset := 1;
buffer := Null;
amount := 32767;
--分段输出请求参数，避免内容超出长度限制
While (offset < v_body_length) Loop
dbms_lob.read(l_req_clob, amount, offset, buffer);
utl_http.write_raw(r => req, data => utl_raw.cast_to_raw(buffer));
offset := offset + amount;
End Loop;
dbms_lob.freetemporary(l_req_clob);
resp := utl_http.get_response(req);

--获取报文
If resp.status_code = '200' Then

buffer := Null;
dbms_lob.createtemporary(l_resp_clob, True);
Begin
Loop
utl_http.read_text(resp, buffer, Null);
dbms_lob.writeappend(l_resp_clob, length(buffer), buffer);
End Loop;
Exception
When utl_http.end_of_body Then
Null;
When Others Then
dbms_output.put_line('读取HTTP返回的数据' || Sqlerrm);
End;
Else
dbms_output.put_line(resp.status_code);
End If;

--关闭连接
utl_http.end_response(resp);

l_resp_json := json(l_resp_clob);
dbms_lob.freetemporary(l_resp_clob);
data_json := json_ext.get_json(l_resp_json, 'data');
page_json := json_ext.get_json(data_json, 'Page');
l_totalSize := json_ext.get_number(obj => page_json,
path => 'totalSize');
If nvl(l_totalSize, 0) > 0 Then
record_jsonList := json_ext.get_json_list(data_json, 'records');
For j In 1 .. record_jsonList.count Loop
record_json := json(record_jsonList.get(j));
l_inv_info_rec.inv_id := json_ext.get_string(record_json,
'cguid');
l_inv_info_rec.EXPENSE_NUMBER := json_ext.get_string(record_json,
'bxdbh');
l_inv_info_rec.DESCRIPTION := Null;
l_inv_info_rec.CREATION_DATE := Sysdate;
l_inv_info_rec.CREATED_BY := g_user_id;
l_inv_info_rec.LAST_UPDATED_BY := g_user_id;
l_inv_info_rec.LAST_UPDATE_DATE := Sysdate;
l_inv_info_rec.LAST_UPDATE_LOGIN := g_login_id;

Insert Into tableName Values l_inv_info_rec;
End Loop;
End If;If l_cnt > 50 Then
Commit;
l_cnt := 0;
End If;
Exception
When Others Then
dbms_output.put_line(doc_rec.expense_number);
utl_http.end_response(resp);

End;

End Loop;


End;

请求报文json样例

{
    "pageNo": 1,
    "pageSize": 500,
    "bxdbh": "2105290151",
    "cjrq_from": null,
    "cjrq_to": null
}

响应报文json样例

{
    "data": {
        "records": [
            {
                "cguid": "252255959741292294",
                "fpdm": "011002000911",
                "fphm": "54209129",
                "fplx": "10",
                "bxzt": "2",
                "states": "0",
                "wjmc": "yx_20210529182921932SSLuxODYrmB1e5a_1.pdf",
                "cflj": "10.0.50.35:21/OCR0/pdf&ofd/vat/2021/05/yx_20210529182921932SSLuxODYrmB1e5a_1.pdf",
                "bxdbh": "XCL_2105290151"
            },
            {
                "cguid": "252241048999044153",
                "fpdm": "",
                "fphm": "66812300040523K054841",
                "fplx": "20",
                "bxzt": "2",
                "states": "0",
                "wjmc": "yx_20210529180425026M6HKJc0vaDmr50G_1.jpg",
                "cflj": "10.0.50.35:21/OCR0/jpg&jpeg&png/train/2021/05/yx_20210529180425026M6HKJc0vaDmr50G_1.jpg",
                "bxdbh": "XCL_2105290151"
            },
            {
                "cguid": "252241048999044158",
                "fpdm": "",
                "fphm": "10010301100525D075476",
                "fplx": "20",
                "bxzt": "2",
                "states": "0",
                "wjmc": "yx_20210529180425026M6HKJc0vaDmr50G_2.jpg",
                "cflj": "10.0.50.35:21/OCR0/jpg&jpeg&png/train/2021/05/yx_20210529180425026M6HKJc0vaDmr50G_2.jpg",
                "bxdbh": "XCL_2105290151"
            }
        ],
        "Page": {
            "pageCount": 1,
            "totalSize": 3,
            "pageNo": 1,
            "pageSize": 500
        }
    },
    "error": 0,
    "success": true,
    "script": null
}