Thanks for the question, Anisa.

Asked: November 13, 2019 - 11:34 am UTC

Last updated: November 27, 2019 - 11:04 am UTC

Version: Oracle Database 18c Express Edition

Viewed 1000+ times

You Asked

Hello,

I have a case when I should consume a REST web service, by sending json as parameter,
in the query string (url), something like this: http:// <BaseUrl>/postPayment/{"customerNo":"KU0H150001110191","invoiceNo":"","agreementNo":"
LINSTAGR208838$44","instance":"KUKES","paymentRefId":"106239205","paymentDate":"19-10-2018 08:37:22","principalAmt":1000.00,"interest":0.00,"totalAmt":1000.00,"passCode":"Besnik Xhelaj","invoiceType":"A","phoneNo":""}

I tried at first using APEX_WEB_SERVICE.make_rest_request but I am unable to receive reply:

create or replace FUNCTION FN_REST_POSTPMNT 
(
customerNo        IN   VARCHAR2,  
invoiceNo        IN   VARCHAR2,
agreementNo        IN   VARCHAR2,
instanceNo        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
) 
    RETURN VARCHAR2 AS 
    L_RESULT  CLOB ;
    L_BASE_URL  VARCHAR2 (4000) :='http://IP:7474/mpower/rest/postPayment/' ;

BEGIN
--dbms_output.put_line('{"customerNo":"'||customerNo||'","invoiceNo":"'||invoiceNo||'","agreementNo":"","instance":"'||instance||'","paymentRefId":"'||paymentRefId||'","paymentDate":"'||paymentDate||'","principalAmt":'||principalAmt||',"interest":'||interest||',"totalAmt":'||totalAmt||',"passCode":"'||passCode||'","invoiceType":"'||invoiceType||'","phoneNo":"'||phoneNo||'"}');
--l_base_url  := l_base_url||'{"customerNo":"'||customerNo||'","invoiceNo":"'||invoiceNo||'","agreementNo":"",
"instance":"'||instance||'","paymentRefId":"'||paymentRefId||'","paymentDate":"'||paymentDate||'",
"principalAmt":'||principalAmt||',"interest":'||interest||',"totalAmt":'||totalAmt||',"passCode":"
'||passCode||'","invoiceType":"'||invoiceType||'","phoneNo":"'||phoneNo||'"}';
 
--dbms_output.put_line(l_base_url);
--apex_web_service.g_request_headers.delete();
l_base_url := l_base_url ||'%7B%22customerNo%22%3A%22'||customerNo||'%22%2C%22invoiceNo%22%3A%22'||invoiceNo||
'%22%2C%22agreementNo%22%3A%22%22%2C%22instance%22%3A%22'||instanceNo||
'%22%2C%22paymentRefId%22%3A%22'||paymentRefId||'%22%2C%22paymentDate%22%3A%22'||paymentDate||
'%22%2C%22principalAmt%22%3A'||principalAmt||'%2C%22interest%22%3A'||interest||
'%2C%22totalAmt%22%3A'||totalAmt||
'%2C%22passCode%22%3A%22ABI%22%2C%22invoiceType%22%3A%22F%22%2C%22phoneNo%22%3A%22%22%7D';
dbms_output.put_line(l_base_url);

    l_result      := APEX_WEB_SERVICE.make_rest_request(
    p_url         => l_base_url,
    p_http_method => 'POST',
    p_username    => 'user',
    p_password    => 'password'--,
  --  p_parm_name   =>  APEX_UTIL.string_to_table('customerNo:invoiceNo:agreementNo:instance:paymentRefId:paymentDate:
principalAmt:interest:totalAmt:passCode:invoiceType:phoneNo'),
   -- p_parm_value  =>   APEX_UTIL.string_to_table(customerNo|| ':' ||invoiceNo|| ':' ||agreementNo|| ':' ||instance|| ':' ||paymentRefId|| ':' ||paymentDate|| ':' ||principalAmt||
 ':' ||interest|| ':' ||totalAmt|| ':' ||passCode|| ':' ||invoiceType|| ':' ||phoneNo)
  );
 

--APEX_JSON.parse(l_result);
dbms_output.put_line('result:'||l_result); 

RETURN l_result;
END FN_REST_POSTPMNT;

Actually I am pretty novice in the area, so after some searching around it got me thinking I should make use of utl_http instead.
Below the procedure making use of utl_http:

create or replace procedure another_way

( 
customerNo        IN   VARCHAR2,  
invoiceNo        IN   VARCHAR2,
agreementNo        IN   VARCHAR2,
instanceNo        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://IP:7474/mpower/rest/postPayment/';
name varchar2(4000);
buffer varchar2(4000); 
content varchar2(5000) := '%7B%22customerNo%22%3A%22'||customerNo||'%22%2C%22invoiceNo%22%3A%22'||invoiceNo||
'%22%2C%22agreementNo%22%3A%22%22%2C%22instance%22%3A%22'||instanceNo||
'%22%2C%22paymentRefId%22%3A%22'||paymentRefId||'%22%2C%22paymentDate%22%3A%22'||
paymentDate||'%22%2C%22principalAmt%22%3A'||principalAmt||'%2C%22interest%22%3A'||interest||
'%2C%22totalAmt%22%3A'||totalAmt||'%2C%22passCode%22%3A%22ABI%22%2C%22invoiceType
%22%3A%22F%22%2C%22phoneNo%22%3A%22%22%7D' ;
 
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, 'user','password', 'Basic' );
utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); 
utl_http.set_header(req, 'content-type', 'application/x-www-form-urlencoded'); 
utl_http.set_header(req, 'Content-Length', length(content));
 


