Oracle · Web API

Calling Web API REST based Services from Oracle

chiragbogra

A distributed system has many networked components, and each of them interact by passing messages to each other. In my current project implementation, one such problem i had to solve was passing few xml messages from an Oracle database hosted on one part of the globe to a Web API  (.NET) based REST service hosted on another part of a globe. Sounds Interesting, Isn’t It !!!! Here is the solution.

  • We will make use of utl_http package. Ensure the schema has the package installed and has access.
  • Ensure you provide ACL access from Oracle database server to the server where the services are hosted. Configuring Access Control Lists (ACLs)
  • Create a stored procedure wherein, we pass the xml or varchar2(or any other datatype as per the requirements), and the message is passed to the REST Service as a POST request.
  • Specify the URL of the hosted service and pass it as a parameter to utl_http.begin_request.

utl_http.begin_request( 'http://10.20.22.55:86/v1/TestService', 'POST', 'HTTP/1.1');
  • Use set_header to provide the header information to the request. Here i am assigning the Content-Type & Content-Length

utl_http.set_header(t_http_req, 'Content-Type', 'text/plain');

utl_http.set_header(t_http_req, 'Content-Length', length(t_request_body));
  • Use the write_text method to call the REST service. If you are expecting a response from the service, read it using get_response
  • Close the connection stream using end_response.

Here is the code snippet that you can use :


create or replace PROCEDURE "CALL_TEST_SERVICE" (t_request_body varchar2)
as
t_http_req utl_http.req;
t_http_resp utl_http.resp;
begin

t_http_req:= utl_http.begin_request( 'http://10.20.22.55:86/v1/TestService'
, 'POST'
, 'HTTP/1.1');

utl_http.set_header(t_http_req, 'Content-Type', 'text/plain');

utl_http.set_header(t_http_req, 'Content-Length', length(t_request_body));

utl_http.write_text(t_http_req, t_request_body);

t_http_resp:= utl_http.get_response(t_http_req);

utl_http.end_response(t_http_resp);
end;

I am migrating to Blogger for all my new blogs. You may visit https://helpmedevelop.blogspot.in/ for more insights.

 

Leave a comment