Comments
| Consuming Web Services in Oracle - Access web services directly from PL/SQL using this simple API. |
mark s said... do you have a copy of the wsdl of the webservice that you are calling? |
Tim... said... Hi.When I wrote the article, the WDSL file was: http://services.xmethods.net/soap/urn:xmethods-delayed-quotes.wsdl Cheers Tim... |
florin p said... this is nice but how can I support 2 level params e.g. |
Thiago Lima said... Hi,I tried to use your solution, but i get errors. Can you give me a example using this WebService (HTTPS), please? https://www3.bcb.gov.br/sgspub/JSP/sgsgeral/FachadaWSSGS.wsdl I tried to open the URL that you use in your example, but it isn't acessible. thank's Thiago |
PsmakR said... Tim, could you supply a code with a working service ?Thx in advance |
NickRuiz said... I don't know if there is a better way to post code, but I just wrote a function that demonstrates the use of the SOAP_API package to invoke a web service that I found on the Internet.
|
george said... I get this error"RPCs cannot use variables with schema level user-defined types in this release" |
Tim... said... Hi.The old orignal web service listed is no longer available, so I've recoded it to use an available web service (suggested by NickRuiz). Nick: I've been a little more verbose than you, and I've used a different namespace syntax so I can reuse a single variable, but it's essentially the same. I've also redone the UTL_DBWS article to use the same web service so they look consistent. Cheers Tim... |
Marco said... Hi, your example helped me a lot to consume some basic information from a Web Service, but the thing is that it got a little more complex and now I need to get just selected information from a service XML response such us the one I list below, do you know how can I scan inside the XML and get only certain values that I need?Thanks in advance!! =0) |
Tim... said... Please ask questions in the forum. As you can see, the comments are for plain text and don't work well for formatted text questions.Cheers Tim... |
sukhen said... query |
roopesh said... Hi Thiago Lima ,can you able to run with your wsdl provided...if is can you provide me the function.. roopesh_7@yahoo.co.in Thanks Roopesh |
Jack said... Hi NickRuiz,I tried to exicute with the function you provided..i am getting below errors... ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00260: invalid xml:space attribute declaration Error at line 4 Error at line 372 Any idea???????? |
Susanna said... Hi Tim,I installed the code, and executed the following "select get_city_from_zipcode1('94065') from dual", But I got error below, can you please advise me what to do ? ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1029 ORA-12541: TNS:no listener Thanks Susanna |
Samod said... I too got a similar errorORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1029 ORA-12541: TNS:no listener Thanks, Samod |
Tim... said... Make sure your server has access to the web service.Cheers Tim... |
Channa said... Hi,This method seems to be the less complex of all methods to call a WS from the DB. However, I happen to read this Oracle Doc called FAQ Oracle Database as a Web Services Client (Doc ID-838892.1). This is a July-2009 article I got from Metalink. It says that there are ONLY 2 ways to call a WS from the DB. (1.) Using Oracle JPublisher & Static Proxy (2.) Using The Oracle Database Web Services ( DBWS ) Callout Utility & Dynamic Invocation Interface ( DII ) I wonder whether this method using SOAP_API is now NOT recommended by Oracle? |
Tim... said... Hi.They are the only native methods. You can do anything you want with HTTP. :) Cheers Tim... |
Channa said... Hi Tim,Thanks for your reply. But according to this Official Oracle document of July-2009 (FAQ Oracle Database as a Web Services Client (Oracle Doc ID-838892.1):"UTL_HTTP should not be used to make SOAP calls. UTL_DBWS & the DBWS Callout Utility are specially developed for making SOAP calls, and tested and supported for the purpose. New features, bug fixes and patches are available only for the Oracle DBWS Callout Utility as far as SOAP calls are concerned.". Can you please confirm whether we can use your method using SOAP_API? Also, the WS you have specified for the example is NOT working. I tried another WS on the net. Even that gave "ORA-12545: Connect failed because target host or object does not exist". So I did this: I installed OC4J in my machine and installed the sample Oracle web service as specified in this Oracle document: http://www.oracle.com/technology/sample_code/tech/java/jsp/callout_users_guide.htm. Can you please look at this Oracle Forum where I put this as a query with screen shots of the way I changed my parameters. http://forums.oracle.com/forums/thread.jspa?threadID=981169&tstart=45 Can you please tell me what I have done wrong? |
Tim... said... Hi.Q. What are SOAP web services? A. Just XML documents passed over HTTP. Q. Does Oracle fully support HTTP access from PL/SQL. A. Yes. Q. Is there anything preventing me from coding web services manually using the PL/SQL HTTP functionality? A. No. Q. What is Oracle's preferred method? A. Using their built in functionality, but there is nothing to stop you doing it manually. Regarding the error you received, that just means your server can't see the server providing the web services. It doesn't mean the web service does not exist or the code doesn't work. Please, if you want to continue this discussion, raise it on the forum. These comments are not a place to quest/answer sessions. Cheers Tim... |
RR said... Wonderful functionnal exemple, thanks a lot ! |
henry said... Hi,I need change params for string, no integer params, How to is it? Thanks |
Tim... said... Hi.The type needs to be xsd:string Cheers Tim... |
Pau said... I tried your code and worked perfect until I invoked WS with a long response.I had to patched it to work with long responses. I had to change those lines: UTL_HTTP.read_text(l_http_response, l_envelope); UTL_HTTP.end_response(l_http_response); l_response.doc := XMLTYPE.createxml(l_envelope); with those ones: -- Type declaration SOAP_RESPOND CLOB; -- Body BEGIN DBMS_LOB.createtemporary(SOAP_RESPOND, TRUE); DBMS_LOB.open(SOAP_RESPOND, DBMS_LOB.lob_readwrite); LOOP UTL_HTTP.read_text(l_http_response, l_envelope, 32767); DBMS_LOB.writeappend (SOAP_RESPOND, LENGTH(l_envelope), l_envelope); END LOOP; EXCEPTION WHEN utl_http.end_of_body THEN NULL; END; UTL_HTTP.end_response(l_http_response); l_response.doc := XMLTYPE.createxml(SOAP_RESPOND); I hope that can help others with the same problem. Cheers. Pau |
biswanathchakraborty said... png file can be added in sql scripting. |
Fran said... Hi Time how can I use this type of parameters?:-p_type => 'http://schemas.xmlsoap.org/soap/encoding/soapenc:string' and p_type => 'soapenc:string' gracias!! |
Tim... said... Hi.Looks like the Envelope would need extra elements the SOAP_API does not cope with. You would need to code it manually. Cheers Tim... PS. Questions in forum, not here. :) |
Fran said... thanks Tim!! |
alex said... i have problems to test that code, i compile sopa_api and add_numbers but i have errors...ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP",line 1029 ORA-12541: TNS:no listener ORA-06512: at "dbtest.SOAP_API",line 144 ORA-06512: at "dbtest.ADD_NUMBERS", line 34 |
Tim... said... Hi.My guess would be your Db can't access the internet directly. Please ask questions in the forum, not in the comments. Cheers Tim... |
DO NOT ask technical questions here, that's what my forum is for!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!