这两天在做一个数据分发的程序.由一张中间表的数据分发到不同的子表当中.(这些子表的结构与中间表的结构完全相同).
但每次程序分发150来个表后就抛了异常了...
报游标不足了.
在网上查了很多,说是一个Statement 对象将会消耗一个游标.
但我这是做了一个外部的对象啊.放在循环之外.不懂为什么也会这样!而且数据库我也将游标调到了1000个...
请各位帮帮忙...谢了!
具体的代码和异常都贴出来.
数据库是Oracle 10g 的.
public void dispenseGPS(){
Connection conn=null;
Statement st_selete=null;
Statement st_delete=null;
try {
conn=ConnectionPool.openConn();
//conn.setAutoCommit(false);
List<String> telsim=this.query();//获取需要分发的所有设备通讯号
int count=this.telsim.size();//获取需要分发数据的设备数量
String sim="";
String sql_select="";
String sql_delete="";
System.out.println("开始进行分发设备:"+telsim);
long begin=System.currentTimeMillis();
for (int i = 0; i <count; i++) {
sim=this.telsim.get(i);
sql_select="insert all into ydgps_extend.dg_car_"+sim+" select YD_ID, YD_TIME, LONGITUDE, LATITUDE, VELOCITY, ANGLE, ALARM, ACC_SIGN, TAXISTATE, M_RECTYPE, M_PLACE, LOCATE, M_OILSCALE, M_OILNUM, LIGHT, RUNMILE, COMPONENT, SENSOR, ALARMNOTE, SIGNIN, SIGNNO, GPRS, GSMSIGNAL, EQUIPMENT, INTFLAG from ydgps.dg_location_middle where yd_sim='"+sim+"'";
sql_delete="delete from ydgps.dg_location_middle dlm where dlm.yd_sim='"+sim+"'";
st_selete=conn.createStatement();
st_delete=conn.createStatement();
st_selete.executeUpdate(sql_select);
st_delete.executeUpdate(sql_delete);
System.out.println("通讯号为:"+telsim+" 设备数据分发完成!"+" 第 "+(i+1) +"个!");
}
System.out.println("分发数据完成,共花费 [ "+(System.currentTimeMillis()-begin)+" ] 毫秒!");
} catch (SQLException e) {
e.printStackTrace();
}finally{
isDispense=true;
try {
if(st_selete!=null){
st_selete.close();
}
if(st_delete!=null){
st_delete.close();
}
ConnectionPool.closeConn();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
但每次程序分发150来个表后就抛了异常了...
报游标不足了.
在网上查了很多,说是一个Statement 对象将会消耗一个游标.
但我这是做了一个外部的对象啊.放在循环之外.不懂为什么也会这样!而且数据库我也将游标调到了1000个...
请各位帮帮忙...谢了!
具体的代码和异常都贴出来.
数据库是Oracle 10g 的.
public void dispenseGPS(){
Connection conn=null;
Statement st_selete=null;
Statement st_delete=null;
try {
conn=ConnectionPool.openConn();
//conn.setAutoCommit(false);
List<String> telsim=this.query();//获取需要分发的所有设备通讯号
int count=this.telsim.size();//获取需要分发数据的设备数量
String sim="";
String sql_select="";
String sql_delete="";
System.out.println("开始进行分发设备:"+telsim);
long begin=System.currentTimeMillis();
for (int i = 0; i <count; i++) {
sim=this.telsim.get(i);
sql_select="insert all into ydgps_extend.dg_car_"+sim+" select YD_ID, YD_TIME, LONGITUDE, LATITUDE, VELOCITY, ANGLE, ALARM, ACC_SIGN, TAXISTATE, M_RECTYPE, M_PLACE, LOCATE, M_OILSCALE, M_OILNUM, LIGHT, RUNMILE, COMPONENT, SENSOR, ALARMNOTE, SIGNIN, SIGNNO, GPRS, GSMSIGNAL, EQUIPMENT, INTFLAG from ydgps.dg_location_middle where yd_sim='"+sim+"'";
sql_delete="delete from ydgps.dg_location_middle dlm where dlm.yd_sim='"+sim+"'";
st_selete=conn.createStatement();
st_delete=conn.createStatement();
st_selete.executeUpdate(sql_select);
st_delete.executeUpdate(sql_delete);
System.out.println("通讯号为:"+telsim+" 设备数据分发完成!"+" 第 "+(i+1) +"个!");
}
System.out.println("分发数据完成,共花费 [ "+(System.currentTimeMillis()-begin)+" ] 毫秒!");
} catch (SQLException e) {
e.printStackTrace();
}finally{
isDispense=true;
try {
if(st_selete!=null){
st_selete.close();
}
if(st_delete!=null){
st_delete.close();
}
ConnectionPool.closeConn();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
st_delete=conn.createStatement();
你循环里每次执行以上两句,就创建了2个statement对象,可以把这两句放在for外面
另外,这种需求可以用触发器实现,当delete的时候触发insert操作
写过一个Job定时调用存储过程.由于子表和中间不在一个用户下面.运行不了!.
sim=this.telsim.get(i);
sql_select="insert all into ydgps_extend.dg_car_"+sim+" select YD_ID, YD_TIME, LONGITUDE, LATITUDE, VELOCITY, ANGLE, ALARM, ACC_SIGN, TAXISTATE, M_RECTYPE, M_PLACE, LOCATE, M_OILSCALE, M_OILNUM, LIGHT, RUNMILE, COMPONENT, SENSOR, ALARMNOTE, SIGNIN, SIGNNO, GPRS, GSMSIGNAL, EQUIPMENT, INTFLAG from ydgps.dg_location_middle where yd_sim='"+sim+"'";
sql_delete="delete from ydgps.dg_location_middle dlm where dlm.yd_sim='"+sim+"'";
st_selete=conn.createStatement();
st_selete.executeUpdate(sql_select);
st_selete.executeUpdate(sql_delete);
st_selete.close();
System.out.println("通讯号为:"+telsim+" 设备数据分发完成!"+" 第 "+(i+1) +"个!");
}
Statement st_selete=null;
Statement st_delete=null;
定义在外面就只一个对象了.呵呵..
谢谢各位了!