• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

ORA-01000: maximum open cursors exceeded

 
Ranch Hand
Posts: 127
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Ranchers

I am getting an maximum open cursors exception in my DAO.I am using Oracle as my backend.

please suggest me a solution to this problem....

thanks in advance.....


package com.jps.dao;

import java.util.Date;
import java.util.ArrayList;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


import java.text.ParseException;
import java.util.Date;

import com.jps.utils.DbBean;
import com.jps.bean.ViewRemittanceDOB;

import java.text.DateFormat;
import java.text.SimpleDateFormat;

import java.io.PrintWriter;
public class ViewRemittanceDao {
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
DbBean db=new DbBean ();
String strFormattedBusinessDate=null;

public ArrayList viewRemittance(String View,String year,String month,String day) throws ParseException {

String query=null;
ArrayList all_al_remittance=new ArrayList();
int i_count=0;

try{

con=db.dbConnection();
if(View==null || year==null || month==null || day==null){
//to get the remittance date
ps=con.prepareStatement("SELECT BUSINESS_DT FROM JPS_BUSINESS_DATE");
rs=ps.executeQuery();
while(rs.next()){
strFormattedBusinessDate=getDateInFormat(rs.getDate(1));
}
query="SELECT MOC_ID,MOC_CONTRACT_NO,TOT_ADVANCE_PAYMENT_AMT,TOT_CANCELLATION_AMT,TOT_REMITTANCE_AMT,COUNTING_NO FROM JPS_MO_REMITTANCE_TRNS WHERE JPS_MO_REMITTANCE_TRNS.REMITTANCE_DT=(SELECT BUSINESS_DT FROM JPS_BUSINESS_DATE) AND JPS_MO_REMITTANCE_TRNS.VALIDATED_FLG='Y' ORDER BY MOC_ID ASC";
}else{
String str_date=year+month+day;
query="SELECT MOC_ID,MOC_CONTRACT_NO,TOT_ADVANCE_PAYMENT_AMT,TOT_CANCELLATION_AMT,TOT_REMITTANCE_AMT,COUNTING_NO FROM JPS_MO_REMITTANCE_TRNS WHERE JPS_MO_REMITTANCE_TRNS.REMITTANCE_DT=to_date('"+str_date+"'"+",'yyyymmdd') AND JPS_MO_REMITTANCE_TRNS.VALIDATED_FLG='Y' ORDER BY MOC_ID ASC";
}
System.out.println("main query in ViewRemittanceDao:"+query);
ps=con.prepareStatement("select count(*) from JPS_MO_REMITTANCE_TRNS");
rs=ps.executeQuery();
while(rs.next()){
i_count=rs.getInt(1);
}
ps=con.prepareStatement(query);
rs=ps.executeQuery();

String str_moc_id="";
String str_moc_contract_no="";
double d_tot_advance_payment_amt=0;
double d_tot_cancellation_amt=0;
double d_tot_remittance_amt=0;
String str_countNo="";

for(int i=0;i<i_count;i++){
while(rs.next()){
str_moc_id=rs.getString(1);
str_moc_contract_no=rs.getString(2);
d_tot_advance_payment_amt=rs.getDouble(3);
d_tot_cancellation_amt=rs.getDouble(4);
d_tot_remittance_amt=rs.getDouble(5);
str_countNo=rs.getString(6);

ViewRemittanceDOB viewRemittanceDOB=new ViewRemittanceDOB(str_moc_id,str_moc_contract_no,d_tot_advance_payment_amt,d_tot_cancellation_amt,d_tot_remittance_amt,str_countNo);
all_al_remittance.add(i,viewRemittanceDOB);
}
}
return all_al_remittance;
}catch(Exception e){
e.printStackTrace();
System.out.println("Exception caught in the method viewRemittance of ViewRemittanceDao-------------");
}
finally{
try{
if (ps!=null)rs.close();
if (ps!=null)ps.close();
if (con!=null)con.close();
}catch(SQLException sqe){sqe.printStackTrace();System.out.println("Exception caught in the method viewRemittance of ViewRemittanceDao of ps.close --------------------");}
}

return all_al_remittance;
}

public void saveAsFile(PrintWriter pwOut,String moc_id, String saveAsHtml,String year,String month,String day,String str_countNo){

if(moc_id!=null && saveAsHtml.equalsIgnoreCase("true")){

String str_date=year+month+day;

String str_moc_id="";
String str_moc_contract_no="";
String str_counting_no="";
String str_large_prcel_no="";
String str_shipping_dt="";
String str_data_confirmed_dt="";
String str_remittance_dt="";
String str_cacellation_dt="";
double d_cod_amt=0;
double d_service_chrg=0;
double d_cancellation_amt=0;
double d_advance_payment_amt=0;
String query1=null;


query1="SELECT JPS_MO_REMITTANCE_TRNS.MOC_ID,JPS_MO_REMITTANCE_TRNS.MOC_CONTRACT_NO,JPS_MO_REMITTANCE_TRNS.COUNTING_NO,JPS_APLUS_CONTROL.LARGE_PARCEL_NO,JPS_APLUS_CONTROL.SHIPPING_DT,JPS_APLUS_CONTROL.DATA_CONFIRMED_DT,JPS_MO_REMITTANCE_TRNS.REMITTANCE_DT,JPS_APLUS_CONTROL.CANCELLATION_DT,JPS_APLUS_CONTROL.COD_AMT,JPS_APLUS_CONTROL.SERVICE_CHRG,JPS_APLUS_CONTROL.CANCELLATION_AMT,JPS_APLUS_CONTROL.ADVANCE_PAYMENT_AMT FROM JPS_MO_REMITTANCE_TRNS,JPS_APLUS_CONTROL WHERE JPS_APLUS_CONTROL.COUNTING_NO=JPS_MO_REMITTANCE_TRNS.COUNTING_NO AND JPS_APLUS_CONTROL.MOC_ID=JPS_MO_REMITTANCE_TRNS.MOC_ID AND JPS_APLUS_CONTROL.COUNTING_NO='"+str_countNo+"' AND JPS_APLUS_CONTROL.MOC_ID='"+moc_id+"' ORDER BY JPS_APLUS_CONTROL.LARGE_PARCEL_NO";

System.out.println("save as csv query in ViewRemittanceDao:"+query1);


try{
con=db.dbConnection();
ps=con.prepareStatement(query1);
rs=ps.executeQuery();
PrintWriter output=pwOut;
output.println("MOC identification key,MOC no. for reference,Counting no,Large parcel number(Tracking key),Shipping date,Data confirmed date,Remittance date,Cancellation date,Amount of COD(Advance money amount),Service charge for COD.,Total amount of cancellation.,Total amount of remittance.");

while(rs.next()){
str_moc_id=initiliseToEmptyIfNull(rs.getString(1));
str_moc_contract_no=initiliseToEmptyIfNull(rs.getString(2));
str_counting_no=initiliseToEmptyIfNull(rs.getString(3));
str_large_prcel_no=initiliseToEmptyIfNull(rs.getString(4));
str_shipping_dt=getDateInFormat(rs.getDate(5));
str_data_confirmed_dt=getDateInFormat(rs.getDate(6));
str_remittance_dt=getDateInFormat(rs.getDate(7));
str_cacellation_dt=getDateInFormat(rs.getDate(8));

d_cod_amt=rs.getDouble(9);
d_service_chrg=rs.getDouble(10);
d_cancellation_amt=rs.getDouble(11);
d_advance_payment_amt=rs.getDouble(12);
output.println(str_moc_id+","+str_moc_contract_no+","+str_counting_no+","+str_large_prcel_no+","+str_shipping_dt+","+str_data_confirmed_dt+","+str_remittance_dt+","+str_cacellation_dt+","+d_cod_amt+","+d_service_chrg+","+d_cancellation_amt+","+d_advance_payment_amt);
}
output.flush();
}catch(Exception e){
e.printStackTrace();
System.out.println("------------Exception caught in the method saveAsHtml the DAO-------------");
}
finally{
try{
if (rs!=null)rs.close();
if (ps!=null)ps.close();
if (con!=null)con.close();
}catch(SQLException sqe){sqe.printStackTrace();System.out.println("Exception caught in method saveAsHtml of ViewRemittanceDao of ps.close --------------------");}
}
}//end of if
}//end of method saveAsHtml
public String getDateInFormat(Date date){
if(date!=null){
String strDate=date.toString();
String dateInFormat=strDate.substring(0,4)+"/"+strDate.substring(5,7)+"/"+strDate.substring(8,10);
return dateInFormat;
}else{
return "";
}

}
public String getFormattedBusinessDate(){
return this.strFormattedBusinessDate;

}
public boolean checkBusinessDate(String year,String month,String day) {

boolean returnValue=false;
try{
con=db.dbConnection();
ps=con.prepareStatement("SELECT BUSINESS_DT FROM JPS_BUSINESS_DATE");
rs=ps.executeQuery();

Date businessDate=null;
while(rs.next()){

businessDate=rs.getDate(1);
}
DateFormat df=new SimpleDateFormat("yyyy-MM-dd");
Date paramDate=df.parse(year+"-"+month+"-"+day);
int compareValue=businessDate.compareTo(paramDate);
if(compareValue >= 0){
returnValue =true;
}
else
{
returnValue=false;
}

}catch(Exception e){
e.printStackTrace();
System.out.println("------------Exception caught in the method saveAsHtml the DAO-------------");
}
finally{
try{if (rs!=null)rs.close();
if (ps!=null)ps.close();
if (con!=null)con.close();
}catch(SQLException sqe){sqe.printStackTrace();System.out.println("Exception caught in method saveAsHtml of ViewRemittanceDao of ps.close --------------------");}
}
return returnValue;
}
public String initiliseToEmptyIfNull(String value){
if(value==null){
value="";
}
return value;
}
}
 
Madhu Sudhana
Ranch Hand
Posts: 127
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Ranchers

if any body faced the sa,e problem or if you know the solution .tell me how can I avoid this problem?

thanks and regards
 
Ranch Hand
Posts: 425
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It's bit complicated, First to play around with this exception you need DBA assitant. If you have DBA or select privilege on V$PARAMETER view then you can query to find the configured setting for open_cursors.



If the value is configured to be too less (depends on application to application) you need to increase the value using alter system command.

The root cause to this CAN be cursor leaks either in Java code or in PL/SQL units. There are ways to find out the open cursors of a particular session but it's better you talk to the administrator.
[ September 06, 2006: Message edited by: Purushothaman Thambu ]
 
Ranch Hand
Posts: 980
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi..

Have a look at this site..

Oracle Error Code
 
Madhu Sudhana
Ranch Hand
Posts: 127
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I got the answer

that is I left ResultSet and PreparedStatements unclosed

I closed them and its working fine
 
Won't you be my neighbor? - Fred Rogers. tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic