Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

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. p2

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.


FUNCTION get_city_by_zip (
p_zip IN VARCHAR2
)
RETURN VARCHAR2
IS
l_request soap_api.t_request;
l_response soap_api.t_response;

l_url VARCHAR2(400) := 'http://webservices.imacination.com/distance/Distance.jws';
l_namespace VARCHAR2(400);

l_city VARCHAR2(4000);
BEGIN
l_namespace := 'xmlns:ns1="' || l_url || '"';

l_request := SOAP_API.new_request('ns1:getCity', l_namespace);

SOAP_API.add_parameter(l_request, 'zip', 'xsd:string', p_zip);

l_response := soap_api.invoke(l_request, l_url, l_url || '#getCity');

l_city := soap_api.get_return_value(l_response, 'getCityReturn',
'xmlns="' || l_url || '" ');

RETURN l_city;
END;

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?




PRINCIPAL AAA
MERCANTIL


ACTIVIDAD 1:
TRUE
90


ACTIVIDAD 2:
TRUE
123


BIN LADEN
ACTIVIDAD 1:
550000




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 error

ORA-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...

pcmnac said...

Hello,

(sorry for the bad english)

I'm writing to introduce a tool I've developed in last year. It's available on GitHub (https://github.com/pcmnac/wsdl2plsql). The main idea is to generate PL/SQL code to consume a web service from its WSDL.

I'd like you enjoy it. Thanks.

Tim... said...

Hi.

OK, but you do know that UTL_DBWS does this right?

http://www.oracle-base.com/articles/10g/utl_dbws-10g.php

Cheers

Tim...
Prepared Statement Error: Table './oraclebasecms/cms_page_comment_uuids' is marked as crashed and should be repaired