Before I forget, hereby on my “notepad”, a small reminder for me and others that might be interested, how you can consume a webservice via plain old PL/SQL, for instance the SOAP service that can be created via the Oracle 11g Native Database WebService (so called NDWS), out-of-the-box 11g database functionality, as explained by Mark D on the XMLDB forum site here: “Re: Using utl_dbws to call web service”
The essential consuming part/code is shown below:
SET serveroutput ON -- DECLARE V_SOAP_REQUEST XMLTYPE := XMLTYPE( '<soap -ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> </soap><soap -ENV:Body> <m :SNUMBER-GET_SQRTInput xmlns:m="http://xmlns.oracle.com/orawsv/&USERNAME/GET_SQRT"> </m><m :INPUT_VALUE-NUMBER-IN>2</m> </soap> '); V_SOAP_REQUEST_TEXT CLOB := V_SOAP_REQUEST.getClobVal(); V_REQUEST UTL_HTTP.REQ; V_RESPONSE UTL_HTTP.RESP; V_BUFFER VARCHAR2(1024); BEGIN V_REQUEST := UTL_HTTP.BEGIN_REQUEST(URL => :URL, METHOD => 'POST'); UTL_HTTP.SET_HEADER(V_REQUEST, 'User-Agent', 'Mozilla/4.0'); V_REQUEST.METHOD := 'POST'; UTL_HTTP.SET_HEADER (R => V_REQUEST, NAME => 'Content-Length', VALUE => DBMS_LOB.GETLENGTH(V_SOAP_REQUEST_TEXT)); UTL_HTTP.WRITE_TEXT (R => V_REQUEST, DATA => V_SOAP_REQUEST_TEXT); V_RESPONSE := UTL_HTTP.GET_RESPONSE(V_REQUEST); LOOP UTL_HTTP.READ_LINE(V_RESPONSE, V_BUFFER, TRUE); DBMS_OUTPUT.PUT_LINE(V_BUFFER); END LOOP; UTL_HTTP.END_RESPONSE(V_RESPONSE); EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN UTL_HTTP.END_RESPONSE(V_RESPONSE); END; / |
A demo script for an Oracle 11g database can be downloaded here.
More info in how to set-up Native Database WebServices in Oracle 11g is explained here:
HTH
Marco
4 comments
Skip to comment form ↓
A_Non
16 July, 2009 at 23:30 (UTC 1) Link to this comment
Just a note of caution when sending large amounts of info to the remote web service. UTL_HTTP.WRITE_TEXT takes in a VARCHAR2 so if your data is over 32K, you’ll need a loop around UTL_HTTP.WRITE_TEXT to write the total message as 32K chunks to the web service.
Marco Gralike
17 July, 2009 at 0:21 (UTC 1) Link to this comment
Thanks Jason, good pointer…
Aditya
28 March, 2013 at 17:10 (UTC 1) Link to this comment
This is good, if the db version is 10.2 and above, using UTL_DBWS is the best way to consume Webservice
Marco Gralike
28 March, 2013 at 21:35 (UTC 1) Link to this comment
It might…but there are reasons why UTL_DBWS is not installed by default, and/or a part of a database feature, in the database. Oracle might become fuzzy regarding its support.