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

Comments

UTL_DBWS - Consuming Web Services in Oracle 10g - Use the UTL_DBWS package to consume web services from PL/SQL.



kyo said...

does anyone has this problem?

ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: HTTP transport error: java.net.MalformedURLException
ORA-06512: at "SYS.UTL_DBWS", line 387
ORA-06512: at "SYS.UTL_DBWS", line 384
ORA-06512: at "STOCKUSERS.GET_STOCKS_PRICE", line 32

Tim... said...

Hi.

I can't say I've seen this with this example. Make sure the URL for the web service is correct and you have access to the internet from the Oracle server. If this doesn't help, please use the forum to discuss the problem.

Cheers

Tim...

kyo said...

I was wondering if anyone has a working example of taking in multiple parameters into the pl/sql function and invokes a call that takes in multiple parameters.

Thanks,

Chris Muir said...

Hi Tim

To get a clarification on this article please. The article says "UTL_DBWS package, which is essentially a PL/SQL wrapper over JPublisher."

Can you clarify what you mean by this as my understanding is JPublisher is a tool for creating PL/SQL wrappers over Java code and vice versa? I'm guessing what you meant is that UTL_DBWS is a wrapper over some Java Class Library in the database which has been produced with JPublisher. I ask because I’m curious to know what Java code that is and how you found out?

Cheers,

CM.

Tim... said...

Hi Chris.

If you download the zip file listed at the start yo will see the following line in the readme:

"This utility is based on JPublisher 10g Release 2 (10.2) and Oracle WebService 10g Release 3 (10.1.3)"

Perhaps my wording is vague. :)

The JAR files are present in the zip file.

Cheers

Tim...

Vadim said...

Looks like the WSDL address in the example is no longer valid. Can anyone please give a working example for the variables

l_wsdl_url
l_service_name
l_operation_name

Thanks.

Tim... said...

Hi.

Recoded with a working web service. I hope this one stays around for a while. :)

Cheers

Tim...

Felipe B said...

Congratulations,

You was the only one who could show us how to use UTL_DBWS.
Maybe one day Oracle will write some usefull documentantion about that package.

Good work!

Many thanks,

Felipe

Chris Muir said...

Hi Tim

As I prepare for my OOW08 session, including a discussion on utl_dbws, I'll just follow up on your post with a subnote. On following your installation instructions under 11g (this could also be applicable for 10g), the loadjava routine will actually return a number of errors such as:

The following operations failed
class com/sun/jmx/interceptor/DefaultMBeanServerInterceptor$1: creation (createFailed)
class com/sun/jmx/interceptor/DefaultMBeanServerInterceptor$ListenerWrapper: creation (createFailed)
class com/sun/jmx/interceptor/DefaultMBeanServerInterceptor: creation (createFailed)

..and so on..

As far as I can tell these errors are acceptable and don't effect the operation of utl_dbws.

Cheers,

CM.

Chris Muir said...

