在线程中执行一个存储过程（oracle），运行一段时间之后就会报错

2012-10-16 09:50:34,575 [Thread-3] INFO [com.tg.thread.util.LoggerUtil] - java.sql.SQLException: ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01000: 超出打开游标的最大数
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01000: 超出打开游标的最大数
ORA-01000: 超出打开游标的最大数
 
  at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
   at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
   at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:120)
   at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:586)
   at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:385)
   at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:413)
   at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:119)
   at oracle.jdbc.driver.OracleCallableStatement.<init>(OracleCallableStatement.java:77)
   at oracle.jdbc.driver.OracleCallableStatement.<init>(OracleCallableStatement.java:48)
   at oracle.jdbc.driver.OracleConnection.privatePrepareCall(OracleConnection.java:1134)
   at oracle.jdbc.driver.OracleConnection.prepareCall(OracleConnection.java:988)
   at sun.reflect.GeneratedMethodAccessor108.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:592)
   at org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:74)
   at $Proxy1.prepareCall(Unknown Source)
   at com.tg.thread.busi.period.dao.CmPeriodInfoDaoImpl.execProce_strpPeriod(CmPeriodInfoDaoImpl.java:40)
   at com.tg.thread.busi.period.service.PeriodService.proess_StopPeriod(PeriodService.java:37)
   at com.tg.thread.test.TestThread.run(TestThread.java:62)
 
2012-10-16 09:50:34,575 [Thread-3] INFO [com.tg.thread.util.LoggerUtil] - PeriodService--> 期次过期过程 执行失败 数据库异常，过程返回null
2012-10-16 09:50:34,575 [Thread-3] INFO [com.tg.thread.util.LoggerUtil] - 正在执行的查询hql： from CmPeriodInfoBean t where (t.istate=2 or t.istate=3) and rownum<15 order by t.id desc 
2012-10-16 09:50:34,576 [Thread-3] INFO [com.tg.thread.util.LoggerUtil] - org.hibernate.exception.GenericJDBCException: could not execute query
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
   at org.hibernate.loader.Loader.doList(Loader.java:2235)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
   at org.hibernate.loader.Loader.list(Loader.java:2124)
   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
   at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
   at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
   at com.tg.thread.base.BaseDaoImpl.findObjects(BaseDaoImpl.java:92)
   at com.tg.thread.busi.period.service.PeriodService.getNoAwardsPeriods(PeriodService.java:204)
   at com.tg.thread.test.TestThread.run(TestThread.java:64)
Caused by: java.sql.SQLException: ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01000: 超出打开游标的最大数
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01000: 超出打开游标的最大数
ORA-01000: 超出打开游标的最大数
 
  at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
   at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
   at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:120)
   at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:586)
   at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:385)
   at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:413)
   at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:119)
   at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:92)
   at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:950)
   at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:802)
   at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
   at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
   at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
   at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1577)
   at org.hibernate.loader.Loader.doQuery(Loader.java:696)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
   at org.hibernate.loader.Loader.doList(Loader.java:2232)
   ... 10 more



--下面是存储过程的代码
create or replace procedure pro_stopPeriod--期次过期截至
(

   po_ErrCode Out Int, --错误代码
   po_ErrDesc Out varchar2, --错误描述
   po_orderAccoutSum Out Int --重置订单总数
)
is
   v_count int;
   v_period VARCHAR2(20);
   v_lotterycode VARCHAR2(20);
   cursor emp_cursor is
   select t.cperiodid,t.clotterycode from cm_period_info t
   where t.cendtime <to_char(sysdate,'yyyy-MM-dd HH:mm:ss') and (t.ISTATE=0 or t.ISTATE=1);


begin
   po_ErrCode :=9999;
   po_ErrDesc :='未知异常';
   po_orderAccoutSum:=0;
   OPEN emp_cursor;

   IF emp_cursor%ISOPEN THEN
   LOOP
   FETCH emp_cursor INTO v_period,v_lotterycode;
   EXIT WHEN emp_cursor%NOTFOUND;
   --处理代购0表示未处理; 1-收单成功 2-收单失败
   select count(*) into v_count from cm_orderinfo t where t.cperiodid=v_period and t.clotterycode=v_lotterycode
   and (t.tradestate=1 or t.tradestate=2 );
   if v_count>0 then
   update cm_orderinfo t1 set t1.TRADESTATE=11 where t1.cperiodid=v_period and t1.clotterycode=v_lotterycode;
   po_orderAccoutSum:=po_orderAccoutSum+v_count;
   ----缺少退款流程--单独线程处理
   end if;

   --处理合买
   select count(*) into v_count from cm_hm_info t where t.ILEAVINGNUM>0
   and t.ICPERIODS=v_period and t.CLOTTERYCODE=v_lotterycode and (t.ISTATE=0 or t.ISTATE=1);
   if v_count>0 then
   update cm_hm_info t set t.istate=5 where ( t.ILEAVINGNUM>0 or t.istate=0 or t.istate=1 ) and t.ICPERIODS=v_period and t.CLOTTERYCODE=v_lotterycode;
   po_orderAccoutSum:=po_orderAccoutSum+v_count;
   ---缺少退款流程--单独线程处理
   end if;

   end loop;
   CLOSE emp_cursor;

   update cm_period_info t set t.istate=2 where t.cendtime <to_char(sysdate,'yyyy-MM-dd HH:mm:ss');
   po_ErrCode :=0;
   po_ErrDesc :='期次停止并将相关订单修改为[出票失败]完成';
   commit;
   else
   po_ErrCode :=2001;
   po_ErrDesc :='游标未打开';
   commit;
   end if;



   /*
   LOOP
   FETCH emp_cursor INTO v_ename;
   EXIT WHEN emp_cursor%NOTFOUND;

   update cm_orderinfo t1 set t1.TRADESTATE=5 where t1.cperiodid=emp_cursor%cperiodid
   and t1.clotterycode=emp_cursor%clotterycode;
   po_orderAccoutSum:=emp_cursor%ROWCOUNT;
   END LOOP;
   end if;

   FOR Emp_record IN emp_cursor LOOP
   DBMS_OUTPUT.PUT_LINE(Emp_record.cperiodid|| Emp_record.clotterycode);
   END LOOP;
   */

Exception
   When Others Then
   po_ErrCode :=9999;
   po_ErrDesc := '未知异常' ;
   IF emp_cursor%ISOPEN THEN
   CLOSE emp_cursor;
   END IF;
   rollback;
end pro_stopPeriod;