oracle的dblink

今 天解决个问题，建了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来管理事务了，所以这个问题没有报露出来。