Invoke a REST service from PL/SQL – make an HTTP POST request using UTL_HTTP in Oracle Database 11g XE

9

This article is small and simple. It discusses how from PL/SQL an HTTP POST request can be made to a REST service. This particular service is exposed at http://localhost:9002/cinema and it expects a POST call.

Making HTTP requests from PL/SQL is fairly simple, using the supplied package UTL_HTTP. Starting in Oracle Database 11g, some security constraints are in force around network interactions. This means that before from a specific database account a PL/SQL unit can make an HTTP call to a host and port, that account needs to be explicitly granted the privilege to do so. In this case, the user is called WC.

As DBA (for example user SYS) we have to execute the following statements to free the way for the WC account:

grant execute on utl_http to wc
grant execute on dbms_lock to wc

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'local_sx_acl_file.xml', 
    description  => 'A test of the ACL functionality',
    principal    => 'WC',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);
end;

begin
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'local_sx_acl_file.xml',
    host        => 'localhost', 
    lower_port  => 9002,
    upper_port  => NULL);    
end; 

Through these statements, we create an Access Control List called local_sx_acl_file.xml that is associated with database user WC. The privilege that is assigned per direct is connect. One of the specific hosts that the connect privilege applies to is configured in the last statement: localhost that may be accessed on ports 9002 and above.

After these preparations by the DBA allow the WC account to start making the call outs.

Connect as user WC and create the following PL/SQL procedure:

create or replace
procedure publish_cinema_event
( p_room_id in varchar2
, p_party_size in number
) is
  req utl_http.req;
  res utl_http.resp;
  url varchar2(4000) := 'http://localhost:9002/cinema';
  name varchar2(4000);
  buffer varchar2(4000); 
  content varchar2(4000) := '{"room":"'||p_room_id||'", "partySize":"'||p_party_Size||'"}';

begin
  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);
  res := utl_http.get_response(req);
  -- process the response from the HTTP call
  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;
end publish_cinema_event;

This procedures publishes a JSON body in an HTTP POST request to the REST service. The payload is composed using the two input parameters passed into the procedure.

Execute this anonymous PL/SQL statement to have the procedure sent three JSON payloads to the REST service.

begin 
  publish_cinema_event('2', -4);
  publish_cinema_event('1', 4);
  publish_cinema_event('3', -1);
end;

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