It looks like that Web Service has been swallowed by the internet blackhole too :(

CM.

Ricardo said...

First of all, Thank you! it really helps me.
but
I have a Problem:
I create a function CallWS that works very nice,

but when i call the function CallWS from a JavaSource It doesn't Work. I think is weird because i execute the same function from a plsql window and it works very nice, but i have the problem in java sources...

The error is:

ORA-29532: Java call terminated by uncaught Java exception: java.lang.IllegalAccessException: javax.xml.rpc.ServiceException: Unable to create Service Factory: null


My function is: (I have the problem after 2.2;

create or replace function EjecutaWSGenerico(p_xml_in in varchar2,
p_wsdl_url VARCHAR2,
p_namespace VARCHAR2,
p_service_qname varchar2,
p_port_qname varchar2,
p_operation_qname varchar2,
p_internet boolean := FALSE)
return varchar2 is

l_service sys.UTL_DBWS.service;
l_call sys.UTL_DBWS.call;
l_result ANYDATA;


l_service_qname sys.UTL_DBWS.qname;
l_port_qname sys.UTL_DBWS.qname;
l_operation_qname sys.UTL_DBWS.qname;
l_input_params sys.UTL_DBWS.anydata_list;



BEGIN


IF p_internet THEN
-- solo para internet
sys.UTL_DBWS.set_http_proxy('X.X.X.X:XX');
END IF;

l_service_qname := sys.UTL_DBWS.to_qname(p_namespace, p_service_qname);
l_port_qname := sys.UTL_DBWS.to_qname(p_namespace, p_port_qname);
l_operation_qname := sys.UTL_DBWS.to_qname(p_namespace, p_operation_qname);


dbms_output.put_line(p_wsdl_url);
dbms_output.put_line(p_xml_in);
dbms_output.put_line('Here 2.2');


l_service := sys.UTL_DBWS.create_service( wsdl_document_location => URIFACTORY.getURI(p_wsdl_url),
service_name => l_service_qname);



dbms_output.put_line('Here 2.3');

l_call := sys.UTL_DBWS.create_call(service_handle => l_service,
port_name => l_port_qname,
operation_name => l_operation_qname);

l_input_params(0) := ANYDATA.ConvertVarchar2(p_xml_in);

l_result := sys.UTL_DBWS.invoke(call_handle => l_call,
input_params => l_input_params);

sys.UTL_DBWS.release_call(call_handle => l_call);
sys.UTL_DBWS.release_service(service_handle => l_service);

return l_result.AccessVarchar2;

exception
when others then
dbms_output.put_line(SQLCODE || ' - ' || SQLERRM);
RETURN SQLCODE || ' - ' || SQLERRM;
--return(l_result.AccessVarchar2);


end;

I don't know what it's going on, because the java invoke properly the function, but doesn't work.

If anybody have an idea, please mail me;

weychoi@yahoo.com

Thanks!

Ricardo Leon



J.K.-O. said...

Upgrading from 10.1.0.5 to 10.2.0.4 generates 232 invalid java class objects after catpatch. What is the proper path for upgrading with dbws-callout already installed?

Pankaj said...

Hi Tim,

Does your code with UTL_DBWS package work on Oracle version "10.2.0.1.0" on Windows XP OS?

I read somewhere that "10.2.0.1.0" does not support this package.

Please advice.

PS: When I run a sample function on "10.2.0.1.0", I get the following error -

ORA-29532: Java call terminated by uncaught Java exception: HTTP transport error: javax.xml.soap.SOAPException: java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Bad response: 404
ORA-06512: at "SYS.UTL_DBWS", line 387
ORA-06512: at "SYS.UTL_DBWS", line 384

Bala said...

Hi,
I have followed the steps given in the forum to setup external web service call using UTL_DBWS. I am able to read the XML file and call the operation. But during the invoke, I am getting the following error.

ORA-29532: Java call terminated by uncaught Java exception: HTTP transport error: javax.xml.soap.SOAPException: java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Message send failed: Connection refused

Can any one help me to solve the issue.

With Regards,
Bala

Leo said...

Hello. I'm trying to call a WAS from PL / SQL with the package utl_dbms. and when I run get the following error:

ORA-29532: llamada Java terminada por una excepción Java no resuelta: HTTP transport error: java.net.MalformedURLException

Can any one help me.

Maurizio said...

Hi,
I've got a similar problem. After a successfully installation
Classes Loaded: 3991
Resources Loaded: 81
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 4072
Errors: 0

I receive a

ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException
ORA-06512: at "SYS.UTL_DBWS", line 564
ORA-06512: at "SYS.UTL_DBWS", line 494
ORA-06512: at "SYS.UTL_DBWS", line 376

calling my webservice...

Can anyone help me please ?

Thanks in advance

hank said...

I ran the exact same code described in this article, but getting the following error
------------------------*INVALID ARGUMENT*
ORA-29532: Java call terminated by uncaught Java exception: java.lang.IllegalAccessException: error.build.wsdl.model: oracle.j2ee.ws.common.tools.api.WsdlValidationException: Failed to read wsdl file at: "http://www.oracle-base.com/webservices/server.php?wsdl", caused by: java.net.SocketException. : Invalid argument
Invalid argument
ORA-06512: at "SYS.UTL_DBWS", line 159
ORA-06512: at "SYS.UTL_DBWS", line 156
ORA-06512: at line 22

Thanks a lot for your help.

Tim... said...

Hi.

If you paste the WSDL file URL to a browser you can see it is present. Does your DB have access to the internet?

Cheers

Tim...

KUMAR said...

Thank you. In a previous article a method for Consuming Web Services using a basic SOAP implementation was presented. Like to know if this method is better than DBWS method. Is the previous method valid for 10g and 11.
Are there any preference ? Please enlighten me with your ideas.

Tim... said...

Hi.

The SOAP_API method is more flexible because you are completely in control. The ULT_DBWS method is simpler.

I would suggest you use the method you are happiest with. :)

Cheers

Tim...

Kumar said...

Just want to make sure I have understood all the aspects of using SOAP_API method. If I bring in the SOAP_API then all I have to do is use the methods in the API. Now since this is not using any java related stuff I assume I will have fewer issues (no java issues). More over it should perform better as it has fewer layers (no java layer).
Are the above statements true?

Tim... said...

Hi.

Yes. You just use the SOAP_API methods, so you might consider it simpler from a technology point of view.

As for issues and performance, I guess that depends on your usage. I still kinda prefer the SOAP_API method myself. :)

Cheers

Tim...

Kumar said...

Hi!
I back with some questions. When I access a webservice if everything is OK I get the result back and that is good. Now lets take I get a error message back. This is a different xml. Now how do I handle this? Like to know how to handle multiple xml response. Please give me some ideas.

Tim... said...

Dude. Look at the red text. Questions in forum, not in comments!!!

For other return documents, do a different extract at the end. Pull out the necessary elements as desired.

NO MORE QUESTIONS IN THE COMMENTS!

Tim...

Kumar said...

Thank you.
The documents you put out were very helpful and I thank you for your help.

Aryan said...

Hi,
I am trying to invoke a webservice using UTL_DBWS on 11g. There were no errors while installation also the UTL_DBWS package was created without errors. I get following errors when I execute my script:
29532. 00000 - "Java call terminated by uncaught Java exception: %s"
*Cause: A Java exception or error was signaled and could not be
resolved by the Java code.
Request respons

Tim... said...

Hi.

Questions in the forum please.

Cheers

Tim...

kamal said...

ORA-29540: class oracle/jpub/runtime/dbws/DbwsProxy does not exist
ORA-06512: at "SYS.UTL_DBWS", line 159
ORA-06512: at "SYS.UTL_DBWS", line 156
ORA-06512: at "KAMAL.ADD_NUMBERS1", line 25



What can be done to overcome this ??

Tim... said...

Hi.

Looks like the DBWS Callout Utility is not installed properly on your server.

Please ask questions in the forum not here.

Cheers

Tim...

hamed said...

i hava this error when calling web servies

ORA-29532: Java call terminated by uncaught Java exception: HTTP transport error: javax.xml.soap.SOAPException: java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Message send failed: Operation timed out

Tim... said...

Hi.

"Operation timed out", kinda says it all. Check your database server has access to this web service.

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!

Add your comments here.
Name
Comment
(max 400 chars - plain text)