UTL_DBWS Tutorial Problems

[anasdawood]

Hi,

I followed the example mentioned in this link : http://www.oracle-base.com/articles/10g/utl_dbws-10g.php . and compared it with other examples in other links.
after much researching i was able to load the required JAR files with no errors. I also created the UTL_DBWS in my database correctly. Also, I created the 'add_numbers' function with no errors.
However, In the mentioned example, and in all other examples i tried with utl_dbws , I'm always getting an error in the line that is calling the 'create_service' method.
the error is :

Code: Select all

ORA-29532: Java call terminated by uncaught Java exception:
java.lang.IllegalAccessException: java.lang.NoSuchMethodError
ORA-06512: at "SCOTT.UTL_DBWS", line 169
ORA-06512: at "SCOTT.UTL_DBWS", line 167
ORA-06512: at "SCOTT.ADD_NUMBERS", line 21

After checking the method in the utl_dbws package I'm finding that this method refers a JAVA class oracle.jpub.runtime.dbws.DbwsProxy.createService :

Code: Select all

 function create_service(wsdl_Document_Location URITYPE, service_Name QNAME)
  RETURN SERVICE IS
 BEGIN
  RETURN create_service_proxy(wsdl_Document_Location.GETURL(), service_Name);
 END create_service;
-----------------------------------------------------------------------------------------------------------
 function create_service_proxy(wsdl_Document_Location VARCHAR2, service_Name VARCHAR2) return SERVICE
 as language java
    name 'oracle.jpub.runtime.dbws.DbwsProxy.createService(java.lang.String,java.lang.String) return long';;

and when i search for this JAVA class in my db, i cant find it. Furthermore, when i try to look for the class in the dbws-callout-utility-10131.zip file attached in the example, its not there either, And its not in any of the included JAR files.
Where can i find this class?
if it was an installation error, I would have at least found the class manually inside one of the JAR files.

Im using Oracle database 11g.

thanks in advance.

[Tim...]

Hi.

Just a few questions:

- What version of the database. You just mention 11g.
- What OS is the database server running.
- Do you have access to the web service from your database server? There are no firewalls etc. to prevent you accessing it?
- You have used the correct version of the callout utility? (dbws-callout-utility-10131.zip)

Cheers

Tim...

[anasdawood]

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Im Running the Example on my local machine, which is running a 64-bit windows 7.i.e. the database is on my local machine.
When Im trying the Example with the UTL_HTTP, Its working fine.

Im not using a fire wall or a proxy.

I did use the 11g version of the callout utility.

every thing is compiling and loading fine, except that I cant find the class 'oracle.jpub.runtime.dbws.DbwsProxy.createService' anywhere . Not in the data base, not in the Zip files, not even in the JAR files.

[Tim...]

Hi.

Not sure what to say. I just downloaded the zip file. Loaded it into the SYS schema, as described in the article and it just worked.

Cheers

Tim...

[anasdawood]

did you find oracle.jpub.runtime.dbws.DbwsProxy.createService class ?

maybe you have this class in your database already.
if you have it just give it to me and Ill load it to my database. This will probably solve the problem.

[Tim...]

Hi.

I just loaded it on a Windows installation at work:

Code: Select all

