json - 在 PLSQL 中调用 REST API (JSON)

Question

我想在 plsql 中调用一个 restful API (JSON)。 JSON Payload 如下:

{
"data": {
"content": "encrypted content", "signature": "JKQWJK34K32JJEK2JQWJ5678",
"dataDescription": {
"codeType": "0",
"encryptCode": "1",
"zipCode": "0"
}
},
"globalInfo": {
"appId": "AP01",
"version": "1.1.20191201",
"dataExchangeId": "9230489223014123",
"interfaceCode": "T101",
"requestCode": "TP",
"requestTime": "2019-06-11 17:07:07",
"responseCode": "TA",
"userName": "admin",
"deviceMAC": "FFFFFFFFFFFF",
"deviceNo": "00022000634",
"tin": "1009830865",
5
"brn": "",
"taxpayerID": "1",
"longitude": "116.397128",
"latitude": "39.916527",
"extendField": {
"responseDateFormat": "dd/MM/yyyy",
"responseTimeFormat": "dd/MM/yyyy HH:mm:ss"
}
},
"returnStateInfo": {
"returnCode": "",
"returnMessage": ""
}
} 

定义了接口(interface)代码(interfaceCode:T101)，每个方法都有一个接口(interface)代码，我只想知道如何调用接口(interface)方法？

Answer 1

您可以使用 UTL_HTTP 包来完成。

declare
    v_req       utl_http.req;
    v_res       utl_http.resp;
    v_buffer    varchar2(4000); 
    v_body      varchar2(4000) := '{"field":"value"}'; -- Your JSON
begin
    -- Set connection.
    v_req := utl_http.begin_request('http://your.api/operation', 'POST');
    utl_http.set_authentication(v_req, 'your_username','your_password');
    utl_http.set_header(v_req, 'content-type', 'application/json'); 
    utl_http.set_header(v_req, 'Content-Length', length(v_body));
    
    -- Invoke REST API.
    utl_http.write_text(v_req, v_body);
  
    -- Get response.
    v_res := utl_http.get_response(v_req);
    begin
        loop
            utl_http.read_line(v_res, v_buffer);
            -- Do something with buffer.
            dbms_output.put_line(v_buffer);
        end loop;
        utl_http.end_response(v_res);
    exception
        when utl_http.end_of_body then
            utl_http.end_response(v_res);
    end;
end;

但是如果你的数据库已经安装了 APEX，那么你可以尝试 APEX_WEB_SERVICE 包(更简单)。

declare
    v_response      clob;
    v_buffer        varchar2(32767);
    v_buffer_size   number := 32000;
    v_offset        number := 1;
begin
    -- Set connection and invoke REST API.
    v_response := apex_web_service.make_rest_request(
        p_url           => 'http://your.api/operation',
        p_http_method   => 'POST',
        p_username      => 'your_username',
        p_password      => 'your_password',
        p_body          => '{"field":"value"}' -- Your JSON.
    );
    
    -- Get response.
    begin
        loop
            dbms_lob.read(v_response, v_buffer_size, v_offset, v_buffer);
            -- Do something with buffer.
            DBMS_OUTPUT.PUT_LINE(v_buffer);
            v_offset := v_offset + v_buffer_size;
        end loop;
    exception
        when no_data_found then
            null;
    end;
end;

如果您收到 ACL 异常，则必须创建 ACL 以打开 TCP 端口以连接 REST API。

BEGIN
    DBMS_NETWORK_ACL_ADMIN.create_acl (
        acl          => 'acl.xml',
        description  => 'Connecting with REST API',
        principal    => 'YOUR_DATABASE_SCHEMA',
        is_grant     => TRUE, 
        privilege    => 'connect',
        start_date   => SYSTIMESTAMP,
        end_date     => NULL
    );
    
    DBMS_NETWORK_ACL_ADMIN.assign_acl (
        acl         => 'acl.xml',
        host        => 'localhost', -- Or hostname of REST API server (e.g. "example.com").
        lower_port  => 80, -- For HTTPS put 443.
        upper_port  => NULL
    );
    
    COMMIT;
end; 

我假设您的 REST API 受到基本身份验证方案(用户和密码)的保护。为了让这个例子简单，我使用了 HTTP。如果您必须通过 HTTPS 连接，则必须更改 ACL 中的 TCP 端口并为您的 Oracle 数据库实例配置 Oracle Wallet。