For a customer i needed to call a external REST webservice to update an external database with some specific information.
With the use of the utl_http package Oracle has made this very simple.
I tried to make the procedure i made, self-explaining.
create or replace procedure call_rest_webservice
as
t_http_req utl_http.req;
t_http_resp utl_http.resp;
t_request_body varchar2(30000);
t_respond varchar2(30000);
t_start_pos integer := 1;
t_output varchar2(2000);
begin
/*Construct the information you want to send to the webservice.
Normally this would be in a xml structure. But for a REST-
webservice this is not mandatory. The webservice i needed to
call excepts plain test.*/
t_request_body := 'the data you want to send to the webservice';
/*Telling Oracle where the webservice can be found, what kind of request is made
and the version of the HTTP*/
t_http_req:= utl_http.begin_request( 'http://the_url_of_the_webservice'
, 'POST'
, 'HTTP/1.1');
/*In my case the webservice used authentication with a username an password
that was provided to me. You can skip this line if it's a public webservice.*/
utl_http.set_authentication(t_http_req,'username','password');
/*Describe in the request-header what kind of data is send*/
utl_http.set_header(t_http_req, 'Content-Type', 'text/xml charset=UTF-8');
/*Describe in the request-header the lengt of the data*/
utl_http.set_header(t_http_req, 'Content-Length', length(t_request_body));
/*Put the data in de body of the request*/
utl_http.write_text(t_http_req, t_request_body);
/*make the actual request to the webservice en catch the responce in a
variable*/
t_http_resp:= utl_http.get_response(t_http_req);
/*Read the body of the response, so you can find out if the information was
received ok by the webservice.
Go to the documentation of the webservice for what kind of responce you
should expect. In my case it was:
<responce>
<status>ok</status>
</responce>
*/
utl_http.read_text(t_http_resp, t_respond);
/*Some closing?1 Releasing some memory, i think....*/
utl_http.end_response(t_http_resp);
end;
Hi Wasantha,
My apologies for this late reaction.;
For calling the webservice with a JSON-object, you only have to change the request body to a JSON-string. Eg: t_request_body :=
JSON-object can be written down as a string-value. (the same as for XML). JSON is not the same as XML and has its own format/syntax. For making a JSON-string/object from pl/sql I suggest you take a look at the PL/JSON-project at http://sourceforge.net, maybe it can help you.
In the coming weeks I have to build my own rest-webservice request from pl/sql were a JSON-object is returns. After that I can help you maybe further.
Greetings,
Sergei
Hello Sergei,
I’m interested on developing a rest/json web service request from pl/sql. I have been searching the web and Oracle forums for such implementation without success.
Have you already developed such code, or can you give more tips how to do it?
Regards,
Jose.
Hi Jose,
I have not developed any code with pl/sql for JSON yet. You could have a look at http://sourceforge.net/projects/pljson/.
It is a seperate package for creating json-objects with pl/sql.
I hope thiss helps for you.
Regards,
Sergei
Dear Sir,
We are new to web-services and would be very grateful for your expert advice. We have a Telco app. and we need to get some information from a REST web-service. i.e. We need to write a PL/SQL stored procedure to pass parameters to a REST web-service and get the return value from it. Only thing is, for that REST WS all parameters (in/out) is handled using JSON objects.
If I understand it after looking it up in the web, JSON is just a text string as we are concerned??? i.e. text string in JSON format, just like XML?
So, if paras are in JSON what changes needs to be done to the above???
Also, how can we get the string/value pairs from the returned JSON string? I tried the Web but could not find a JSON parser for PL/SQL???
Your help would be greatly appreciated?
Regards,
Wasantha de Silva
Hi Steve,
Just like Richard says; try to put the parameter and value in the URL:
/*Telling Oracle where the webservice can be found, what kind of request is made
and the version of the HTTP*/
t_http_req:= utl_http.begin_request( ‘http://162.18.42.10/ws-rest-1.0/getCurrency?currency=CHF’,'POST’,'HTTP/1.1′);
Did you try to add the parameters to the URL just as is in your example?
Hi,
I’m a absolutly rookie in WebServices, so many thanx for this example.
But how to handle Parameters with Rest WebServices?? We have a WebService over here to get the exchange rate of a given currency. Example:
http://162.18.42.10/ws-rest-1.0/getCurrency?currency=CHF
This call returns the current exchange rate of Suisse Francs.
Regards
Steve
Hi Richard, we did some searching and found out that this is a bug in Oracle 10.2.
It is fixed in 11.2. This is bug 6878991: UTL_HTTP THROWS ERROR =ORA-29266: END-OF-BODY REACHED. Thanks for your time anyway. Reaaly appreciate it. Since we don’t have 11.2 DB we are asking our .Net team to develop the stuff.
Rohan,
Again ping only checks the network connection. In other words is there a computer at this address.. Abviously there is, otherwise you would not be able to open the web-pages..
192.168.*.* indicates that this is a local server. *.aspx indicates that this is a .NET webservice. Is the server maintained by you guys?
Maybe invistigating the WSDL gives some insight:
In your case : http://192.168.20.205:8099/web/BackendAPIs/Ref/?WSDL or something like that.
Good luck,
Richard
Yes, PING is a regular PING we use. i.e. PING ip_addr.
Thanks so much for the reply Richard.
It’s like this: The WS URL they gave us is http://192.168.20.205:8099/web/BackendAPIs/Ref/Country.aspx
We can ping 20.205. When we copy the above URL in the browser and press enter we get a blank page with “Done” specified at the bottom left of the browse (i.e. msg bar).
http://192.168.20.205:8099/web will invoke login page for the main system. The WSs are part of this system.
I have searched all over the web and put this even in Oracle forums but we did not get a reply from anybody. You are the only lifeline for us. We would really appreciate if you could help us Richard. Thanks in advance.
Hello,
We get error when we ORA-29266: end-of-body reached when calling the Web Service.
Even if we use GET, the same error comes. What could be the problem.
We can PING the service the WS is, so we are not using authentication. Could this be the problem?
Thanks & Regards,
Rohan
@rohan,
What kind of ping is that, a regular PING? That only checks if you can reach the IP number. The IP-routing so to speak.
What happens when you enter the URL in a browser?