Monday, February 4, 2013

Invoking RESTful web service in Oracle PL/SQL

UTL_HTTP package allows to invoke web services via pure Oracle PL/SQL. Here the invoking of RESTful web service will be shown. Obviously, server where Oracle is deployed must have an access to host with web service.

To perform of the invoking the following steps should be done

  • form Request object, namely
               set web service's URL
               set request's header attributes
               set request's input parameters
  • get Response object as answer on sent request
  • obtain expected resource from Response object
  • make some final action with Request and Response objects

After short googling I found this currency converter, also implemented as RESTful web service. There all needed request header's attributes and input parameters are described.

As answer the web service returns a piece of XML code where value of double tag is cross rate between input currencies.

PL/SQL code to invoke this currency converter web service
DECLARE
  l_param_list     VARCHAR2(512);
 
  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;
 
  l_response_text  VARCHAR2(32767);
BEGIN
 
  -- service's input parameters
  l_param_list := 'FromCurrency=EUR&ToCurrency=USD';
 
  -- preparing Request...
  l_http_request := UTL_HTTP.begin_request ('http://www.webservicex.net/currencyconvertor.asmx/ConversionRate'
                                          , 'POST'
                                          , 'HTTP/1.1');
 
  -- ...set header's attributes
  UTL_HTTP.set_header(l_http_request, 'Content-Type', 'application/x-www-form-urlencoded');
  UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list));
 
  -- ...set input parameters
  UTL_HTTP.write_text(l_http_request, l_param_list);
 
  -- get Response and obtain received value
  l_http_response := UTL_HTTP.get_response(l_http_request);
 
  UTL_HTTP.read_text(l_http_response, l_response_text);
 
  DBMS_OUTPUT.put_line(l_response_text);
 
  -- finalizing
  UTL_HTTP.end_response(l_http_response);
 
EXCEPTION
  WHEN UTL_HTTP.end_of_body 
    THEN UTL_HTTP.end_response(l_http_response);  
END;

The result
<double xmlns="http://www.webserviceX.NET/">1.3647</double>

Troubles you may face with

Denial by Network Access Control List

In Oracle 11g DBMS_NETWORK_ACL_ADMIN package has been provided. It's used to administer an access to network via Access Control List (ACL).

So if you use Oracle 11g the code above raises ORA-24247: network access denied by access control list (ACL) exception.

To work it out the following script should be run (if user you have logged on has EXECUTE privilege on DBMS_NETWORK_ACL_ADMIN package)
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (acl         => 'ws_hosts.xml'
                                   , description => 'ACL for web services hosts'
                                   , principal   => 'ADMIN'
                                   , is_grant    => TRUE
                                   , privilege   => 'connect');
 
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (acl       => 'ws_hosts.xml'
                                      , principal => 'ADMIN'
                                      , is_grant  => TRUE
                                      , privilege => 'resolve');
 
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'ws_hosts.xml',
                                    host => 'www.webservicex.net');
 
  COMMIT;
END;

Some comments concerning the script. create_acl procedure creates an ACL in XML format and set connect privilege to user or role (principal) for URLs will be added to this ACL. add_privilege procedure grant one more privilege - resolve. This privelege allows to resolve a host name by IP address and vise versa. And finally assign_acl procedure adds URI (in our case - www.webservicex.net) into created ACL.

Proxy server

In case when http requests are sent through proxy server it has to be set in UTL_HTTP.SET_PROXY procedure. If authentication is required it has to be set in the same procedure.

The whole format of proxy parameter of set_proxy procedure is
<username>:<password>@<proxy-server>:<port>

Proxy should be set before all other code. In the example above possible change is
BEGIN
  UTL_HTTP.set_proxy('http://jsmith:secret@10.2.36.17:3388');
  ...

When necessary proxy server is not set ORA-12535: operation timed out exception will be raised.

When incorrect username or password is set in the http response the error text like this will be shown - "Error Code: 407 Proxy Authentication Required"

Also if Oracle 11g is used don't forget to add proxy server's host name or IP address into ACL (using DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL).