9 Comments

  1. Hello,

    How can I send HTTP requests in Oracle to a web service which uses “Form” method as its base.

  2. Invoke a restful webservice from pl/sql
    ——————————————
    Dear all,

    I am new to xml or web service related pl/sql’s

    I created a procedure as below

    create or replace procedure test_restfull_ws

    is

    req utl_http.req;

    res utl_http.resp;

    url varchar2(4000) := ‘https://mohdnc-hms.moh.gov.kw/MOHWS-1/license’;–

    name varchar2(4000);

    buffer varchar2(4000);

    content varchar2(4000) := ‘{“civilId”:”‘||’249080700202’||'”, “type”:”‘||’p’||'”}’;

    –civilId=249080700202 –type=p

    –‘{“room”:”‘||p_room_id||'”, “partySize”:”‘||p_party_Size||'”}’;

    begin

    dbms_output.put_line(content);

    –D:\app\DBADMIN\product\11.2.0\dbhome_1\BIN\owm\wallets\DBADMIN

    UTL_HTTP.SET_WALLET (‘file:D:\app\DBADMIN\product\11.2.0\dbhome_1\BIN\owm\wallets\DBADMIN’, ‘P22Jan2018_Wallet’);

    req := utl_http.begin_request(url, ‘GET’,’ HTTP/1.1′);

    –utl_http.set_header(req, ‘user-agent’, ‘mozilla/4.0’);

    UTL_HTTP.SET_AUTHENTICATION(req, ‘TEST’, ‘TEST333’);

    utl_http.set_header(req, ‘user-agent’, ‘license’);

    utl_http.set_header(req, ‘content-type’, ‘application/json’);

    utl_http.set_header(req, ‘Content-Length’, length(content));

    utl_http.write_text(req, content);

    res := utl_http.get_response(req);

    — process the response from the HTTP call

    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;

    end test_restfull_ws;

    while running I am getting the error as

    PL/SQL procedure successfully completed.

    {“civilId”:”249080700202″, “type”:”p”}

    Er:001Please provide valid Civil Id Please provide valid Civil Id

    Here it says that “Please provide valid Civil Id ”

    The same parameters are working from Restlet cleint

    Can anyone help?

    Dennis

  3. Hello. Need help with a Webservice. I consume a Webservice and need to include in the call to the webservice a file located in the server side. How can I use a single webservice call to add the file?
    Thanks.

  4. Hi,

    Thanks for the post, I am able to do it over http by setting the ACL. I want to make it works over HTTPS. When I tried it over https and did it from db server to an app server, even with certificate installed in db server using owm command line, I keep getting the following error:

    ORA-29024: Certificate validation failure

    Here is my sample code:
    Begin
    UTL_HTTP.set_wallet(‘file:/u01/app/oracle/admin/wallets/’, ‘WALLETPWDXXX’);
    select utl_http.request(‘https://someurl.com/’, null) into v from dual;
    End;
    /

    I can query the url from browser and it works but it hits Certificate error when I tried it in PL/SQL.

    Can you help?
    Sum

  5. hi
    sir i am a beginer in oracle and i have a task to call the web service
    for two number in which 2 number are a and b and the return is the addition of this
    pls help me to create a procedure to call the web service in json

    create or replace
    procedure av_websum
    ( x in number
    , y in number
    ) is
    req utl_http.req;
    res utl_http.resp;
    url varchar2(4000) := ‘http://localhost:9002/cinema’;
    name varchar2(4000);
    buffer varchar2(4000);
    content varchar2(4000) := ‘{“a”:”‘||x||'”, “b”:”‘||y||'”}’;

    begin
    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);
    res := utl_http.get_response(req);
    — process the response from the HTTP call
    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;
    end av_websum;
    is it write or wrong plz give suzzetion

  6. hi
    sir i am a beginer in oracle and i have a task to call the web service
    for two number in which 2 number are a and b and the return is the addition of this
    pls help me to create a procedure to call the web service in json

  7. I use this format for post an API and it worked correctly. but my API return a status. how can I have the value of this function.

    create or replace function send_pay(invoice number , filenumber varchar2 ,parvane varchar2,
    tot_price number,
    tax number,
    payable number,
    company_name varchar2,
    customer_name varchar2,
    stop number ) return char is
    — Result number;
    req utl_http.req;
    res utl_http.resp;
    url varchar2(4000) := ‘http://payments.tttt.org/api/v1/lifts’;
    name varchar2(4000);
    buffer varchar2(4000);
    content varchar2(4000) := ‘{
    “invoice_number”: “‘||invoice||'”,
    “file_number”: “‘||filenumber||'”,
    “building_permit_number”: “‘||parvane||'”,
    “total_price”: “‘||tot_price||'”,
    “value_added_tax”: “‘||tax||'”,
    “payable_price”: “‘||payable||'”,
    “installer_company_name”: “‘||email_fonts(company_name)||'”,
    “customer_name”: “‘||email_fonts(customer_name)||'”,
    “stop_number”: “‘||stop||'”,
    “token”:”CSzYfNepsVmAq7DHhZpyZtpbQCJ7zcRd8UJ6ShAywlI=”
    }’;

    begin
    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);
    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;

    return ?????;
    end ;

    what should I return to find the result of this function????

  8. Hello,
    Thanks for sharing,
    I am getting a stragne error when trying to make request to any URL.
    The error is:
    ORA-20987: APEX – The requested URL has been prohibited. Contact your administrator. – Contact your application administrator.

    However, the error on Apex.oracle.com is:
    ORA-20987: APEX – Unauthorized URL: http://jsonplaceholder.typicode.com/posts – Contact your application administrator.
    …..
    This is the sample code I am trying :

    declare
    l_clob CLOB;
    BEGIN

    l_clob := apex_web_service.make_rest_request(
    p_url => ‘http://jsonplaceholder.typicode.com/posts’,
    p_http_method => ‘POST’
    );

    END;

    — The below works fine
    select utl_http.request (‘http://jsonplaceholder.typicode.com/posts’,null) from dual

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.