Spring 集成 - 无法检索 Oracle Array OUT PARAM [英] Spring Integration - Failing to Retrieve Oracle Array OUT PARAM
问题描述
描述:尝试创建返回 oracle 数组类型的网关服务调用.专门获取 sys.dbmsoutput_linesarray.当我注册 OUT 参数或可能在服务中时,我在下面得到的错误似乎是我的配置有问题.想知道是否有人可以告诉我我做错了什么?
Description: Attempting to create a gateway service call that returns a oracle array type. Specially getting the sys.dbmsoutput_linesarray. The error I get below seems that I have something wrong on my configuration when I register the OUT param or possibly in the service. Wondering if someone can tell me what I am doing wrong?
错误信息:CallableStatementCallback;SQL [{call GET_DBMS_OUTPUT(?)}] 的未分类 SQLException;SQL 状态 [99999];错误代码 [17004];无效的列类型:1111;嵌套异常是 java.sql.SQLException:无效的列类型:1111
Error Message: CallableStatementCallback; uncategorized SQLException for SQL [{call GET_DBMS_OUTPUT(?)}]; SQL state [99999]; error code [17004]; Invalid column type: 1111; nested exception is java.sql.SQLException: Invalid column type: 1111
我在 spring 集成中尝试做的参考代码示例:JAVA DBMS ORACLE 数组可调用语句示例
Referenced Code Example I am trying to do in spring integration: JAVA DBMS ORACLE ARRAY CALLABLE STATEMENT EXAMPLE
Oracle 12c - PL SQL 函数:
Oracle 12c - PL SQL Function:
create or replace function get_dbms_output
return dbmsoutput_linesarray
as
l_output dbmsoutput_linesarray;
l_linecount number;
begin
dbms_output.enable;
dbms_output.put_line('This is a line');
dbms_output.put_line('This is another line');
dbms_output.put_line('This is the last line.');
dbms_output.get_lines(l_output, l_linecount);
if l_output.count > l_linecount then
-- Remove the final empty line above l_linecount
l_output.trim;
end if;
return l_output;
end get_dbms_output;
Spring Context File - 取自 spring 集成 git hub 示例 stored-procedure-oracle.示例代码增加了以下出站网关.
Spring Context File - Taken from spring integration git hub example stored-procedure-oracle . Sample code was augmented with the following outbound gateway.
<bean id="sqlReturnArray" class="org.springframework.data.jdbc.support.oracle.SqlReturnArray"></bean>
<int-jdbc:stored-proc-outbound-gateway
id="outbound-gateway-function-dbms" request-channel="procedureDBMSRequestChannel"
data-source="dataSource"
stored-procedure-name="get_dbms_output"
expect-single-result="true">
<int-jdbc:sql-parameter-definition name="l_output" type="#{T(oracle.jdbc.OracleTypes).ARRAY}" type-name="DBMSOUTPUT_LINESARRAY" direction="OUT" return-type="sqlReturnArray" />
</int-jdbc:stored-proc-outbound-gateway>
JAVA - 更新的字符串转换服务
JAVA - Updated String Conversion Service
public interface StringConversionService {
/**
* Converts a String to Upper Case.
*
* @param stringToConvert The string to convert to upper case
* @return The converted upper case string.
*/
String convertToUpperCase(String stringToConvert);
Integer getNumber();
@Payload("new java.util.Date()")
String[] getLines();
}
JAVA - 主要
final StringConversionService service = context.getBean(StringConversionService.class);
System.out.println("Calling Stored Proc");
String[] dbmsLines = service.getLines();
更新:弹簧配置:- 为 SqlReturnArray 添加了一个 bean.- 为 SqlReturnArray bean 的引用添加了返回类型- 更改 StringConversionService 以使用 String[] 而不是 oracle Array 返回类型.- 更新主类以获取 String[]
Updated: Spring Config: - Added a bean for SqlReturnArray. - Added a return-type for a reference to SqlReturnArray bean - Change the StringConversionService to use a String[] instead of oracle Array return type. - Updated main class to get String[]
推荐答案
你必须添加 return-type
并以某种方式实现它 SqlReturnArray
:
<int-jdbc:sql-parameter-definition name="l_output" type="#{T(oracle.jdbc.OracleTypes).ARRAY}" type-name="DBMSOUTPUT_LINESARRAY" direction="OUT" return-type="sqlReturnArray" />
其中 sqlReturnArray
是那个 SqlReturnArray
的 bean.
Where sqlReturnArray
is a bean of that SqlReturnArray
.
当然,已经将结果作为 String[]
处理,而不是不可用的 ARRAY
.
And of course, handle the result as a String[]
already, not a unavailable there ARRAY
.
这篇关于Spring 集成 - 无法检索 Oracle Array OUT PARAM的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!