内部错误：图像是一个集合图像，期望与 Oracle ADT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6140644/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Internal Error: Image is a collection image,expecting ADT with Oracle
提问by pgreen2
I am trying to call a stored function on an oracle database with spring-jdbc.
我正在尝试使用 spring-jdbc 调用 oracle 数据库上的存储函数。
This is the stored function I am trying to call:
这是我试图调用的存储函数：
FUNCTION GET_RESOURCE_LIST RETURN RESOURCE_TAB;
Next is the definition of resource_tab
接下来是resource_tab的定义
TYPE RESOURCE_TAB AS TABLE OF TRESOURCE;
Next is the definition of tresource
接下来是资源的定义
TYPE TRESOURCE AS OBJECT(
RESOURCE_ID NUMBER(10,0),
RESOURCE_NAME VARCHAR2(100)
)
The calling code
调用代码
final SimpleJdbcCall call = new SimpleJdbcCall(idmJdbcTemplate).withFunctionName("get_resource_list").declareParameters(new SqlOutParameter(
"RETURN", OracleTypes.STRUCT,
"RESOURCE_TAB",
new SqlReturnType() {
@Override
public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName) throws SQLException {
final Struct s = (Struct)cs.getObject(paramIndex);
final Object[] attr = s.getAttributes();
return attr[1];
}
}));
call.compile();
final Collection<String> resources = call.executeFunction(Collection.class);
Last is the stack trace that I am receiving:
最后是我收到的堆栈跟踪：
org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call WVUID.IDM_REPO_MANUAL.GET_RESOURCE_LIST()}]; SQL state [99999]; error code [17001]; Internal Error: Image is a collection image,expecting ADT; nested exception is java.sql.SQLException: Internal Error: Image is a collection image,expecting ADT
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:969)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1003)
Truncated. see log file for complete stacktrace
Caused By: java.sql.SQLException: Internal Error: Image is a collection image,expecting ADT
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
Truncated. see log file for complete stacktrace
>
at org.springframework.jdbc.core.JdbcTemplate.doInCallableStatement(JdbcTemplate.java:1015)
at org.springframework.jdbc.core.JdbcTemplate.doInCallableStatement(JdbcTemplate.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:953)
... 62 more
回答by Mihai
I have the same problem, and I spent a day to fix it. Thinking about error "Image is a collection image", instead of
我有同样的问题，我花了一天时间解决它。考虑错误“图像是收藏图像”，而不是
OracleTypes.STRUCTit must be OracleTypes.ARRAY.
OracleTypes.STRUCT它必须是OracleTypes.ARRAY。
About your getTypeValue(...) method I can't say is it right or not, but I will show how parse the resultset (maybe it is and other way, please write it):
关于你的 getTypeValue(...) 方法我不能说它对不对，但我会展示如何解析结果集（也许是和其他方式，请写出来）：
@Override
public Object getTypeValue(CallableStatement cs, int paramIndex,
int sqlType, String typeName) throws SQLException {
ARRAY struct = (ARRAY) cs.getObject(paramIndex);
ResultSet rs = struct.getResultSet();
while (rs.next()) {
Object rowIndex = rs.getObject(1);
oracle.sql.STRUCT row = (oracle.sql.STRUCT) rs.getObject(2);
if (row != null) {
Object[] cols = row.getAttributes();
System.out.print(rowIndex + ": ");
for (Object col : cols) {
System.out.print(col + " ");
}
System.out.println();
}
}
return "construct your data structure above and return here";
}
the another way is
另一种方式是
@Override
public Object getTypeValue(CallableStatement cs, int paramIndex,
int sqlType, String typeName) throws SQLException {
ARRAY array = (ARRAY) cs.getObject(paramIndex);
Object[] rows = (Object[])array.getArray();
for(Object row : rows){
Object[] cols = ((oracle.sql.STRUCT)row).getAttributes();
for (Object col : cols) {
System.out.print(col + " ");
}
System.out.println();
}
return "construct your data structure above and return here";
}
回答by Salandur
The problem is with the declaration of the output parameter
问题在于输出参数的声明
final SimpleJdbcCall call = new SimpleJdbcCall(idmJdbcTemplate).withFunctionName("get_resource_list").declareParameters(new SqlOutParameter(
"RETURN", OracleTypes.STRUCT,
"RESOURCE_TAB",
new SqlReturnType() <etc>
The return type (TYPE RESOURCE_TAB AS TABLE OF TRESOURCE) is not a OracleTypes.STRUCT(that is reserved for Object Types) but an OracleTypes.ARRAY.
返回类型 ( TYPE RESOURCE_TAB AS TABLE OF TRESOURCE) 不是 a OracleTypes.STRUCT（为对象类型保留的）而是OracleTypes.ARRAY.
