Invoke a REST API (JSON) in PLSQL

Question

I want to invoke a restful API (JSON) in plsql. The JSON Payload is as follows:

{
"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": ""
}
}

The Interface Codes (interfaceCode: T101) are defined, and for each method there is an interface Code, I just want to know how do i invoke a interface Method?

user_8551709 · Answer

You can do it with UTL_HTTP package.

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;

But if your database has got APEX installed, then you can try APEX_WEB_SERVICE package (much simpler).

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;

If you get an ACL exception, then you have to create ACL to open TCP port to connect with 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;

I assume your REST API is protected by basic authentication scheme (user and password). To keep this example simple i used HTTP. If you have to connect via HTTPS, then you have to change TCP port in ACL and configure Oracle Wallet for your Oracle database instance.

Invoke a REST API (JSON) in PLSQL

1 Answers