我有一些使用CallableStatement的JDBC代码可以正常工作。我一直在尝试将其转换为使用DataSource、JdbcTemplate和SimpleJdbcCall的Spring框架。我已经尝试了几乎所有我能找到的Spring文档中的教程、示例和片段。但即便进行了调整,所有Spring解决方案都产生了相同的结果:
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call UPCLSCH.P_GET_CLASS_SCHEDULE()}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'P_GET_CLASS_SCHEDULE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
这是正在准备语句的日志部分:
2015-12-29 17:17:18 DEBUG SimpleJdbcCall:214 - Added declared parameter for [p_get_class_schedule]: p_classsched_ref_out
2015-12-29 17:17:18 DEBUG SimpleJdbcCall:214 - Added declared parameter for [p_get_class_schedule]: p_term
2015-12-29 17:17:18 DEBUG SimpleJdbcCall:214 - Added declared parameter for [p_get_class_schedule]: p_scauid
2015-12-29 17:17:18 DEBUG SimpleJdbcCall:214 - Added declared parameter for [p_get_class_schedule]: p_pidm
2015-12-29 17:17:18 DEBUG SimpleJdbcCall:336 - JdbcCall call not compiled before execution - invoking compile
2015-12-29 17:17:18 DEBUG DataSourceUtils:110 - Fetching JDBC Connection from DataSource
2015-12-29 17:17:18 DEBUG DriverManagerDataSource:162 - Creating new JDBC DriverManager Connection to [jdbc:oracle:thin:@umadmn.umt.edu:7895:ADMNRED]
2015-12-29 17:17:21 DEBUG CallMetaDataProviderFactory:123 - Using org.springframework.jdbc.core.metadata.OracleCallMetaDataProvider
2015-12-29 17:17:21 DEBUG CallMetaDataProvider:278 - Retrieving metadata for UPCLSCH/AP_ADMN/P_GET_CLASS_SCHEDULE
2015-12-29 17:17:22 DEBUG DataSourceUtils:332 - Returning JDBC Connection to DataSource
2015-12-29 17:17:22 DEBUG SimpleJdbcCall:304 - Compiled stored procedure. Call string is [{call UPCLSCH.P_GET_CLASS_SCHEDULE()}]
2015-12-29 17:17:22 DEBUG SimpleJdbcCall:282 - SqlCall for procedure [p_get_class_schedule] compiled
2015-12-29 17:17:22 DEBUG SimpleJdbcCall:385 - The following parameters are used for call {call UPCLSCH.P_GET_CLASS_SCHEDULE()} with: {}
2015-12-29 17:17:22 DEBUG JdbcTemplate:937 - Calling stored procedure [{call UPCLSCH.P_GET_CLASS_SCHEDULE()}]
2015-12-29 17:17:22 DEBUG DataSourceUtils:110 - Fetching JDBC Connection from DataSource
2015-12-29 17:17:22 DEBUG DriverManagerDataSource:162 - Creating new JDBC DriverManager Connection to [jdbc:oracle:thin:@xxxxx.xxx.xxx:7895:PRIVATE]
2015-12-29 17:17:24 DEBUG DataSourceUtils:332 - Returning JDBC Connection to DataSource
这是一段正常工作的JDBC代码(不包括连接细节):
private static List<ScheduledClass> callOracleStoredProcCURSORParameter() throws SQLException {
Connection connection = null;
CallableStatement callableStatement = null;
ResultSet rs = null;
List<ScheduledClass> scheduledClassList = new ArrayList<ScheduledClass>();
String getDBUSERCursorSql = "{call upclsch.p_get_class_schedule (?, ?, ?, ?)}";
try {
connection = getApConnection();
callableStatement = connection.prepareCall(getDBUSERCursorSql);
callableStatement.registerOutParameter("p_classsched_ref_out", OracleTypes.CURSOR);
callableStatement.setString("p_term", "201570"); //term code
callableStatement.setString("p_scauid", "rs213498");
callableStatement.setString("p_pidm", null);
callableStatement.executeUpdate();
rs = (ResultSet) callableStatement.getObject("p_classsched_ref_out");
while (rs.next()) {
ScheduledClass sc = new ScheduledClass();
sc.setCourseNumber(rs.getString("subject_code") + rs.getString("course_number"));
sc.setCourseTitle(rs.getString("course_title"));
scheduledClassList.add(sc);
}
} catch (SQLException e) {
e.printStackTrace();
}
return scheduledClassList;
}
以下是我不工作的Spring代码(注意注释掉的部分,当将“in”传递给sjc.execute()时会产生相同的结果):
public void setDataSource(DataSource dataSource){
this.jt = new JdbcTemplate(dataSource);
jt.setResultsMapCaseInsensitive(true);
sjc = new SimpleJdbcCall(jt)
.withCatalogName("upclsch")
.withProcedureName("p_get_class_schedule");
}
public Map<String, Object> execute(String termCode, String netId){
sjc.useInParameterNames("p_term", "p_scauid", "p_pidm")
.declareParameters(new SqlOutParameter("p_classsched_ref_out", OracleTypes.CURSOR),
new SqlParameter("p_term", OracleTypes.VARCHAR),
new SqlParameter("p_scauid", OracleTypes.VARCHAR),
new SqlParameter("p_pidm", OracleTypes.VARCHAR));
// SqlParameterSource in = new MapSqlParameterSource()
// .addValue("p_scauid", netId, OracleTypes.VARCHAR)
// .addValue("p_term", termCode, OracleTypes.VARCHAR)
// .addValue("p_classsched_ref_out", OracleTypes.CURSOR);
Map<String, Object> results = sjc.execute();
return results;
}
我似乎无法在TRACE或DEBUG级别上获取任何额外的信息以查看我的参数是否被错误地排序。因此,我正在寻求任何已经使用这种技术完成此任务的人的帮助。我不想扩展StoredProcedure,因为Spring文档建议在3.2版本中这样做。
sjc.execute(in)
,而不是在代码中看到的空调用。 - user3596751Map<String, Object> results = sjc.execute(termCode, netId, null);
日志与上面的相同。 - user3596751sjc:SimpleJdbcCall@1658 termCode: "201570" netId: "rs213498"
- user35967512015-12-29 21:53:38 DEBUG CallMetaDataProvider:278 - 检索 UPCLSCH/AP_ADMN/P_GET_CLASS_SCHEDULE 的元数据 2015-12-29 21:53:39 DEBUG DataSourceUtils:332 - 将 JDBC 连接返回到数据源 2015-12-29 21:53:39 DEBUG SimpleJdbcCall:304 - 编译存储过程。调用字符串为 [{call UPCLSCH.P_GET_CLASS_SCHEDULE()}] 2015-12-29 21:53:39 DEBUG SimpleJdbcCall:282 - 编译存储过程 [p_get_class_schedule] 的 SqlCall 2015-12-29 21:53:39 DEBUG SimpleJdbcCall:385 - 使用以下参数进行调用 {call UPCLSCH.P_GET_CLASS_SCHEDULE()}:{}
- user3596751