This forum is currently locked. You can't register or post questions at this time. (read more)

UTL_DBWS Tutorial Problems

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

anasdawood
Member
Posts: 5
Joined: Mon Dec 17, 2012 10:37 am

UTL_DBWS Tutorial Problems

Postby anasdawood » Mon Dec 17, 2012 11:08 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: UTL_DBWS Tutorial Problems

Postby Tim... » Mon Dec 17, 2012 5:43 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

anasdawood
Member
Posts: 5
Joined: Mon Dec 17, 2012 10:37 am

Re: UTL_DBWS Tutorial Problems

Postby anasdawood » Tue Dec 18, 2012 7:17 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: UTL_DBWS Tutorial Problems

Postby Tim... » Tue Dec 18, 2012 8:30 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

anasdawood
Member
Posts: 5
Joined: Mon Dec 17, 2012 10:37 am

Re: UTL_DBWS Tutorial Problems

Postby anasdawood » Wed Dec 19, 2012 6:06 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: UTL_DBWS Tutorial Problems

Postby Tim... » Wed Dec 19, 2012 3:17 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

anasdawood
Member
Posts: 5
Joined: Mon Dec 17, 2012 10:37 am

Re: UTL_DBWS Tutorial Problems

Postby anasdawood » Sat Dec 22, 2012 6:17 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: UTL_DBWS Tutorial Problems

Postby Tim... » Sat Dec 22, 2012 11:03 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

anasdawood
Member
Posts: 5
Joined: Mon Dec 17, 2012 10:37 am

Re: UTL_DBWS Tutorial Problems

Postby anasdawood » Sun Dec 23, 2012 6:29 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: UTL_DBWS Tutorial Problems

Postby Tim... » Sun Dec 23, 2012 8:31 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 11 guests