How to invoke a REST web service, with POST method sending the parameters as json in a query string in plsql?

Question

I am trying to invoke a REST web service from plsql, making use of POST method, and trying to send the parameters as json in a query string. Unfortunately, I am failing probably due to the fact that I am not making proper use of utl_http (plsql novice here).

I am afraid that I am not sending properly the request and that I am not submitting the json in the query string.

Actually the provider of the web service expects something like this: BASE URL/postPayment/jsonstring, whereas I am afraid I am not doing that exact thing.

Actually I receive a HTTP 405 error: "The specified HTTP method is not allowed for the requested resource ()."

I have tried to search around, what would be the best way to send the json (initially I tried with APEX_WEB_SERVICE.MAKE_REST_REQUEST but the result returned was empty) but it seems that I cannot find further guidance on my situation.

Can someone please orient me on what way should I proceed, either by indicating the proper usage form of uttl_http in my case, or by pointing out my error below?

create or replace procedure another_way

( 
customerNo        IN   VARCHAR2,  
invoiceNo        IN   VARCHAR2,
agreementNo        IN   VARCHAR2,
instance        IN   VARCHAR2,
paymentRefId        IN   VARCHAR2,
paymentDate        IN   VARCHAR2,
principalAmt        IN   VARCHAR2,
interest        IN   VARCHAR2,
totalAmt        IN   VARCHAR2,
passCode        IN   VARCHAR2,
invoiceType        IN   VARCHAR2,
phoneNo        IN   VARCHAR2

) is

req utl_http.req;
res utl_http.resp;
url varchar2(4000) := 'http://baseurl:7474/mpower/rest/postPayment/';
name varchar2(4000);
buffer varchar2(4000); 
content varchar2(4000) := '{"customerNo":"'||customerNo||'","invoiceNo":"'||invoiceNo||'","agreementNo":"","instance":"'||instance||'","paymentRefId":"'||paymentRefId||'","paymentDate":"'||paymentDate||'","principalAmt":'||principalAmt||',"interest":'||interest||',"totalAmt":'||totalAmt||',"passCode":"'||passCode||'","invoiceType":"'||invoiceType||'","phoneNo":"'||phoneNo||'"}';

begin

   /* just to indicate it started */
    dbms_output.put_line('START'); 

    /* to check if your JSON content is okay and has correct values */
    dbms_output.put_line(content); 

dbms_output.put_line(url||content);


req := utl_http.begin_request(url||content, 'POST',' HTTP/1.1');
utl_http.set_authentication( req, 'abi','oshee', 'Basic' );
--utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); 
--utl_http.set_header(req, 'content-type', 'application/json'); 
--utl_http.set_header(req, 'Content-Length', length(content));




/* not sure if this will work but try to print the utl_http */


res := utl_http.get_response(req);

    begin
    loop
    utl_http.read_line(res, buffer);
    dbms_output.put_line(buffer);

    end loop;
    utl_http.end_response(res);
    exception
    when utl_http.end_of_body then
    utl_http.end_response(res);
    end;

 /* just to indicate it ended */
    dbms_output.put_line('END');
exception
    when utl_http.end_of_body 
    then
        utl_http.end_response(res);

        /* just to indicate it went to exception part */
    DBMS_OUTPUT.put_line (SQLERRM); 
    dbms_output.put_line('EXCEPTION');
    end;

--end another_way;

Answer 1

Basically, invoking a REST web service making use of POST method, by passing a json parameter in the query string via plsql can be performed successfully via APEX_WEB_SERVICE.MAKE_REST_REQUEST, but url encoding should be kept in mind since json is built with reserved characters. UTL_URL.ESCAPE package/function can be utilized. Example for my case below:

L_BASE_URL VARCHAR2 (4000) :='http://baseurl:7474/mpower/rest/postPayment/' ; PAR := '{"customerNo":"'||customerNo||'","invoiceNo":"'||invoiceNo||'","agreementNo":"","instance":"'||instanceNo||'","paymentRefId":"'||paymentRefId||'","paymentDate":"'||paymentDate||'","principalAmt":'||principalAmt||',"interest":'||interest||',"totalAmt":'||totalAmt||',"passCode":"'||passCode||'","invoiceType":"'||invoiceType||'","phoneNo":"'||phoneNo||'"}'; l_base_url := l_base_url ||URL_ENCODE(PAR,'ASCII'); l_result := APEX_WEB_SERVICE.make_rest_request( p_url => l_base_url, p_http_method => 'POST', p_username => 'username', p_password => 'password' ); where URL_ENCODE function is as follows:

create or replace FUNCTION url_encode ( data IN VARCHAR2, charset IN VARCHAR2) RETURN VARCHAR2 AS BEGIN RETURN utl_url.escape(data, TRUE, charset); -- note use of TRUE END;

Answer 2

I haven't had tried this before in PLSQL, but what I can provide to you are the following:

(1) First, test that the URL you are using if it really can capture your POST request and request body. In order to do so, you can make use of POSTMAN (its an app I use to test out APIs - in your case this url "http://10.211.47.98:7474/rest/postPayment/"). It's free. You can also use it as is without signing in.

In POSTMAN, you just need to use "POST" in the dropdown at the left side of the url, then on the url field provide your url, then on the body provide your JSON content. (And Authorization header if required by the endpoint)

Below is the url of postman and sample screenshot from the site: https://www.getpostman.com/downloads/

(2) In your code, you can also add in additional DBMS_OUT.put_line() codes so you could verify upon run-time what is happening when you trigger your code.

SAMPLE (i just added some DBMS_OUT lines and comments in your code):

/*<"create or replace", name and other variables used by your code here>*/
req utl_http.req;
res utl_http.resp;
url varchar2(4000) := 'http://10.211.47.98:7474/rest/postPayment/';
name varchar2(4000);
buffer varchar2(4000); 
content varchar2(4000) := '{"customerNo":"' || customerNo || '","invoiceNo":"' || invoiceNo || '","agreementNo":"","instance":"' || instance || '","paymentRefId":"' ||paymentRefId || '","paymentDate":"' || paymentDate || '","principalAmt":' || principalAmt || ',"interest":' || interest|| ',"totalAmt":' || totalAmt || ',"passCode":"' || passCode || '","invoiceType":"' || invoiceType || '","phoneNo":"' || phoneNo || '"}';

begin
    /* just to indicate it started */
    dbms_output.put_line("START"); 

    /* to check if your JSON content is okay and has correct values */
    dbms_output.put_line(content); 

    req := utl_http.begin_request(url, 'POST',' HTTP/1.1');
    utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); 
    utl_http.set_header(req, 'content-type', 'application/json'); 
    utl_http.set_header(req, 'Content-Length', length(content));

    utl_http.write_text(req, content);

    /* not sure if this will work but try to print the utl_http */
    dbms_output.put_line(utl_http);

    res := utl_http.get_response(req);

    begin
    loop
        utl_http.read_line(res, buffer);
        dbms_output.put_line(buffer);
    end loop;

    utl_http.end_response(res);

    /* just to indicate it ended */
    dbms_output.put_line("END");
exception
    when utl_http.end_of_body 
    then
        utl_http.end_response(res);

        /* just to indicate it went to exception part */
    dbms_output.put_line("EXCEPTION");
    end;
end /*<name of package/procedure>*/;

(3) You can also refer to this document for additional technical details on oracle's utl_http. https://docs.oracle.com/database/121/ARPLS/u_http.htm#ARPLS70957

Hope this helps.

If you encounter any other problems let us know here so we can share additional details.