Database Software

We're calling a web service using utl_dbws and getting a response but the problem is the parameters. I haven't been able to find much documentation about how to use the utl_dbws package and have only one example to work from so could do with some expert help.

The following code calls the webservice:

procedure call_web_service(p_application_key in number,
out_success_message out varchar2)
is

v_service utl_dbws.service;
v_call utl_dbws.call;
v_service_qname utl_dbws.qname;
v_port_qname utl_dbws.qname;
v_operation_qname utl_dbws.qname;
v_string_type_qname utl_dbws.qname;
v_return anydata;
v_send_data anydata;
v_return_string varchar2 (100);
v_return_length number;
v_parameter_string varchar2(32767);
v_params utl_dbws.anydata_list;
v_interview_xml xmltype;
v_policy_number varchar2(14);

-- return parameters
v_interviewId varchar2(1000);
v_statusType varchar2(1000);
v_error_reason varchar2(1000);

begin
message_handler.set_module_name('ostp_to_xpb.call_web_service');
message_handler.set_current_process('retrieve generated xml');
begin

select upload_xml, extractvalue(upload_xml, '/interview/externalReferenceNumber') policy_number
into v_interview_xml, v_policy_number
from xpb_upload_data
where application_key = p_application_key
and extractvalue(upload_xml, '/interview/externalReferenceNumber') is not null;

--dbms_output.put_line('xml retrieved');

exception
when no_data_found then
v_success_message := 'No xml found for application_key = '||p_application_key;
raise v_procedure_error;
end;

message_handler.set_current_process('call web service');

-- create service
v_service_qname := utl_dbws.to_qname (null, 'xpertBridge');

v_service := utl_dbws.create_service (v_service_qname);

-- create call
v_port_qname := utl_dbws.to_qname (null, 'xpertBridgePort');

v_operation_qname :=
utl_dbws.to_qname
('http://m0154ukdox1/xpertBridgeEDSLV/services/xpertBridge',
'orcaAppUpload'
);

v_call := utl_dbws.create_call (v_service, v_port_qname, v_operation_qname);

-- set endpoint
utl_dbws.set_target_endpoint_address
(v_call,
'http://m0154ukdox1/xpertBridgeEDSLV/services/xpertBridge'
);

-- set type of input and output parameters
v_string_type_qname :=
utl_dbws.to_qname ('http://www.w3.org/2001/XMLSchema', 'string');

utl_dbws.add_parameter (v_call,
'orcaXml',
v_string_type_qname,
'ParameterMode.IN'
);
utl_dbws.add_parameter (v_call,
'interviewId',
v_string_type_qname,
'ParameterMode.OUT'
);
utl_dbws.add_parameter (v_call,
'status',
v_string_type_qname,
'ParameterMode.OUT'
);
utl_dbws.add_parameter (v_call,
'errorReason',
v_string_type_qname,
'ParameterMode.OUT'
);

utl_dbws.set_return_type (v_call, v_string_type_qname);

-- convert xmltype to string for call
select xmlserialize(document v_interview_xml)
into v_parameter_string
from dual;

v_params (1) := anydata.convertvarchar(v_parameter_string);

-- call
v_return := utl_dbws.invoke (v_call, v_params);

-- values which can be returned are Success / MessageError / ApplicationError
v_return_string := v_return.accessvarchar2;
dbms_output.put_line ('Message returned is: ' || nvl(v_return_string, 'No success message returned'));

-- retrieve out parameters
v_interviewId := v_params(2).accessvarchar2;
dbms_output.put_line ('Message returned is: ' || nvl(v_interviewId, 'No interviewId returned'));

v_statusType := v_params(3).accessvarchar2;
dbms_output.put_line ('Message returned is: ' || nvl(v_statusType, 'No status type returned'));

v_error_reason := v_params(4).accessvarchar2;
dbms_output.put_line ('Message returned is: ' || nvl(v_error_reason, 'No error reason returned'));


-- release call
utl_dbws.release_call ( v_call );

-- release services
utl_dbws.release_service ( v_service );

message_handler.set_module_finish;
exception
when others then
out_success_message := message_handler.formatted_error_message;
end call_web_service;

Here is an excerpt from the WSDL relating to the call being made:

<xs:element name="orcaAppUpload">
<xs:annotation>
<xs:documentation xml:lang="en">Message payload XML</xs:documentation>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:element name="orcaXml" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="orcaAppUploadResponse">
<xs:complexType>
<xs:sequence>
<xs:element name="interviewId" type="xs:string">
<xs:annotation>
<xs:documentation xml:lang="en">Interview identifier used to access the interview from UI</xs:documentation>
</xs:annotation>
</xs:element>
<xs:element name="status" type="tns:StatusType"/>
<xs:element name="errorReason" type="xs:string" minOccurs="0">
<xs:annotation>
<xs:documentation xml:lang="en">Only included if an error has occured </xs:documentation>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:simpleType name="StatusType">
<xs:restriction base="xs:string">
<xs:enumeration value="Success"/>
<xs:enumeration value="MessageError">
<xs:annotation>
<xs:documentation xml:lang="en">MessageError arises if the request payload was
rejected by xpertBridge. This might be because it does not validate against the
expected schema. Alternatively, a business rule is not satisfied.</xs:documentation>
</xs:annotation>
</xs:enumeration>
<xs:enumeration value="ApplicationError">
<xs:annotation>
<xs:documentation xml:lang="en">ApplicationError would indicate application or system
error or exception occured in xpertBridge while processing the request.</xs:documentation>
</xs:annotation>
</xs:enumeration>
</xs:restriction>
</xs:simpleType>
</xs:schema>
...

<wsdl:message name="orcaAppUploadReq">
<wsdl:part name="orcaAppUpload" element="tns:orcaAppUpload"/>
</wsdl:message>
<wsdl:message name="orcaAppUploadResp">
<wsdl:part name="orcaAppUploadResponse" element="tns:orcaAppUploadResponse"/>
</wsdl:message>

...
<wsdl:portType name="xpertBridgePort">
<wsdl:operation name="orcaAppUpload">
<wsdl:documentation>Upload (typically paper) application from ORCA/Ingenium</wsdl:documentation>
<wsdl:input message="tns:orcaAppUploadReq"/>
<wsdl:output message="tns:orcaAppUploadResp"/>
</wsdl:operation>
</wsdl:portType>

etc.

The error being returned is the following:
ostp_to_xpb.call_web_service.call web service.ORA-29532: Java call terminated by uncaught Java exception: unexpected element name: expected=interviewId, actual=status

Initially I started the params at params(0) but when I received the response above I thought it might solve the problem by starting at 1 - try anything :-) but still had the same response.

I'm now out of ideas!