res := utl_http.get_response(req);

    begin
    loop
    
    utl_http.read_line(res, buffer, TRUE);
    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;

I can understand the execution takes place normally (if outputed SQLERRM the message is
ORA-0000: normal, successful completion) but I am unable to output or receive any result.
I only get this:
START
http://10.214.1.213:7474/mpower/rest/postPayment/%7B%22customerNo%22%3A%22TR3V160273630450%22%2C
%22invoiceNo%22%3A%22253144754%22%2C%22agreementNo%22%3A%22%22%2C%22instance%22%3A%22TIRANA3%22%2C
%22paymentRefId%22%3A%22test1234%22%2C%22paymentDate%22%3A%2206-11-2019 12:10:00%22%2C%22principalAmt%22%3A340.00%2C%22interest%22%3A120.00%2C%22totalAmt%22%3A460.00%2C
%22passCode%22%3A%22ABI%22%2C%22invoiceType%22%3A%22F%22%2C%22phoneNo%22%3A%22%22%7D
END
ORA-0000: normal, successful completion
Process exited.

I tried the generated url and json generated by making use of POSTMAN, and the POST request
toward http://IP:7474/mpower/rest/postPayment/%7B%22customerNo%22%3A%22TR3V160273630450%22%2C%22invoiceNo
%22%3A%22253144754%22%2C%22agreementNo%22%3A%22%22%2C%22instance%22%3A%22TIRANA3%22%2C%22
paymentRefId%22%3A%22test1234%22%2C%22paymentDate%22%3A%2206-11-2019 12:10:00%22%2C%22principalAmt%22%3A340.00%2C%22interest%22%3A120.00%2C%22totalAmt%22%3A460.00%2C
%22passCode%22%3A%22ABI%22%2C%22invoiceType%22%3A%22F%22%2C%22phoneNo%22%3A%22%22%7D

works ok, a json response is returned in the body, concretely for this data :

{"status":"F","prno":"","statusCode":"07","statusText":
"Payments will not be accepted with back payment date"}

Please for your help.
I am a little bit worried that I am not submitting properly the json in the query string.
Or any kind of other suggestion is welcome... some elephant in the room maybe which might be due to my inexperience :) ?

Thanks in advance,

Anisa

and Connor said...

I don't think you need to encode anything - just passing the JSON and setting the request headers should be enough, ie, something along the lines of:

declare
  l_user   constant varchar2(100) := 'xxx';
  l_pass   constant varchar2(100) := 'xxx';
  l_host        constant varchar2(200) := 'https://xxx';
  l_host_path constant varchar2(200) := l_host || '/path/path/path';

  l_clob           clob;
  l_body clob;

begin

  l_body := '{"customerNo":"KU0H150001110191","invoiceNo":"","agreementNo":....}';

  apex_web_service.g_request_headers(1).name := 'Content-Type';
  apex_web_service.g_request_headers(1).value := 'application/json'; 

  l_clob := apex_web_service.make_rest_request(
              p_url         => l_host_path,
              p_username    => l_user,
              p_password    => l_pass,
              p_http_method => 'POST',
              p_body        => l_body);

end
/

if this doesn't work, I think you'll need to jump onto the APEX forum to get their input

https://community.oracle.com/community/groundbreakers/database/developer-tools/application_express/content

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

APEX

Consuming REST web service, PUT method HTTP, by sending json as query string

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

APEX