I am hitting select queries on 16 tables in the database, for huge data and getting cursor limit exceeds exception.
I have already closed the connection, prepared statement, and resultset but still getting cursor limit exceeds exception.
I closed the connection but still not releasing database cursors
I have closed the connection in the init block as shown in the code snippet below, and already closed the resultSet and preparedStatement in finally block
Please suggest me how to release cursor to avoid this exception.
Attaching code for reference.
public class DataReader {
PDFProperty properties = new PDFProperty();
FileInputStream fileInputStream = null;
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
private Connection connection;
private PreparedStatement ps;
private ResultSet rs;
private ArrayDeque<String> OHR = new ArrayDeque<String>(18502);
private DataReader reader = null;
private PDFCollection collect;
ArrayDeque<String> OHRArrayList = new ArrayDeque<>();
private String dbArray[] = null;
private PDFProperty prop = null;
Iterator<String> headerIterator = null;
Iterator<List<String>> dataIterator = null;
Iterator<String> nestedIterator = null;
List<String> nestedList = new ArrayList<String>();
private List<List<String>> totalData = new ArrayList<List<String>>(100000);
HashMap<String, String> map = new HashMap<String, String>();
HashMap<String, String> map1 = new HashMap<String, String>();
{
System.out.println("called before constructor......");
try {
if(connection!=null)
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public DataReader() {
/* Connect connect = Connect.getInstance();
connection = connect.getConnection();*/
Connect connect = new Connect();
connection = connect.setConnection();
System.err.println("******************************************************************");
System.err.println("connection is:"+connection);
System.err.println("******************************************************************");
}
public ArrayDeque<String> getOHRList() {
/*
* Connect connect = Connect.getInstance();
*
* connection = connect.getConnection();
*
* String sql = "select employee_ohr_id from OVERALL_RATING";
*
* try { ps = connection.prepareStatement(sql); rs = ps.executeQuery();
*
* while (rs.next()) { System.out.println(rs.getString(1));
*
* OHR.add(rs.getString(1));
*
* } System.out.println(OHR);
*
* } catch (SQLException e) { // TODO Auto-generated catch block
* e.printStackTrace(); }
*/
OHR.add("703045278");/*
* OHR.add("601002590");
*
* OHR.add("601002424");
*/
return OHR;
}
public List<String> getTableHeader(String name) {
List<String> header = new ArrayList<String>();
String sql = "SELECT column_name FROM USER_TAB_COLUMNS WHERE table_name = '" + name + "'ORDER BY COLUMN_ID ";
System.out.println("Header sql :" + sql);
/*Connect connect = new Connect();
connection = connect.setConnection();*/
/*
Connect connect = Connect.getInstance(); connection =
connect.getConnection();*/
try {
connection.setAutoCommit(false);
ps = connection.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
header.add(rs.getString(1));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
connection.commit();
ps.close();
rs.close();
ps=null;
rs=null;
System.err.println("result set :"+rs);
System.err.println("prepared statement :"+ps);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
System.out.println("Table Name :" + name);
System.out.println("Header :" + header);
return header;
}
/*
* public static void main(String[] args) { DataReader dr = new
* DataReader(); System.out.println(dr.getTableHeader("REVIEW_PERIOD")); }
*/
public void getData(String list, PDFCollection col) {
/*Connect connect = new Connect();
connection = connect.setConnection();*/
/*Connect connect = Connect.getInstance(); connection =
connect.getConnection();*/
System.out.println("lis is " + list);
System.out.println("data reader :" + col);
List<String> headers = new ArrayList<String>();
reader = new DataReader();
/*
* Connect connect = Connect.getInstance();
*
* connection = connect.getConnection();
*/
prop = new PDFProperty();
dbArray = prop.getProperty("TABLE_NAMES").split("~");
/* for (String list : OHRArrayList) { */
for (String table : dbArray) {
List<String> allData = new ArrayList<String>();
List<String> rows = new ArrayList<String>();
String sql = "";
/*
* String sql =
* "select * from CAREER_PLAN where EMPLOYEE_OHR_ID = '703045278'" ;
*/
String columnCount = "SELECT count(column_name) FROM USER_TAB_COLUMNS WHERE table_name = '" + table + "'";
int count = 0;
try {
//connection.setAutoCommit(false);
ps = connection.prepareStatement(columnCount);
rs = ps.executeQuery();
while (rs.next()) {
count = Integer.parseInt(rs.getString(1));
}
headers = reader.getTableHeader(table);
System.out.println("headers :" + headers);
for (String head : headers) {
if (head.equals("OHR_ID") || head.equals("EMPLOYEE_OHR_ID")) {
sql = "select * from " + table + " where " + head + "= '" + list + "'";
}
}
System.out.println("sql is :" + sql);
System.out.println(table);
ps = connection.prepareStatement(sql, ResultSet.CLOSE_CURSORS_AT_COMMIT);
rs = ps.executeQuery();
String res = "";
while (rs.next()) {
for (int i = 1; i <= count; i++) {
res = rs.getString(i);
if (res == null) {
res = "";
}
allData.add(res);
}
}
System.out.println("all data is :" + allData);
totalData = Lists.partition(allData, count);
System.out.println(table);
// System.out.println("total data :" + totalData);
/*
* Iterator it = totalData.iterator();
*
* while (it.hasNext()) { System.out.println(it.next());
*
* }
*/
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} finally {
try {
System.err.println("inside finally");
//connection.commit();
rs.close();
ps.close();
//System.out.println(rs.);
// connection.close();
// connection.close();
/// System.out.println("connection after ");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
System.out.println("======================================================================");
System.out.println(table);
System.out.println("======================================================================");
if (table.equals("EMPLOYEE_INFORMATION")) {
headerIterator = headers.iterator();
dataIterator = totalData.iterator();
while (dataIterator.hasNext()) {
nestedList = dataIterator.next();
nestedIterator = nestedList.iterator();
}
System.out.println("nested List :" + nestedList);
System.out.println("Employee Headers" + headers);
if (!(nestedList.isEmpty())) {
while (headerIterator.hasNext() && nestedIterator.hasNext()) {
map.put(headerIterator.next(), nestedIterator.next());
}
System.out.println("=============================================================================");
System.out.println("employee map :" + map);
System.out.println("=============================================================================");
for (Map.Entry<String, String> ee : map.entrySet()) {
if (ee.getKey().equalsIgnoreCase("OHR_ID")) {
col.setOHR_ID(ee.getValue().trim());
}
if (ee.getKey().equalsIgnoreCase("EMPLOYEE_FIRST_NAME")) {
col.setFirst_Name(ee.getValue().trim());
}
if (ee.getKey().equalsIgnoreCase("EMPLOYEE_MIDDLE_NAME")) {
if (ee.getValue() != null)
col.setMiddle_Name(ee.getValue().trim());
else
col.setMiddle_Name("");
}
if (ee.getKey().equalsIgnoreCase("EMPLOYEE_LAST_NAME")) {
col.setLast_Name(ee.getValue().trim());
}
if (ee.getKey().equalsIgnoreCase("FORM_TEMPLATE_NAME")) {
col.setTemplate(ee.getValue().trim() + " for");
}
if (ee.getKey().equalsIgnoreCase("FORM_COMPLETED_ON_DATE")) {
System.out.println(ee.getValue());
if (ee.getValue().trim().contains(":")) {
String compDate[] = ee.getValue().trim().split("\\s+");
col.setFormCompeletionDate(compDate[0]);
} else {
col.setFormCompeletionDate(ee.getValue().trim());
}
}
}
}
col.setOriginator(col.getFirst_Name() + " " + col.getLast_Name() + " (" + col.getOHR_ID() + ")");
}
if (table.equals("CAREER_PLAN")) {
if (totalData.size() == 0) {
List<List<String>> tempList = new ArrayList<List<String>>();
List<String> nullList = null;
for (int i = 0; i < headers.size(); i++) {
nullList = new ArrayList<String>();
nullList.add("NO DATA");
}
tempList.add(nullList);
col.setCareerPlanHeader(headers);
col.setCareerPlanTotalFilteredRows(totalData);
} else {
col.setCareerPlanHeader(headers);
col.setCareerPlanTotalFilteredRows(totalData);
}
}
if (table.equals("COMPETENCIES")) {
System.out.println("competencies " + totalData);
if (totalData.size() > 1) {
List<List<String>> sortedList = new ArrayList<List<String>>();
Map keyValueofSortedValue = new HashMap();
int index = headers.indexOf("COMPETENCY_NAME");
for (List list1 : totalData) {
String value = (String) list1.get(index);
String num[] = value.split("\\.");
System.out.println(num[0]);
keyValueofSortedValue.put(Integer.parseInt(num[0]), list1);
}
for (int i = 1; i <= keyValueofSortedValue.size(); i++) {
List finalList = (List) keyValueofSortedValue.get(i);
sortedList.add(finalList);
}
System.out.println("sett head " + headers);
col.setCompetenciesExcelHeader(headers);
col.setCompetenciesTotalFilteredRows(sortedList);
} else {
col.setCompetenciesExcelHeader(headers);
col.setCompetenciesTotalFilteredRows(totalData);
}
}
if (table.equals("FEEDBACK_APPRAISAL_PROCESS")) {
System.out.println("FEEDBACK_ON_APPRAISAL_PROCESS :" + totalData);
col.setFeedbackHeader(headers);
col.setFeedbackTotalFilteredRows(totalData);
}
if (table.equals("OVERALL_RATING")) {
System.out.println("inside overall rating" + totalData);
headerIterator = headers.iterator();
dataIterator = totalData.iterator();
while (dataIterator.hasNext()) {
System.out.println("inside while");
List nestedList = dataIterator.next();
nestedIterator = nestedList.listIterator();
}
if (!(nestedList.isEmpty())) {
while (headerIterator.hasNext() && nestedIterator.hasNext()) {
map1.put(headerIterator.next(), nestedIterator.next());
}
for (Map.Entry<String, String> ee : map1.entrySet()) {
if (ee.getKey().equalsIgnoreCase("OVERALL_PERFORMANCE_RATING")) {
if (ee.getValue().trim().equalsIgnoreCase("1")) {
col.setPerformance("1.0 - T20");
} else if (ee.getValue().trim().equalsIgnoreCase("2")) {
col.setPerformance("2.0 - HV70");
} else if (ee.getValue().trim().equalsIgnoreCase("3")) {
col.setPerformance("3.0 - LE10");
} else {
col.setPerformance("Unrated");
}
}
if (ee.getKey().equalsIgnoreCase("OVERALL_POTENTIAL_RATING_DESCRIPTION")) {
if (ee.getValue().trim().equalsIgnoreCase("High")) {
col.setPotential("1.0 - High");
} else if (ee.getValue().trim().equalsIgnoreCase("Specialist")) {
col.setPotential("2.0 - Specialist");
} else if (ee.getValue().trim().equalsIgnoreCase("Medium")) {
col.setPotential("3.0 - Medium");
} else if (ee.getValue().trim().equalsIgnoreCase("Low")) {
col.setPotential("4.0 - Low");
} else {
col.setPotential(" ");
}
}
}
}
}
if (table.equals("REVIEW_PERIOD")) {
System.out.println("Review headers :" + headers);
headerIterator = headers.iterator();
dataIterator = totalData.listIterator();
while (dataIterator.hasNext()) {
List nestedList = dataIterator.next();
nestedIterator = nestedList.listIterator();
}
HashMap<String, String> map1 = new HashMap<String, String>();
while (headerIterator.hasNext() && nestedIterator.hasNext()) {
map1.put(headerIterator.next(), nestedIterator.next().toString());
}
for (Map.Entry<String, String> ee : map1.entrySet()) {
if (ee.getKey().equalsIgnoreCase("FORM_START_DATE")) {
col.setFormStartDate(ee.getValue().trim());
}
if (ee.getKey().equalsIgnoreCase("FORM_END_DATE")) {
col.setFormEndDate(ee.getValue().trim());
}
if (ee.getKey().equalsIgnoreCase("FORM_DUE_DATE")) {
col.setDue_Date(ee.getValue().trim());
}
if (ee.getKey().equalsIgnoreCase("ORIGINATOR_FIRST_NAME")) {
col.setOrgFirstName(ee.getValue().trim());
}
if (ee.getKey().equalsIgnoreCase("ORIGINATOR_LAST_NAME")) {
col.setOrgLastName(ee.getValue().trim());
}
if (ee.getKey().equalsIgnoreCase("ORIGINATOR_OHR_ID")) {
col.setOrgOHRId(ee.getValue().trim());
}
}
col.setReview_Period(col.getFormStartDate() + " - " + col.getFormEndDate());
/*
* col.setFormStartDate("16-feb-2015");
* col.setFormEndDate("28-feb-2015"); col.setDue_Date("");
* col.setOrgFirstName(""); col.setOrgLastName("");
* col.setOrgOHRId(""); col.setReview_Period(
* "16-feb-2015 - 28-feb-2015");
*/
}
if (table.equals("PROJECT_GOALS")) {
System.out.println("project goals :" + totalData);
if (totalData.size() == 0) {
List<List<String>> tempList = new ArrayList<List<String>>();
List<String> nullList = null;
for (int i = 0; i < headers.size(); i++) {
nullList = new ArrayList<String>();
nullList.add("NO DATA");
}
tempList.add(nullList);
col.setTotalFilteredRows(tempList);
col.setExcelHeader(headers);
} else {
col.setTotalFilteredRows(totalData);
col.setExcelHeader(headers);
}
}
if (table.equals("GOALS")) {
if (totalData.size() == 0) {
List<List<String>> tempList = new ArrayList<List<String>>();
List<String> nullList = null;
for (int i = 0; i < headers.size(); i++) {
nullList = new ArrayList<String>();
nullList.add("NO DATA");
}
tempList.add(nullList);
col.setGoalExcelHeader(headers);
col.setGoalTotalFilteredRows(tempList);
} else {
col.setGoalExcelHeader(headers);
col.setGoalTotalFilteredRows(totalData);
}
}
if (table.equals("OVERALL_COMMENTS_ON_GOALS")) {
col.setOverallHeader(headers);
col.setOverallTotalFilteredRows(totalData);
}
if (table.equals("OVERALL_COMMENTS_ON_GOALS_ADD_REVIEWER")) {
/*
* it1 = headerArrayList.iterator(); it2 =
* filteredArrayList.listIterator(); while (it2.hasNext()) {
* List nestedList = it2.next(); it3 =
* nestedList.listIterator(); }
*
* HashMap<String, String> map = new HashMap<String, String>();
*
* while (it1.hasNext() && it3.hasNext()) { map.put(it1.next(),
* it3.next().toString()); }
*
* for (Map.Entry<String, String> ee : map.entrySet()) {
*
* if (ee.getKey().equalsIgnoreCase("Section Comment")) {
* coll.setHussain_Comments(ee.getValue().trim()); } if
* (ee.getKey().equalsIgnoreCase("Section Comment Owner User ID"
* )) { coll.setOverallCommentUserID(ee.getValue().trim()); } }
*/
col.setHussain_Comments("");
col.setOverallCommentUserID("");
}
if (table.equals("GENPACT_VALUES")) {
headerIterator = headers.iterator();
dataIterator = totalData.iterator();
while (dataIterator.hasNext()) {
List nestedList = dataIterator.next();
nestedIterator = nestedList.listIterator();
}
HashMap<String, String> map1 = new HashMap<String, String>();
while (headerIterator.hasNext() && nestedIterator.hasNext()) {
map1.put(headerIterator.next(), nestedIterator.next().toString());
}
for (Map.Entry<String, String> ee : map1.entrySet()) {
if (ee.getKey().equalsIgnoreCase("SECTION_COMMENT")) {
col.setGenpactValue(ee.getValue().trim());
}
}
}
if (table.equals("OVERALL_COMMENTS_ON_POTENTIAL")) {
col.setOverallPotHeader(headers);
col.setOverallPotTotalFilteredRows(totalData);
}
if (table.equals("OVERALL_COMMENTS_POTENTIAL_REV")) {
System.out.println("OVERALL_COMMENTS_ON_POTENTIAL_REVIEWER");
col.setOverallPotReviewHeader(headers);
col.setOverallPotReviewTotalFilteredRows(totalData);
}
if (table.equals("PERONAL_DEVELOPMENT_PLAN")) {
System.out.println("inside pdp");
if (totalData.size() > 1) {
List<List<String>> sortedList = new ArrayList<List<String>>();
Map trainingMap = new HashMap();
Map competencyMap = new HashMap();
int index = headers.indexOf("SECTION_CUSTOM_FIELD_NAME");
for (List list1 : totalData) {
String value = (String) list1.get(index);
value = value.substring(0, value.length() - 1);
String num[] = value.split("\\s+");
if (num[0].trim().contains("Training")) {
trainingMap.put(Integer.parseInt(num[1].trim()), list1);
} else if (num[0].trim().contains("Competency")) {
competencyMap.put(Integer.parseInt(num[1].trim()), list1);
}
}
for (int i = 1; i <= competencyMap.size(); i++) {
List finalList = (List) competencyMap.get(i);
sortedList.add(finalList);
}
for (int i = 1; i <= trainingMap.size(); i++) {
List finalList = (List) trainingMap.get(i);
sortedList.add(finalList);
}
col.setPDPHeader(headers);
col.setPDPTotalFilteredRows(sortedList);
} else {
col.setPDPHeader(headers);
col.setPDPTotalFilteredRows(totalData);
}
}
if (table.equals("PERSONAL_DEV_PLAN_RATING_TRAIN")) {
System.out.println("PERSONAL_DEVELOPEMENT_PLAN_REVIEW");
col.setPDPReviewHeader(headers);
col.setPDPReviewTotalFilteredRows(totalData);
}
if (table.equals("SUCCESSION_PLAN")) {
col.setSuccessHeader(headers);
col.setSuccessTotalFilteredRows(totalData);
}
if (table.equals("RELEASIBILITY_TIMELINE")) {
/*
* it1 = headerArrayList.iterator(); it2 =
* filteredArrayList.listIterator(); while (it2.hasNext()) {
* List nestedList = it2.next(); it3 =
* nestedList.listIterator(); }
*
* HashMap<String, String> map = new HashMap<String, String>();
*
* while (it1.hasNext() && it3.hasNext()) { map.put(it1.next(),
* it3.next().toString()); }
*
* for (Map.Entry<String, String> ee : map.entrySet()) {
*
* if (ee.getKey().equalsIgnoreCase("Section Custom Field Name"
* )) { coll.setReleasability_CFN(ee.getValue().trim()); } if
* (ee.getKey().equalsIgnoreCase("Section Custom Field Value"))
* { if (ee.getValue().trim().equalsIgnoreCase("n1224m")) {
* coll.setReleasability_CFV("Next 12 to 24 Month"); } else if
* (ee.getValue().trim().equalsIgnoreCase("n612m")) {
* coll.setReleasability_CFV("Next 6 to 12 Month"); } else if
* (ee.getValue().trim().equalsIgnoreCase("n6m")) {
* coll.setReleasability_CFV("Next 6 Month"); } else if
* (ee.getValue().trim().equalsIgnoreCase("np")) {
* coll.setReleasability_CFV("Not Planned"); } else {
* coll.setReleasability_CFV(" "); } } }
*/
col.setReleasability_CFN("");
col.setReleasability_CFV("");
}
if (table.equals("SIGNATURE")) {
}
}
/*try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}*/
}
public List<String> getOHRListFromMore4K() {
String columnWanted = "Employee OHR ID";
Integer columnNo = null;
// output all not null values to the list
List<String> list = new ArrayList<String>();
try {
// fileInputStream = new
// FileInputStream(properties.getProperty("OHR_COUNT_FILE"));
// For Rating
System.err.println(properties.getProperty("OHR_4K"));
fileInputStream = new FileInputStream(properties.getProperty("OHR_4K"));
workbook = new XSSFWorkbook(fileInputStream);
sheet = workbook.getSheetAt(0);
// Row firstRow = sheet.getRow(0);
/*
* for (Cell cell : firstRow) { if
* (cell.getStringCellValue().equals(columnWanted)) { columnNo =
* cell.getColumnIndex(); System.err.println(columnNo); } }
*/
for (Row row : sheet) {
Cell cell = row.getCell(0);
if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
// Nothing in the cell in this row, skip it
} else {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
DataFormatter dataFormatter = new DataFormatter();
String cellActualValue = dataFormatter.formatCellValue(cell);
list.add(cellActualValue.trim());
}
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
DataFormatter dataFormatter = new DataFormatter();
String cellActualValue = dataFormatter.formatCellValue(cell);
list.add(cellActualValue.trim());
}
}
}
} catch (IOException ioe) {
ioe.printStackTrace();
} finally {
if (fileInputStream != null) {
try {
fileInputStream.close();
} catch (IOException ioe) {
ioe.printStackTrace();
}
}
}
boolean flag = false;
for (String str : list) {
if (str.equalsIgnoreCase("Employee OHR ID")) {
flag = true;
}
}
if (flag) {
list.remove("Employee OHR ID");
}
return list;
}
}
Stack Trace--
competencies [[2015 Appraisal, 760004687, Genpact Values, Ana is committed to Genpact values, although she is new in the company she has been adapting very quickly, Completed]]
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
at com.genpact.dao.DataReader.getData(DataReader.java:212)
at com.genpact.templateGen.EmpTemplateGen.getTemplate(EmpTemplateGen.java:160)
at com.genpact.templateGen.EmpTemplateGen.main(EmpTemplateGen.java:113)
