今 天解决个问题,建了oracle到sqlserver的dblink,在单表查询的时候没问题,但是如果结合oracle本地表和远程的 sqlserver表或者针对sqlserver的表进行数据库分页操作的时候会发现报告ORA-01002的错误,说什么读取违反顺序,从文字上看不出 问题出现在哪里,google一下:http://ora-01002.ora-code.com/
ORA-01002: | fetch out of sequence |
Cause: | This error means that a fetch has been attempted from a cursor which is no longer valid. Note that a PL/SQL cursor loop implicitly does fetches, and thus may also cause this error. There are a number of possible causes for this error, including: 1) Fetching from a cursor after the last row has been retrieved and the ORA-1403 error returned. 2) If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued will return the error. 3) Rebinding any placeholders in the SQL statement, then issuing a fetch before reexecuting the statement. |
Action: | 1) Do not issue a fetch statement after the last row has been retrieved - there are no more rows to fetch. 2) Do not issue a COMMIT inside a fetch loop for a cursor that has been opened FOR UPDATE. 3) Reexecute the statement after rebinding, then attempt to fetch again. |
感 觉我们的问题应该是action1,也就是游标在失效的状态下被操作了,但是我的sql语句只select了一次,咋回事?应该是dblink在搞怪,实 际上我们写了一个select,但是在执行的时候oralce会先去select一下sqlserver,拿到数据后我们在select刚才拿到的数据 集,而当我们再去取这个数据集的游标时这个游标时这个游标已经失效,因为oracle认为这是两个操作,为啥诶,因为在我们的程序里对于select的操 作默认为自动提交事务,所以对于这种多个select有先后顺序的查询应该放到一个事务中也就是让oracle知道我们这么一堆select是有关系的, select完一个不行得都select完了才能歇着。加上code:conn.setAutoCommit(false);......,conn.commit();run it.....ok!
之前用spring+hibernate的方式的时候没有问题,但是在老系统上用就不行,原来所有service层的调用都用spring来管理事务了,所以这个问题没有报露出来。