关于游标打开超出最大数限制,两个地方需要注意:
1、oracle程序包中,一般返回值是cursor,这个不用关闭。
但是中间用到的cursor必须关闭。如下例子:
open result_cursor for sql_statement;
loop
fetch
result_cursor into
v_HouseCode,v_STD,v_DEN,v_type,p_latefee_type;
exit when
result_cursor%notfound;
end loop;
close result_cursor;(这句必须有)
2、; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">都都必须即时关闭。
以下为四个例子:
(1)以下写法不对:
Statement
stmt = null;
for(int
i=0;i<10;i++){
stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs=stmt.executeQuery(sqlStr);
}
stmt.close();
正确的写法应该是:
Statement
stmt = null;
for(int
i=0;i<10;i++){
stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs=stmt.executeQuery(sqlStr);
stmt.close();
}
(2)以下写法不对:
PreparedStatement pstInsert = null;
try{
pstInsert = conn.prepareStatement(sqlStr);
}catch(Exception e){}
正确的写法应该是:
PreparedStatement pstInsert = null;
try{
pstInsert = conn.prepareStatement(sqlStr);
}catch(Exception e){
}finally{
try{
if(pstInsert!=null)
pstInsert.close();
}catch(Exception ee){}
}
(3)以下写法不对:
CallableStatement spStat = null;
spStat =
conn.prepareCall(sqlStr);
spStat.registerOutParameter(1,OracleTypes.CURSOR);
spStat.execute();
正确的写法应该是:
CallableStatement spStat = null;
spStat =
conn.prepareCall(sqlStr);
spStat.registerOutParameter(1,OracleTypes.CURSOR);
spStat.execute();
ResultSetrs =
(ResultSet)spStat.getObject(1);(关闭程序包返回的cursor,必不可少)
rs.close();
spStat.close();
(4)以下写法不对:
Statement
stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet
rs=stmt.executeQuery(sqlStr);
while(rs.next){
}
正确的写法应该是:
Statement
stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet
rs=stmt.executeQuery(sqlStr);
while(rs.next){
}
rs.close();
stmt.close();
3、一旦出现该错误,一般是在;">中无法快速定位,检测方法为:
selectq.sql_text
from v$open_cursor o, v$sql q
where q.hash_value=o.hash_value
order by q.sql_text;
执行上述的sql语句,查看执行次数特别多的sql语句,然后就能反向追踪,定位到发生问题的;">类。