invoke a webservice through pl/sql block

Question

How to invoke a webservice through pl/sql block for which we know url,username and password.

And how to see the response?

Give some sample code...

Thanks in advance

I have used the following piece of code:

CREATE OR REPLACE FUNCTION READ_DATA_FROM_WS (url IN VARCHAR2,
                                              username IN VARCHAR2,
                                              password IN VARCHAR2)
   RETURN CLOB
IS
   req           UTL_HTTP.req;
   resp          UTL_HTTP.resp;
   DATA          VARCHAR2 (2000);
   data1         CLOB;
   def_timeout   PLS_INTEGER;
   l_envelope       VARCHAR2(32767);
BEGIN
req := utl_http.begin_request(url, 'POST','HTTP/1.0');

UTL_HTTP.set_authentication (req, username, password);
utl_http.set_header(req, 'Content-Type', 'text/xml'); 

resp := utl_http.get_response(req);

   IF (resp.status_code = UTL_HTTP.http_ok)
   THEN
      UTL_HTTP.set_body_charset (resp, 'UTF-8');    
  BEGIN
         LOOP
            UTL_HTTP.read_text (resp, DATA);
            data1 := data1 || DATA;           
         END LOOP;
      EXCEPTION
         WHEN UTL_HTTP.end_of_body
         THEN     
            UTL_HTTP.end_response (resp);
            UTL_HTTP.set_transfer_timeout (def_timeout);
         WHEN OTHERS
         THEN
            NULL;
      END;
      UTL_HTTP.set_transfer_timeout (def_timeout);
   ELSE
      UTL_HTTP.end_response (resp);
      DBMS_OUTPUT.put_line ('HTTP response status code: ' || resp.status_code);
   END IF;
   RETURN (data1);
END read_data_from_ws;
/

Answer 1

I do not think it is possible. Even if it is, it will be very difficult and not worthy to do it inside the pl/sql block.

Maybe you need to write some other program (service) which will periodically check your web service and then it will put response data to the database tables, then you will be able to obtain that data from the web service in your pl/sql block.

Answer 2

Even if there is a way to do this it would be a very bad practice!

Also, there are so many problems here. What will this service return? How are you gonna parse the results to something that sql can understand? How are you going to handle errors coming back from the service?

Just return whatever it is you need to return to the application and have the app invoke the web service.

Answer 3

I have used web services with pl/sql without problems! I'm using this one (+ my own improvements): http://www.oracle-base.com/dba/miscellaneous/soap_api.sql

Be sure that you define name spaces correctly, and I think you should only use this for retrieving ASCII not binary data...

Answer 4

Here is some sample code. Left some pieces out but it gives you an idea. The function returns the capabilities XML for a WMS webservice.

   function getcapabilities(p_url     varchar2
                           ,p_version varchar2) return xmltype is
      pragma autonomous_transaction;

      req                utl_http.req;
      resp               utl_http.resp;
      c                  varchar2(255);
      l_clob             clob;
   begin
      dbms_lob.createtemporary(lob_loc => l_clob, cache => true, dur => dbms_lob.call);
      -- -----------------------------------
      -- OPEN TEMPORARY LOB FOR READ / WRITE
      -- -----------------------------------
      dbms_lob.open(lob_loc => l_clob, open_mode => dbms_lob.lob_readwrite);

      utl_http.set_proxy(proxy => <proxy>, no_proxy_domains => <no_proxy>);

      /* request that exceptions are raised for error Status Codes */
      utl_http.set_response_error_check(enable => true);

      /* allow testing for exceptions like Utl_Http.Http_Server_Error */
      utl_http.set_detailed_excp_support(enable => true);

      if instr(p_url, '?') > 0
      then
         req := utl_http.begin_request(p_url || '&REQUEST=GetCapabilities&SERVICE=WMS&VERSION=' ||
                                       p_version);
      else
         req := utl_http.begin_request(p_url || '?REQUEST=GetCapabilities&SERVICE=WMS&VERSION=' ||
                                       p_version);
      end if;

      utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
      resp := utl_http.get_response(req);

      begin
         loop
            utl_http.read_text(r => resp, data => c);

            /* function that adds a string to a clob */
            add_to_clob(l_clob, c);

         end loop;
      exception
         when utl_http.end_of_body then
            null;
         when others then
            raise;
      end;

      utl_http.end_response(resp);

      dbms_lob.close(lob_loc => l_clob);

      /* this was for some Oracle bug */
      execute immediate 'alter session set events =''31156 trace name context forever, level 2''';
      commit;
      return xmltype.createxml(l_clob);
   end;