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;
![Hands-on introduction to Oracle 11g at the AMIS KC Server Development event, Donderdag 1 november, 16.30 uur]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Hands-on introduction to Oracle 11g at the AMIS KC Server Development event, Donderdag 1 november, 16.30 uur")
Hello,
How can I send HTTP requests in Oracle to a web service which uses “Form” method as its base.
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
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.
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
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
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
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????
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
bhi, is it work in apex process.@MOHMMAD MSADI