我遵循这篇文章[1]作为指南,构建一个在WSO2 DSS中查询UDT数组的示例。在帖子中只是查询一个UDT,我的配置尝试查询一个UDT数组。
我在我的数据库中创建了这个,一个虚拟的过程来尝试这个:
create or replace
TYPE "LIST_CUSTOMERS" IS TABLE OF customer_t
CREATE OR REPLACE
PROCEDURE getCustomer2(listcust OUT list_customers) IS
cust customer_t;
cust2 customer_t;
BEGIN
listcust := list_customers();
cust := customer_t(1, 'prabath');
cust2 := customer_t(2, 'jorge');
listcust.extend;
listcust(1) := cust;
listcust.extend;
listcust(2) := cust2;
END;
我的DS是这样的:
<?xml version="1.0" encoding="UTF-8"?>
<data name="UDTSample2">
<config id="default">
<property name="org.wso2.ws.dataservice.driver">oracle.jdbc.driver.OracleDriver</property>
<property name="org.wso2.ws.dataservice.protocol">jdbc:oracle:thin:@localhost:1521:DBMB</property>
<property name="org.wso2.ws.dataservice.user">****</property>
<property name="org.wso2.ws.dataservice.password">****</property>
</config>
<query id="q3" useConfig="default">
<sql>call getCustomer2(?)</sql>
<result element="customers">
<element name="customer" arrayName="custArray" column="cust" optional="true"/>
</result>
<param name="cust" paramType="ARRAY" sqlType="ARRAY" type="OUT" structType="LIST_CUSTOMERS" />
</query>
<operation name="op3">
<call-query href="q3" />
</operation>
</data>
蚂蚁返回:
<customers xmlns="http://ws.wso2.org/dataservice">
<customer>{1,prabath}</customer>
<customer>{2,jorge}</customer>
</customers>
但是我想要这样的:
<customers xmlns="http://ws.wso2.org/dataservice">
<customer>
<id>1</id>
<name>prabath<name>
</customer>
<customer>
<id>2</id>
<name>Jorge<name>
</customer>
</customers>
我怎样才能做到这一点?
[1] http://prabathabey.blogspot.com/2012/05/query-udtsuser-defined-types-with-wso2.html
不确定这种转换是否可以在DSS级别完成,因为DSS会返回它从数据库接收到的内容。对于这种转换,最好使用wso2esb。
目前,仅使用DSS还不可能完成此场景。在将响应发送到客户端之前,必须将DS响应发送到WSO2 ESB以执行相应的转换。将来创建JIRA就是为了这样做https://wso2.org/jira/browse/DS-1104
作为一种解决方法,您可以使用返回sys_refcursor的过程。它看起来像这样:
PROCEDURE getCustomer_CUR(cur_cust OUT SYS_REFCURSOR)
l_cust LIST_CUSTOMERS;
IS
-- Retrieve cust list:
getCustomer2(l_cust);
OPEN cur_cust for
select cast(multiset(select * from TABLE(l_cust)) as customer_t) from dual;
...
END;
然后你可以像这样做DSS映射:
<sql>call getCustomer_CUR(?)</sql>
<result element="customers">
<element arrayName="custArray" name="Customers">
<element column="custArray[0]" name="col0" xsdType=.../>
...
</element>
</result>
<param name="cust" sqlType="ORACLE_REF_CURSOR" type="OUT"/>
这是乏味的,但它的工作。