java ORA-01002: 读取违反顺序[急急急!!!!]

[toss2000]

我创建了一个dblink从oracle9i链接到sqlserver2000,在存储过程了构造一条查询sql,结果每次只能取到10条数据,之后就报ORA-01002: 读取违反顺序错误,我在java代码中已经加了conn.setAutoCommit(false); 可是仍然报错,不加的话,连10条都没有,请高人指点,查绝了baidu\google都没有找到合适的答案

[toss2000]

这是我的存储过程
procedure createSmsData(v_result out ref_cur,v_deptId in varchar2)is
tempSql varchar2(2000) := '';
begin


tempSql:='select t."leaveTime" std,m."psc" userid,m."psn" username
from task@REMOTESQL2000 t,chedule@REMOTESQL2000 m
where t."confirm"=0 and t."deptId"='''||v_deptId||'''
and t."tskmark"=m."tsk"
open v_result for tempSql;

end;

其中v_result out ref_cur中的ref_cur是在包中定义的游标变量type ref_cur is ref cursor;
返回的游标,我在java中做循环取值java部分代码
conn.setAutoCommit(false);
……
rs = (ResultSet) cstmt.getObject(1);
while (rs.next())
{
……[这里只是简单的取值]
}
……
finally
{
free(conn, cstmt, rs);--调用方法关闭conn、cstmt、rs
}
不知道应该还要修改什么地方

[minitoy]

不知道java里怎么用cursor.不过plsql里是这样用的.
open cursorname;
loop
fetch cursorname into varlist;
exit when cursorname%notfound;
do something;
end loop;
close cursorname;

[minitoy]

ORA-01002 fetch out of sequence

Cause: In a host language program, a FETCH call was issued out of sequence. A successful parse-and-execute call must be issued before a fetch. This can occur if an attempt was made to FETCH from an active set after all records have been fetched. This may be caused by fetching from a SELECT FOR UPDATE cursor after a commit. A PL/SQL cursor loop implicitly does fetches and may also cause this error.

Action: Parse and execute a SQL statement before attempting to fetch the data.

两种可能:1.cursor里的记录已经全部取过了,你继续fetch会报错.
2.cursor已经关闭了,你还在fetch也报错.

[toss2000]

[Quote=引用 8 楼 minitoy 的回复:]
ORA-01002 fetch out of sequence

Cause: In a host language program, a FETCH call was issued out of sequence. A successful parse-and-execute call must be issued before a fetch. This can occur if an a……
[/Quote]

这个文档我找过了,但是我弄不明白,我的存储过程,哪里导致了这2个问题

[minitoy]

哦,这样啊,找个java高手给你看看吧.我是门外汉.

[madFatso]

Statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
加上结果集可滚动试试

[toss2000]

[Quote=引用 11 楼 madfatso 的回复:]
Statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
加上结果集可滚动试试
[/Quote]
加了仍然报错……

[ChDw]

while (rs.next())
{
……[这里只是简单的取值]
}

就是这里错误了！部分的SQL Server驱动，在读取过程中必须按列的顺序来读，并且不允许重复读取
即：
select id, name from a;

while(rs.next()) {
rs.getString("name");//这里不会错
rs.getString("id");//这里就会错了，因为你先读取了第二列再读取第一列
rs.getString("name");//同样会错，因为重复读取了一次
}

[luoyoumou]

http://space.itpub.net/7607759/viewspace-201899

-- 看一下“三思”遇到过类似的问题！
-- 可能是你的哪个与此表相关的存储过程的逻辑有问题！

[dawugui]

只能提供英文说明，敬请参考：

ORA-01002 fetch out of sequence

Cause: In a host language program, a FETCH call was issued out of sequence. A successful parse-and-execute call must be issued before a fetch. This can occur if an attempt was made to FETCH from an active set after all records have been fetched. This may be caused by fetching from a SELECT FOR UPDATE cursor after a commit. A PL/SQL cursor loop implicitly does fetches and may also cause this error.

Action: Parse and execute a SQL statement before attempting to fetch the data.

[toss2000]

我在想是不是我的透明网观有问题

[minitoy]

莫非用到cursor了?

[toss2000]

[Quote=引用 4 楼 minitoy 的回复:]
莫非用到cursor了?
[/Quote]
对确实用到了cursor……，是不是要对cursor做一些特殊处理？

[toss2000]

[Quote=引用 13 楼 chdw 的回复:]
while (rs.next())
{
……[这里只是简单的取值]
}

就是这里错误了！部分的SQL Server驱动，在读取过程中必须按列的顺序来读，并且不允许重复读取
即：
select id, name from a;

while(rs.next()) {
rs.getString("name");//这里不会错
rs.getString("id");//……
[/Quote]

唉,我现在真是怀疑是我的透明网关的问题了,换了一台机器作了同样配置,结果就没有出现问题……