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

Lucas Jellema May 11, 2015 Database 9 Comments

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;

Tags:Oracle, plsql, post, REST, xe

About The Author

Lucas Jellema

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

Rahul August 7, 2018

Hello,

How can I send HTTP requests in Oracle to a web service which uses “Form” method as its base.
Dennis John February 15, 2018

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
Rags January 25, 2018

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.
Sum November 22, 2017

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
ashok verma September 9, 2017

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
ashok verma September 9, 2017

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
salman October 3, 2016

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????
Mohmmad Msadi April 24, 2016

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
- Jibon November 7, 2017
  
  bhi, is it work in apex process.@MOHMMAD MSADI

Share this:

Like this:

Related Posts

About The Author

Lucas Jellema

9 Comments