SQL> CREATE OR REPLACE FUNCTION add_numbers (p_int_1 IN NUMBER,
  2                                          p_int_2 IN NUMBER)
  3    RETURN NUMBER
  4  AS
  5    l_service          UTL_DBWS.service;
  6    l_call             UTL_DBWS.call;
  7
  8    l_wsdl_url         VARCHAR2(32767);
  9    l_namespace        VARCHAR2(32767);
 10    l_service_qname    UTL_DBWS.qname;
 11    l_port_qname       UTL_DBWS.qname;
 12    l_operation_qname  UTL_DBWS.qname;
 13
 14    l_xmltype_in       SYS.XMLTYPE;
 15    l_xmltype_out      SYS.XMLTYPE;
 16    l_return           NUMBER;
 17  BEGIN
 18    l_wsdl_url        := 'http://www.oracle-base.com/webservices/server.php?wsdl';
 19    l_namespace       := 'http://www.oracle-base.com/webservices/';
 20
 21    l_service_qname   := UTL_DBWS.to_qname(l_namespace, 'Calculator');
 22    l_port_qname      := UTL_DBWS.to_qname(l_namespace, 'CalculatorPort');
 23    l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'ws_add');
 24
 25    l_service := UTL_DBWS.create_service (
 26      wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
 27      service_name           => l_service_qname);
 28
 29    l_call := UTL_DBWS.create_call (
 30      service_handle => l_service,
 31      port_name      => l_port_qname,
 32      operation_name => l_operation_qname);
 33
 34    l_xmltype_in := SYS.XMLTYPE('<?xml version="1.0" encoding="utf-8"?>
 35      <ws_add xmlns="' || l_namespace || '">
 36        <int1>' || p_int_1 || '</int1>
 37        <int2>' || p_int_2 || '</int2>
 38      </ws_add>');
 39    l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call,
 40                                     request     => l_xmltype_in);
 41
 42    UTL_DBWS.release_call (call_handle => l_call);
 43    UTL_DBWS.release_service (service_handle => l_service);
 44
 45    l_return := l_xmltype_out.extract('//return/text()').getNumberVal();
 46    RETURN l_return;
 47  END;
 48  /

Function created.

SQL> SELECT add_numbers(1, 5) FROM dual;

ADD_NUMBERS(1,5)
----------------
               6

SQL>

You can see what I've got in the database as far as Java classes go.

Code: Select all

SQL> select name from dba_java_classes where name like '%DbwsProxy%';

NAME
--------------------------------------------------------------------------------
oracle/jpub/runtime/dbws/DbwsProxy$ServiceProxy
oracle/jpub/runtime/dbws/DbwsProxy$1
oracle/jpub/runtime/dbws/DbwsProxy$CallProxy
oracle/jpub/runtime/dbws/DbwsProxy

SQL>

Cheers

Tim...

[anasdawood]

Thank you so much for your cooperation,
This doesn't make any sense. I have the exact same classes . What does your UTL_DBWS.create_service method call ?
and if it is calling oracle.jpub.runtime.dbws.DbwsProxy.createService, then i don't know where to go from here !!

[Tim...]

Hi.

Looking at the source or the UTL_DBWS package body, it is a call specification.

Code: Select all

function create_service(service_Name QNAME)
  RETURN SERVICE
 as language java
    name 'oracle.jpub.runtime.dbws.DbwsProxy.createService(java.lang.String) return long';

So the class is "oracle/jpub/runtime/dbws/DbwsProxy", which you see from my previous post is present in my database.

The class contains a method called createService, which is what this call specification is calling.

I think you've got confused thinking that "oracle.jpub.runtime.dbws.DbwsProxy.createService" is a class, when actually "createService" is a method in the "oracle.jpub.runtime.dbws.DbwsProxy" class. It can be a little confusing for us PL/SQL people.

Cheers

Tim...

[anasdawood]

hi,
Just to add to the mystery, after deleting all oracle.jpub.runtime.dbws classes and reloading them from the attached files in the examples, I'm getting all kind of errors and there not getting compiled. I think this is mainly because some other classes that are needed are not available.
the rabbit whole is getting deeper.

after using "alter java class resolve" and "show error" commands it turns out that a class called oracle/j2ee/ws/client/dii/ConfiguredService is not available.
where to find it ?

[Tim...]

Hi.

I don't know what is going on in your installation. If I were you I think I would:

- Uninstall the Oracle JVM.
@$ORACLE_HOME/javavm/install/rmjvm.sql
- Manually remove any remaining classes that get left behind.
- Re-install the Oracle JVM.
@$ORACLE_HOME/javavm/install/initjvm.sql
@$ORACLE_HOME/rdbms/admin/initplsj.sql
- Try to load the UTL_DBWS stuff again.

If that doesn't work, I think you should open an SR with Oracle Support and see if they can sort this out. I've certainly not seen anything like this before.

Cheers

Tim...