For example
BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'ws_hosts.xml',
                                    host => '10.2.36.17');
END;

Code snippets have been tested on Oracle Database 11g Release 11.2.0.1.0

30 comments:

  1. Probably a really dumb question but, if the POST call is asynchronous, how does the PL/SQL block handle the response?

    For example:

    1. Call a Web Service / REST api for starting some process
    2. Kickoff a background thread to run the requested process
    3. Return from HTTP POST request

    Will the PL/SQL block above simply wait until the return from post? (This is what happened with GET)

    -Joe

    ReplyDelete
    Replies
    1. PL/SQL block waits a response on UTL_HTTP.read_text function. You should set timeout (UTL_HTTP.set_transfer_timeout procedure) to avoid the block will hung up.

      From here (http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_http.htm#i1027020)
      "If transfer timeout is set in the request of this response, read_text waits for each data packet to be ready to read until timeout occurs. If it occurs, this procedure stops reading and returns all the data read successfully. If no data is read successfully, the transfer_timeout exception is raised. The exception can be handled and the read operation can be retried later."

      Delete
    2. hi when I checked it is asking for subtitution variable ToCurrency

      Delete
  2. Hi,

    Thanks for the example.
    If the webservice accepts a single form-field argument which is XML how do I pass it?

    Appreciate your help.

    Thanks,
    Sri

    ReplyDelete
    Replies
    1. Hi,

      It seems that you mixed up with name of parameter or letters' case.
      To illustrate this I have created RESTful web service which processes POST request with one string parameter, named 'P', and returns value of this parameter without any changes.

      Here Java code of this handler:
      @POST
      @Consumes({MediaType.TEXT_XML})
      public String postHandler(@FormParam("P") String param) {
      return param;
      }

      Then I copied XML you have provided here (https://forums.oracle.com/thread/2550044) or here (https://forums.oracle.com/message/11062412?tstart=0) and put them as P parameter's value. This is what I changed in the example from my post:
      ...
      -- service's input parameters
      l_param_list := 'P=your xml here';

      -- preparing Request...
      l_http_request := UTL_HTTP.begin_request ('http://localhost:8080/RestWebService/rest/hello'
      , 'POST'
      , 'HTTP/1.1');

      -- ...set header's attributes
      UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml; charset=UTF-8');
      UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list));
      ...

      And all works fine.
      When I changed name of parameter to 'p' (in lower case)
      ... l_param_list := 'p=your xml here ...
      any error was not raised, but the response returned nothing. This proves that form parameter's name is case sensitive.

      So, as suggestion, check up carefully name of parameter you use.

      Delete
  3. thanks a lot from Chile
    i think this is the answer that i needed

    kind regards

    ReplyDelete
    Replies
    1. I have a webservice that upload a local binary file (for example a jpg picture) and insert into a Oracle database

      my test url http://localhost/UploadService/upload?token={p_token}&filename={p_filename}

      Say I have a picture in C:\Users\Peter\Pictures\test_image.jpg -> http://localhost/UploadService/upload?token=5&filename=test_image.jpg


      How can I upload this picture using the utl_http procedure. I have seen examples to download a web picture using utl_http and examples for uploading xml/clob using an url but not a specific example uploading a blob through a webservice..


      It would be helpful if there is an example how to upload the image file using my uploadservice. I have made a C# example that works but want to make it work also using utl_http....

      database: 10gr2



      Thanks in advance,



      Regards

      Henk

      Delete
    2. To illustrate an ability of uploading a binary file via RESTful web service using Oracle UTL_HTTP package:
      1) I created table X with one BLOB column IMG
      2) insert one row with an image
      3) create RESTful web service using Jersey (https://jersey.java.net/?)
      The method that receives a binary file (an image in our case) and save it on local disk
      @POST
      @Path("/upload")
      @Consumes(MediaType.APPLICATION_OCTET_STREAM)
      public Response uploadFile(InputStream fileInputStream) {
      // create file (save on disk)
      try {
      OutputStream outpuStream = new FileOutputStream(new File("D://TEMP/tmp.jpg"));
      int read = 0;
      byte[] bytes = new byte[1024];

      while ((read = fileInputStream.read(bytes)) != -1) {
      outpuStream.write(bytes, 0, read);
      }

      outpuStream.flush();
      outpuStream.close();
      } catch (IOException e) {
      e.printStackTrace();
      }

      // return a message in response
      return Response.status(200).entity("File successfully saved").build();
      }

      And PL/SQL code for upload an image from X table to created web service
      DECLARE

      l_img blob;
      l_img_length pls_integer;

      l_http_request UTL_HTTP.req;
      l_http_response UTL_HTTP.resp;

      l_buffer raw(32767);
      l_amount integer := 32767;
      l_position integer := 1;

      l_response_text varchar2(32767);

      BEGIN

      -- obtain an image from one-row dummy table
      select img
      into l_img
      from x;

      -- preparing Request...
      l_http_request := UTL_HTTP.begin_request ('http://localhost:8080/MyRestWebService/upload'
      , 'POST'
      , UTL_HTTP.http_version_1_1);

      -- ...set header's attributes
      UTL_HTTP.set_header(l_http_request, 'Content-Type', 'application/octet-stream');
      l_img_length := DBMS_LOB.getlength(l_img);
      UTL_HTTP.set_header(l_http_request, 'Content-Length', l_img_length);

      -- ...write the image into Request
      while (l_position < l_img_length)
      loop
      DBMS_LOB.read(l_img, l_amount, l_position, l_buffer);
      UTL_HTTP.write_raw(l_http_request, l_buffer);
      l_position := l_position + l_amount;
      end loop;

      -- get Response and obtain received value
      l_http_response := UTL_HTTP.get_response(l_http_request);

      UTL_HTTP.read_text(l_http_response, l_response_text);

      DBMS_OUTPUT.put_line(l_response_text);

      -- finalizing
      UTL_HTTP.end_response(l_http_response);

      EXCEPTION
      WHEN UTL_HTTP.end_of_body
      THEN UTL_HTTP.end_request(l_http_request);
      END;

      Hope it will help you.

      Delete
  4. Hi Alexis , one help please ie

    when we use the UTL_HTTP.set_transfer_timeout (180);

    how to handle the situation after timeout , for example the ws did not respond for more than 180 sec.
    Then we should handle that.
    in my requirement.

    we should get the timeout error; and insert it into a sepearte logging table the error message.

    can you please help me on this

    ReplyDelete
    Replies
    1. Hi there!

      Assume you should handle "ORA-29276: transfer timeout".

      Delete
  5. Hi Alexey, Thanks for your reply .
    well is there any way can we code like
    exception
    when others
    l_err = 'Error occured '||sqlerrm;
    end;

    if we do like this can we handle the error ?

    could you please give me a sample code for a requirment like " create a request xml :call a web service: get response xml. when timeout happnes insert the error mesage , time in a log table like that ?

    ReplyDelete
  6. Question.. I am trying to consume a restful web service that will provide a pdf to be downloaded, issue is I am abl eto get the file down using APEX's apex_web_service.make_rest_request, but when I convert returned clob to a blob the pdf document is mangled...

    Any suggestions?

    Thank you,

    Tony Miller
    LuvMuffin Software
    Ruckersville, VA

    ReplyDelete
    Replies
    1. Using apex 4.2.3 and the web service uses a post to send a json object in body..

      Thank you,

      Tony Miller
      LuvMuffin Software
      Ruckersville, VA

      Delete
  7. Tony, thanks for your question.
    But I have never dealt with APEX and can not help you.

    ReplyDelete
  8. Alex,
    I want to invoke a API that accepts XML in the Body. How do I POST with XML in the body using UTL_HTTP.
    I keep getting Http status 400. And the target system says they received the http call, but with no body.I even hard coded the xml payload into a varchar variable and posted but no luck. What are the techniques to debug http calls made from UTL_HTTP. How do i see what is being transmitted from Oracle Database?
    -- Begin HTTP Request
    l_http_req := UTL_HTTP.BEGIN_REQUEST (url => lv_http_url,
    method => 'POST',
    http_version => UTL_HTTP.http_version_1_1);
    UTL_HTTP.set_header (r => l_http_req,
    name => 'Content-Type',
    VALUE => 'application/xml');
    UTL_HTTP.set_header (r => l_http_req,
    name => 'Accept',
    VALUE => 'application/xml');
    UTL_HTTP.set_authentication (r => l_http_req,
    username => lv_http_username,
    password => lv_http_password ,
    scheme => 'Basic',
    for_proxy => FALSE
    );
    UTL_HTTP.WRITE_TEXT (r => l_http_req, data => result);
    l_http_resp :=
    UTL_HTTP.get_response(l_http_req);

    It doesn't throw any error till here but the status code returned is 400.

    Appreciate your inputs on this issue.

    ReplyDelete
    Replies
    1. Hi,

      Maybe it will be helpful https://community.oracle.com/thread/3617712
      The idea to put chr(13) || chr(10) as new line symol.

      Delete
  9. Hello All, The REST API services we are trying to invoke are OAM SSO enabled services and we are not sure what header parameters we need to use and set in the pl/sql process using UTL_HTTP API. Every time we invoke the service we get html of of SSO login page; which is an incorrect response. We are not able to pass through the authentication step. We are not sure what the client HTTP POST should contain for OAM SSO authentication. Please help.

    ReplyDelete
    Replies
    1. Any suggestions on this?

      Please.

      Delete
    2. Any suggestions on this?

      Please.

      Delete
    3. I am also having this problem. Anyone know a solution?

      Delete
  10. worked perfectly.. Thanks a lot

    ReplyDelete
  11. Hi..

    How do i ads email_id as parameters and pass to SOAP to send Email Notifications.

    ReplyDelete
  12. Hi Alex,

    It is very helpful post.

    I have a question, If have a webservice which returns a binary data stream which in turn I need to convert into a PDF file, how can I achieve this using PL/SQL (like UTL_HTTP)?


    Regards,
    Pradeep

    ReplyDelete
    Replies
    1. Hi Pradeep, do you have the solution now? I have the same question.

      Delete
  13. Thank you for the article, it worked for me!

    ReplyDelete
  14. Hello,

    I am using below code and i get below response (bad request).Is there a way i can print request and check
    declare
    req utl_http.req;
    res utl_http.resp;
    errmsg varchar2(4000);
    url varchar2(4000):='http://129.156.113.192:2100/bcsgw/rest/v1/transaction/invocation';
    buffer varchar2(4000);
    content varchar2(4000) := '{"channel": "kpipmchannel","chaincode": "POChaincode2", "chaincodeVer": "v1", "method": "updatePO","args":["PO_29052018","12"]}';

    begin
    dbms_output.put_line('calling begin request');
    UTL_HTTP.set_transfer_timeout(180);
    utl_http.set_proxy('dmz-proxy.oracleads.com:80');
    req := utl_http.begin_request(url,'POST','HTTP/1.1');
    errmsg:=utl_http.get_detailed_sqlerrm;
    UTL_HTTP.set_header(req, 'User-Agent', 'Mozilla/4.0');
    UTL_HTTP.set_header(req, 'Connection', 'close');
    utl_http.set_header(req, 'Content-Type', 'application/json');
    utl_http.set_header(req, 'Content-Length', 1112);
    utl_http.write_text(req, 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;
    commit;
    end;
    /

    Response:
    calling begin request
    Bad Request
    The request could not be understood by the server.

    ReplyDelete
  15. I think both SQL and REST API are useful in establishing a strong database connection.This in turn also provides for solution of some very complex IT problems.

    SQL Server Load Soap Api

    ReplyDelete
  16. I am trying to make a REST POST API call from pl/sql stored procedure. I am getting 200 response as well as ORA-29276: transfer timeout" Can someone please help me on this

    ReplyDelete