Spring StoredProcedure for Oracle cursor

全栈程序员-站长 • 2022年7月26日下午10:16 • 未分类 • 阅读 3

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元售后保障童叟无欺

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.

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 　

发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/164516.html原文链接：https://javaforall.net