xml - 如何处理 Oracle XML DB native Web 服务中的 NULL 存储过程返回参数？

Question

我有一个简单的 Oracle 包，其中包含一个简单的存储过程。存储过程声明 1 个 IN 参数和几个 OUT 参数。

只要 OUT 参数包含值，我就可以使用 XML DB native Web 服务成功调用存储过程。

但是，如果任何 OUT 参数包含 NULL，我将得到一个包含 ORA-01405 提取列值为空的 SOAP 错误。

我可以看到在调用 SQL 时有处理 NULL 值的选项(使用 <null_handling> 元素，但是有人知道如何用 PL/SQL 做同样的事情吗？

...

-- create a test table
CREATE TABLE xmldb_test
(
   key_value varchar2(32)
   ,value1 varchar2(32)
   ,value2 varchar2(32)
   ,value3 varchar2(32)
);

-- populate the table
insert into xmldb_test values ('key1', 'value1', 'value2', 'value3');
insert into xmldb_test values ('key2', 'value4', 'value5', null); -- this row has a null
commit;

-- create package
CREATE OR REPLACE package pack_xmldb_test
is
procedure getDataValue (ca_key in varchar2, ra_value1 out varchar2, ra_value2 out varchar2, ra_value3 out varchar2);
end;

-- create package body
create or replace package body pack_xmldb_test
is
procedure getDataValue (ca_key in varchar2, ra_value1 out varchar2, ra_value2 out varchar2, ra_value3 out varchar2)
IS
BEGIN
  select value1, value2, value3 
  into ra_value1, ra_value2, ra_value3
  from xmldb_test
  where key_value = ca_key;
END;
end;

-- test the package in pl/sql
declare
key_value varchar2(32) := 'key1';
value1 varchar2(32) := 'dog';
value2 varchar2(32) := null;
value3 varchar2(32) := null;
begin
dbms_output.put_line('before call, key_value:' || key_value);
pack_xmldb_test.getDataValue(key_value, value1, value2, value3);
dbms_output.put_line('after call, value1:' || value1 || ' value2:' || value2 || ' value3:' || value3);
end;

The WSDL for the Stored Procedure is at ...
http://node:port/orawsv/schema_name/pack_xmldb_test/getDataValue?wsdl

Build a soapUI project based on the WSDL, then ...

Sending the following request :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:get="http://xmlns.oracle.com/orawsv/CUSTOMER_FRAUD/PACK_XMLDB_TEST/GETDATAVALUE">
   <soapenv:Header/>
   <soapenv:Body>
      <get:GETDATAVALUEInput>
         <get:RA_VALUE3-VARCHAR2-OUT/>
         <get:RA_VALUE2-VARCHAR2-OUT/>
         <get:RA_VALUE1-VARCHAR2-OUT/>
         <get:CA_KEY-VARCHAR2-IN>key1</get:CA_KEY-VARCHAR2-IN>
      </get:GETDATAVALUEInput>
   </soapenv:Body>
</soapenv:Envelope>

... results in a successful call :

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
   <soap:Body>
      <GETDATAVALUEOutput xmlns="http://xmlns.oracle.com/orawsv/CUSTOMER_FRAUD/PACK_XMLDB_TEST/GETDATAVALUE">
         <RA_VALUE1>value1</RA_VALUE1>
         <RA_VALUE2>value2</RA_VALUE2>
         <RA_VALUE3>value3</RA_VALUE3>
      </GETDATAVALUEOutput>
   </soap:Body>
</soap:Envelope>

... but calling with this request :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:get="http://xmlns.oracle.com/orawsv/CUSTOMER_FRAUD/PACK_XMLDB_TEST/GETDATAVALUE">
   <soapenv:Header/>
   <soapenv:Body>
      <get:GETDATAVALUEInput>
         <get:RA_VALUE3-VARCHAR2-OUT/>
         <get:RA_VALUE2-VARCHAR2-OUT/>
         <get:RA_VALUE1-VARCHAR2-OUT/>
         <get:CA_KEY-VARCHAR2-IN>key2</get:CA_KEY-VARCHAR2-IN>
      </get:GETDATAVALUEInput>
   </soapenv:Body>
</soapenv:Envelope>

... results in the following fault :

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
   <soap:Body>
      <soap:Fault>
         <faultcode>soap:Client</faultcode>
         <faultstring>Error processing input</faultstring>
         <detail>
            <OracleErrors xmlns="http://xmlns.oracle.com/orawsv/faults">
               <OracleError>
                  <ErrorNumber>ORA-19202</ErrorNumber>
                  <Message>Error occurred in XML processing</Message>
               </OracleError>
               <OracleError>
                  <ErrorNumber>ORA-01405</ErrorNumber>
                  <Message>fetched column value is NULL</Message>
               </OracleError>
            </OracleErrors>
         </detail>
      </soap:Fault>
   </soap:Body>
</soap:Envelope>

Answer 1

抱歉，这不是一个更有说服力的解决方案，但也许可以在您的 pl/sql 中尝试:

select nvl(ra_value1, '""'), nvl(ra_value2,'""'), nvl(ra_value3,'""')
into ra_value1, ra_value2, ra_value3
from xmldb_test
where key_value = ca_key;

我只是将空值转换为双引号 ""，但更改为对 XML 有意义的内容。

我承认这是一个 hack，应该有一个更有说服力的解决方案。检查docs ，老实说，我不确定如何通过 wsdl 执行此操作(如果可能的话)。