Spring StoredProcedure for Oracle cursor
来源:互联网 发布:我打打单软件 编辑:程序博客网 时间:2024/05/16 07:55
http://forum.springsource.org/archive/index.php/t-24915.html
PDA
View Full Version : SqlReturnResultSet vs. SqlOutParameter
lvm
May 12th, 2006, 12:33 PM
I spent some time yesterday trying to call an Oracle stored procedure(function) using the org.springframework.jdbc.object.StoredProcedure class and thought I share what I found.
I got some code from a co-worker that was doing the same thing, but connecting to MS SQLServer. I didn't have his stored proc to look at for clues.
He was using SqlReturnResultSet:
declareParameter(new SqlReturnResultSet("rs" new MessageResultExtractor(msgList)));
My Oracle stored proc accepts no inputs and returns a cursor. When I tried to use the code above I kept getting an error like "invalid number or type of arguments". I looked at the API for SqlReturnResultSet, which states that it is: "Subclass of SqlOutParameter". But looking at the inheritance stack it shows that it does not! So I figured I needed to declare an SqlOutParameter, which conveniently accepts a ResultSetExtractor. So I used:
declareParameter(new SqlOutParameter("rs", OracleTypes.CURSOR, new MessageResultExtractor(msgList))); and it worked!
Note that I had to use the OracleTypes.CURSOR, which I wasn't sure would work.
I don't know if there are ways of getting a resultset from an oracle stored procedure other than a cursor, or maybe there's a way to use SqlReturnResultSet with a cursor, but the SqlOutParameter worked for me.
I got some code from a co-worker that was doing the same thing, but connecting to MS SQLServer. I didn't have his stored proc to look at for clues.
He was using SqlReturnResultSet:
declareParameter(new SqlReturnResultSet("rs" new MessageResultExtractor(msgList)));
My Oracle stored proc accepts no inputs and returns a cursor. When I tried to use the code above I kept getting an error like "invalid number or type of arguments". I looked at the API for SqlReturnResultSet, which states that it is: "Subclass of SqlOutParameter". But looking at the inheritance stack it shows that it does not! So I figured I needed to declare an SqlOutParameter, which conveniently accepts a ResultSetExtractor. So I used:
declareParameter(new SqlOutParameter("rs", OracleTypes.CURSOR, new MessageResultExtractor(msgList))); and it worked!
Note that I had to use the OracleTypes.CURSOR, which I wasn't sure would work.
I don't know if there are ways of getting a resultset from an oracle stored procedure other than a cursor, or maybe there's a way to use SqlReturnResultSet with a cursor, but the SqlOutParameter worked for me.
trisberg
May 15th, 2006, 10:50 AM
What you are doing is the best solution. The way Oracle works is different from SQL Server since a stored procedure has to return the cursor/resultset as an explicitly declared ref-cursor out parameter. There is no such restriction in SQL Server so the way we retreive the resultset using standard JDBC API calls is very different for Oracle as compared to SQL Server.
==========
注意:
在申明参数时应该按照sp的参数顺序,否则会报错:
PLS-00306: wrong number or types of arguments in call to
- Spring StoredProcedure for Oracle cursor
- Spring StoredProcedure Cursor 使用
- Spring StoredProcedure调用Oracle函数各种异常解决方法
- Spring StoredProcedure调用ORACLE存储过程或函数
- 【Spring StoredProcedure调用Oracle函数各种异常解决方法】
- spring storedProcedure 使用
- Oracle/PLSQL: CURSOR FOR Loop
- spring 中StoredProcedure的用法
- oracle.for update,更新游标,cursor
- Oracle中用For Loop 替代Cursor
- ORACLE 关于CURSOR中的FOR UPDATE关键字
- ORACLE中用for in 使用cursor 游标
- oracle cursor
- oracle cursor
- Oracle Cursor
- Oracle Cursor
- Oracle cursor
- oracle cursor
- tel.xls.exe U盘病毒的查杀方法总结
- HDFS命令行操作
- CDatabase::ExecuteSQL()
- 监听控件值的改变
- 进程与线程的区别
- Spring StoredProcedure for Oracle cursor
- iPhone开发--UIActionSheet的按钮不灵敏
- 格式化输出
- poj 1436 Horizontally Visible Segments(线段树 区间的覆盖关系)
- Nginx目录设置访问验证
- Spring+Hibernate配置多数据源
- 【Matlab】数据降维工具箱drtoolbox
- INF SourceDisksNames Section 和 SourceDisksFiles Section详解
- Linux 学习笔记