0

I have some unique design like this. There are two db servers 1)MSSQL Server (from 3rd party) There are two tables in MSSQL server, they are the parent tables. One table contain company Names and other table contain its employees. 2)Oracle DB(our internal) contains 2 tables(child tables) One table contain company names and other one are its employees.

Now we need to load data from parent tables to child tables, Problem is data in parent tables may change (i.e., rows can be inserted or updated). To solve this we have used JDBC to read data from parent tables and check whether the data is available in child table, if available update it or insert it. Below is the typical code 

//con1=sql server connection
//con2= oracle connection.
rs=con1.prepareStatement("select COMPANY_CODE from PARENT_COMPANY")
 while(rs.next)
{
rs2=con2.prepareStatement("select COMPANY_CODE from CHILD_COMPANY WHERE COMPANY_CODE="+rs.getString("COMPANY_CODE"));
if(rs2.next())
{
//code for updation in child company

}
else
{

//code for insertion in child company
}




   }
 //similar logic for employee table

I know this is a very bad coding practice. It is working fine until data is small like 10 or 15 companies, now the company count has become 300, this code is not working properly and giving following error "maximum number of open cursors exceeded" I have two questions here..

1)How to optimize this code or change the design of the code..

2)Are there any third party tools which does such kind of data insert/update.

Already visited this post java.sql.SQLException: - ORA-01000: maximum open cursors exceeded

Community
  • 1
  • 1
aravind
  • 978
  • 3
  • 12
  • 21
  • What is it about the referenced post that you didn't understand? You're creating a bunch of cursors but as far as anyone can see you never close them. Your program needs to close the cursors it opens - see the example in the dup reference under "Closing JDBC objects". Best of luck. – Bob Jarvis - Слава Україні Dec 26 '16 at 17:02
  • I have understood the post to close the cursors..In future if my parent table count has become 1000..my application performance will go down because 1000 select queries inside while loop..My question is how to optimize the code for future data..Thanks a lot for your reply.. – aravind Dec 26 '16 at 18:24

0 Answers0