请教一下Oracle抛出 Cause: java.sql.SQLException: Ref 游标无效 的原因。这是Ibatis调用Oracle存储过程的一个错误,在PL/SQL中这个过程测试是无误的。但在Java通过Ibatis调用会抛出以上错误。Ibatis设置: <parameterMap id="spLoginMemberParameters" class="java.util.HashMap">
<parameter property="u_name" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="u_pwd" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="u_loginip" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="iswritelog" jdbcType="DECIMAL" javaType="java.lang.Integer" mode="IN" />
<parameter property="isloginaddintegrral" jdbcType="DECIMAL" javaType="java.lang.Integer" mode="IN" />
<parameter property="u_result" jdbcType="DECIMAL" javaType="java.lang.Integer" mode="OUT" />
<parameter property="re_cursor" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet"
resultMap="loginCamemberResult" mode="OUT" />
</parameterMap>
<procedure id="SPMEMBER.spUserLogin" parameterMap="spLoginMemberParameters">
<![CDATA[
{call pkg_reg_login.sp_UserLogin(?,?,?,?,?,?,?)}
]]>
</procedure>
部分存储过程: PROCEDURE sp_UserLogin(
u_name in WATCHVIDEO.camember.uname%type,
u_pwd in WATCHVIDEO.camember.uname%type,
u_loginip in WATCHVIDEO.camember.lastloginip%type,
iswritelog in number,
isloginaddintegrral in number,
u_result out number,
re_cursor out WATCHVIDEO.pkg_reg_login.T_MEMBER_CURSOR
)
is
.......
rowinfo camember%rowtype;
begin
u_result := 0;
parms_date := sysdate;
........
if rowinfo.members_id is null then
u_result := 1;
raise_application_error(-21111,'不存在这个用户');
else
if rowinfo.upwd != u_pwd then
u_result := 2;
raise_application_error(-21111,'密码错误');
end if;
end if;
.........
open re_cursor for
select * from WATCHVIDEO.vuserlogininfo
where WATCHVIDEO.pkg_view_param.set_param(rowinfo.members_id)=rowinfo.members_id;
u_result := 0;
EXCEPTION
when others then rollback;
u_result := 5;
报错信息:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/webvideo/vo/SpMember.xml.
--- The error occurred while applying a parameter map.
--- Check the SPMEMBER.spLoginMemberParameters.
--- Check the output parameters (retrieval of output parameters failed).
--- Cause: java.sql.SQLException: Ref 游标无效spLoginMemberParameters 这个map的传入和值、sqlmap都已检查过,是没问题的。当出现问题后,我把游标删除,仅返回数字时可正常使用。但加上游标就抛错,另外在测试代码的环境中,过程并没有执行到open re_cursor for,我想是不是因为没有打开游标,导致oracle返回一个null的游标而错误呢?因为oracle不熟悉,所以请大家指教一下小弟,谢谢了!
<parameter property="u_name" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="u_pwd" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="u_loginip" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="iswritelog" jdbcType="DECIMAL" javaType="java.lang.Integer" mode="IN" />
<parameter property="isloginaddintegrral" jdbcType="DECIMAL" javaType="java.lang.Integer" mode="IN" />
<parameter property="u_result" jdbcType="DECIMAL" javaType="java.lang.Integer" mode="OUT" />
<parameter property="re_cursor" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet"
resultMap="loginCamemberResult" mode="OUT" />
</parameterMap>
<procedure id="SPMEMBER.spUserLogin" parameterMap="spLoginMemberParameters">
<![CDATA[
{call pkg_reg_login.sp_UserLogin(?,?,?,?,?,?,?)}
]]>
</procedure>
部分存储过程: PROCEDURE sp_UserLogin(
u_name in WATCHVIDEO.camember.uname%type,
u_pwd in WATCHVIDEO.camember.uname%type,
u_loginip in WATCHVIDEO.camember.lastloginip%type,
iswritelog in number,
isloginaddintegrral in number,
u_result out number,
re_cursor out WATCHVIDEO.pkg_reg_login.T_MEMBER_CURSOR
)
is
.......
rowinfo camember%rowtype;
begin
u_result := 0;
parms_date := sysdate;
........
if rowinfo.members_id is null then
u_result := 1;
raise_application_error(-21111,'不存在这个用户');
else
if rowinfo.upwd != u_pwd then
u_result := 2;
raise_application_error(-21111,'密码错误');
end if;
end if;
.........
open re_cursor for
select * from WATCHVIDEO.vuserlogininfo
where WATCHVIDEO.pkg_view_param.set_param(rowinfo.members_id)=rowinfo.members_id;
u_result := 0;
EXCEPTION
when others then rollback;
u_result := 5;
报错信息:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/webvideo/vo/SpMember.xml.
--- The error occurred while applying a parameter map.
--- Check the SPMEMBER.spLoginMemberParameters.
--- Check the output parameters (retrieval of output parameters failed).
--- Cause: java.sql.SQLException: Ref 游标无效spLoginMemberParameters 这个map的传入和值、sqlmap都已检查过,是没问题的。当出现问题后,我把游标删除,仅返回数字时可正常使用。但加上游标就抛错,另外在测试代码的环境中,过程并没有执行到open re_cursor for,我想是不是因为没有打开游标,导致oracle返回一个null的游标而错误呢?因为oracle不熟悉,所以请大家指教一下小弟,谢谢了!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货