
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; /
3 Answers
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...
3Here 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;
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.
1ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoa2lpYmiAdnvIp62oo5Virm7DxJuqnqqmnrCmedOhqaitl516sbiMrKilZZKhvKS3