Hi Experts,
I have below mentioned code for a web service call. This code I copied from oracle-base.com(Tim Hall's site) for testing purpose. I executed it in sys and gave execute privilages to scott on both utl_http, utl_dbws.
[code]
create or replace FUNCTION updatePOStatus (vendor IN VARCHAR,
ponum IN VARCHAR,
poinum IN NUMBER,
status IN VARCHAR)
RETURN VARCHAR2
AS
l_service UTL_DBWS.service;
l_call UTL_DBWS.call;
l_wsdl_url VARCHAR2(32767);
l_namespace VARCHAR2(32767);
l_service_qname UTL_DBWS.qname;
l_port_qname UTL_DBWS.qname;
l_operation_qname UTL_DBWS.qname;
l_xmltype_in SYS.XMLTYPE;
l_xmltype_out SYS.XMLTYPE;
l_result LONG;
BEGIN
l_wsdl_url :='http://10.9.142.73/Service1.svc?singleWsdl';
l_namespace := 'http://10.9.142.73/';
l_service_qname := UTL_DBWS.to_qname(l_namespace, 'Service1');
l_port_qname := UTL_DBWS.to_qname(l_namespace, 'BasicHttpBinding_IService1');
l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'UpdatePOStatus');
l_service := UTL_DBWS.create_service(
wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
service_name => l_service_qname);
l_call := UTL_DBWS.create_call(
service_handle => l_service,
port_name => l_port_qname,
operation_name => l_operation_qname);
l_xmltype_in := SYS.XMLTYPE('<?xml version="10.0" encoding="utf-8"?>
<UpdatePOStatus xmlns="' || l_namespace || '">
<Poinum' || poinum || ' </Poinum>
<Ponum' || poinum || ' </Ponum>
<Status' || poinum || ' </Status>
<Vendor' || poinum || ' </Vendor>
</UpdatePOStatus>');
l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call,
request => l_xmltype_in);
UTL_DBWS.release_call (call_handle => l_call);
UTL_DBWS.release_service (service_handle => l_service);
l_result := l_xmltype_out.extract('//return/test()').getstringval();
RETURN l_result;
END;
[/code]
code with line numbers:
[code]
SQL> create or replace FUNCTION sys.updatePOStatus (vendor IN VARCHAR,
2 ponum IN VARCHAR,
3 poinum IN NUMBER,
4 status IN VARCHAR)
5 RETURN VARCHAR2
6 AS
7 l_service UTL_DBWS.service;
8 l_call UTL_DBWS.call;
9
10 l_wsdl_url VARCHAR2(32767);
11 l_namespace VARCHAR2(32767);
12 l_service_qname UTL_DBWS.qname;
13 l_port_qname UTL_DBWS.qname;
14 l_operation_qname UTL_DBWS.qname;
15
16 l_xmltype_in SYS.XMLTYPE;
17 l_xmltype_out SYS.XMLTYPE;
18 l_result LONG;
19 BEGIN
20 l_wsdl_url :='http://10.9.142.73/Service1.svc?singleWsdl';
21 l_namespace := 'http://10.9.142.73/';
22
23 l_service_qname := UTL_DBWS.to_qname(l_namespace, 'Service1');
24 l_port_qname := UTL_DBWS.to_qname(l_namespace, 'BasicHttpBinding_IService1');
25 l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'UpdatePOStatus');
26
27 l_service := UTL_DBWS.create_service(
28 wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
29 service_name => l_service_qname);
30
31 l_call := UTL_DBWS.create_call(
32 service_handle => l_service,
33 port_name => l_port_qname,
34 operation_name => l_operation_qname);
35
36 l_xmltype_in := SYS.XMLTYPE('<?xml version="10.0" encoding="utf-8"?>
37 <UpdatePOStatus xmlns="' || l_namespace || '">
38 <Poinum' || poinum || ' </Poinum>
39 <Ponum' || poinum || ' </Ponum>
40 <Status' || poinum || ' </Status>
41 <Vendor' || poinum || ' </Vendor>
42 </UpdatePOStatus>');
43 l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call,
44 request => l_xmltype_in);
45 UTL_DBWS.release_call (call_handle => l_call);
46 UTL_DBWS.release_service (service_handle => l_service);
47
48 l_result := l_xmltype_out.extract('//return/test()').getstringval();
49 RETURN l_result;
50 END;
[/code]
query from Scott is:
select updatePOStatus('10143','10037448','2','Started') from dual;
Errors:
Now when I execute through sql plus error is as below:
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 "SCOTT.UPDATEPOSTATUS", line 27
and the same executed from sql developer the error is as below:
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 "SCOTT.UPDATEPOSTATUS", line 27
29540. 00000 - "class %s does not exist"
*Cause: Java method execution failed to find a class with the indicated name.
*Action: Correct the name or add the missing Java class.
I checked all the methods in utl_dbws package there is no mistake in function names as per above Cause in Error.
Help me out in sorting out this.
Thanks & Regards,
Sirajuddin