public List obtainPartNumber(String leftobid, Connection con, PreparedStatement ps) { ResultSet rs = null;
String tableName = "";
String partnumber = "";
String obid = "";
String sql = "" ; try { sql = "select /*+RULE*/ CLASS2,RIGHTfrom PDMM31A.ASSMSTRC where LEFT=?";
ps = con.prepareStatement(sql);
ps.setString(1, leftobid);
rs = ps.executeQuery();
while (rs.next()) {
String SSql = "select /*+RULE*/OBID from PDMM31A." + rs.getString("CLASS2") + " where OBID=?";
ps =con.prepareStatement(SSql);
ps.setString(1,rs.getString("RIGHT"));
ResultSet rss = ps.executeQuery();
if (rss.next())
obid= rss.getString("OBID ");
close(rss);
close(ps);
obtainPartNumber(obid, newvalue, condition, flag, con, ps);
close(rss);
close(ps);
}
} catch (Exception e) { e.printStackTrace(); } finally { close(ps);
close(rs);
}
return null;}上面是一段递归代码,在执行3000多次时报错,错误代码指向上面红色行:
java.sql.SQLException: ORA-01000: 超出打开游标的最大数 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)请问各位我该如何修改这个错误呢??
String tableName = "";
String partnumber = "";
String obid = "";
String sql = "" ; try { sql = "select /*+RULE*/ CLASS2,RIGHTfrom PDMM31A.ASSMSTRC where LEFT=?";
ps = con.prepareStatement(sql);
ps.setString(1, leftobid);
rs = ps.executeQuery();
while (rs.next()) {
String SSql = "select /*+RULE*/OBID from PDMM31A." + rs.getString("CLASS2") + " where OBID=?";
ps =con.prepareStatement(SSql);
ps.setString(1,rs.getString("RIGHT"));
ResultSet rss = ps.executeQuery();
if (rss.next())
obid= rss.getString("OBID ");
close(rss);
close(ps);
obtainPartNumber(obid, newvalue, condition, flag, con, ps);
close(rss);
close(ps);
}
} catch (Exception e) { e.printStackTrace(); } finally { close(ps);
close(rs);
}
return null;}上面是一段递归代码,在执行3000多次时报错,错误代码指向上面红色行:
java.sql.SQLException: ORA-01000: 超出打开游标的最大数 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)请问各位我该如何修改这个错误呢??
解决方案 »
- oracle10数据如何导入到oracle11?
- 如何通过sql语句更新一个没有连接条件的表
- 满分求救:复杂行列转换
- 送分100分:ORACLE10G中的都有什么帐号、权限有什么区别
- 数据库表导入到excel表中的问题.急急.
- sql 语句
- union简单问题
- 请问这个SQL语句怎么写?谢谢了
- develop form的菜鸟问题
- 请问各位我在ORACLE9 安装时候后创建的全局数据库名是test,安装成功后,再添加新的数据库xxx的时候,在创建数据字典和创建jvm的时候出现
- imp导入数据后,出现一个函数和两个存储过程打了红叉?谁看看
- 如何把oracle查询出来的数据,整体放到excel里面?如何把查询出来的数据保存为csv/html文件?
http://www.blogjava.net/snoopy/archive/2005/01/27/744.html
obtainPartNumber(obid, newvalue, condition, flag, con, ps);
应该是:
obtainPartNumber(obid, con, ps);
那么我应该如何的改呢,先关闭游标,再做递归恐怕不行吧??
我觉得增大open_cursors不是解决的根本办法,应该考虑改进你的程序算法设计.
PreparedStatement pss = null ;
String tableName = "";
String partnumber = "";
String obid = "";
String sql = "" ; try { sql = "select /*+RULE*/ CLASS2,RIGHTfrom PDMM31A.ASSMSTRC where LEFT=?";
pss = con.prepareStatement(sql);
pss.setString(1, leftobid);
rs = pss.executeQuery();
while (rs.next()) {
String SSql = "select /*+RULE*/OBID from PDMM31A." + rs.getString("CLASS2") + " where OBID=?";
ps =con.prepareStatement(SSql);
ps.setString(1,rs.getString("RIGHT"));
ResultSet rss = ps.executeQuery();
if (rss.next())
obid= rss.getString("OBID ");
close(rss);
close(pss);
obtainPartNumber(obid, newvalue, condition, flag, con, ps);
}
} catch (Exception e) { e.printStackTrace(); } finally { close(ps);
close(rs);
}
return null;}其就是就是多定义了一个PreparedStatement,这样便减少了游标的打开数量。
二楼推荐的文章写的不错,遇到类似问题的朋友可以参考,结帖,给分!