File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes ORA-01000: maximum open cursors exceeded Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ORA-01000: maximum open cursors exceeded " Watch "ORA-01000: maximum open cursors exceeded " New topic
Author

ORA-01000: maximum open cursors exceeded

Madhu Sudhana
Ranch Hand

Joined: Apr 16, 2006
Posts: 127
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;
}
}


"And the trouble is, if you don't risk anything, you risk even more." -- Erica Jong.
Madhu Sudhana
Ranch Hand

Joined: Apr 16, 2006
Posts: 127
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
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
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 ]
A Kumar
Ranch Hand

Joined: Jul 04, 2004
Posts: 979
Hi..

Have a look at this site..

Oracle Error Code
Madhu Sudhana
Ranch Hand

Joined: Apr 16, 2006
Posts: 127
I got the answer

that is I left ResultSet and PreparedStatements unclosed

I closed them and its working fine
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ORA-01000: maximum open cursors exceeded