I am in the process of converting software to interface with Oracle from
Interbase. The program works great but after a few database accesses and
changes through the dataExpress components(QueryDataSet(qds)) a dataset
Exception is thrown(ORA-01000: maximum open cursors exceeded). To trace the
source of the error I ran the jdbc
monitor(com.borland.jbcl.sql.monitor.MonitorPanel) only to find that there
is a sql Exception that is thrown after every qds.execute() method
(ORA-00942: table or view does not exist). This message is not being caught
by the dataSet, but the dataset retrieves the necessary information
correctly. The reason for this is probably because a have created public
synonym so that the tables schema for the sake of backward compatibilty...

Here is a sample code that throws the same error:

try {
for (int i = 0; i < 300; i++) {
qds.close();
qds = null;
qds = new QueryDataSet();

qds.setQuery(new QueryDescriptor(database1,"select * from
mytable"));
// [1] Commenting out the following code below does the same
// as the above but without the jdbc error
// qds.setQuery(new QueryDescriptor(database1,"select * from
userschema.mytable"));
qds.executeQuery();

// [2] Commenting out the following code below does the same
// as the above but without the jdbc error
// Statement s = database1.getJdbcConnection().createStatement();
// ResultSet rs = s.executeQuery("select * from mytable");
// ps.executeQuery();
// rs.close();
// s.close();
}
} catch (Exception dse) {
dse.printStackTrace()
}

Due to the sheer size of the code I am working with, which uses dataexpress
component almost entirely for it database access changing the code to
comment[1] or comment[2] is not options even though it would sort out my
problems!!

My guess is that when ORA-00942 error is caught in the jdbc that statement
that threw the error is not closed even when the qds.close() method is
called. Eventually because all these cursors remain open the maximum open
cursor limit is reached thereby throwing dataset error ORA-01000.

Is there a way of closing these open cursors? Can the qds retrieve the
statement that it opened so that it can be closed exiplicitly. Has anyone
else had this problem and found a solution? PLEASE HELP...!!

Many Thankx

Sheldon