Home » Developer & Programmer » JDeveloper, Java & XML » Huge Batch execution through the java application into the oracle DB
Huge Batch execution through the java application into the oracle DB [message #677252] Fri, 06 September 2019 07:54 Go to next message
revathitirun
Messages: 16
Registered: May 2011
Junior Member
Hi All,

When we are trying to insert the batch (collection records of ), through the java program into the oracle Database its working differently.

If the collection count is less than or equals to 65536 all required records are inserting to the table.
But when that collection size is more than the 65536, we are missing those 65536 records.
Inserted record count is always equal to the actual count - 65536

Example 1:
Collection count = 5000 - working fine & All Records inserted & application was running successfully.

Example 2:
Collection count = 65536 - Not working fine - ZERO Records inserted & application was running successfully.

Example 3:
Collection count = n* 65536 - Not working fine - ZERO Records inserted & application was running successfully.

Example 4:
Collection count = 65540 - Not working fine - FOUR(65540-65536) Records inserted & application was running successfully.

Example 4:
Collection count = M+(N*65540) - Not working fine - M Records inserted & application was running successfully.


Following is code snippet which we are executing through Java:

[‎9/‎6/‎2019 12:09 PM]  Anumula, Rajesh [ICG-IT NE]:  
pStatement = dbConnection
					.prepareStatement("INSERT INTO temp_table1 (BRANCH,SEQNO) VALUES(?,?)");
			for(long i =1;i<=70000;i++) {
				pStatement.setString(1, "XYZ");
				pStatement.setLong(2, i);
				pStatement.addBatch();
				System.out.println(i);
			}
			int count[]=pStatement.executeBatch(); 


Following are version of our software

Oracle :Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Java :1.8
JDBC : 4.3.22.RELEASE


Please help me to understand the problem.Is the Issue is with Java or Oracle ?


Thanks
Revathi.T






Re: Huge Batch execution through the java application into the oracle DB [message #677254 is a reply to message #677252] Fri, 06 September 2019 08:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

You have a simple INSERT statement there which I guess your Java code is running in a loop? It is hard to see how Oracle can have any affect on that. Can you add some code to report on what is going on?

By the way, I wish you would not say "record" when you mean "row" Smile
Huge Batch execution through the java application into the oracle DB [message #677255 is a reply to message #677252] Fri, 06 September 2019 08:25 Go to previous messageGo to next message
revathitirun
Messages: 16
Registered: May 2011
Junior Member
Hi All,

When we are trying to insert the batch (collection records of ), through the java program into the oracle Database its working differently.

If the collection count is less than or equals to 65536 all required records are inserting to the table.
But when that collection size is more than the 65536, we are missing those 65536 records.
Inserted record count is always equal to the actual count - 65536

Example 1:
Collection count = 5000 - working fine & All Records inserted & application was running successfully.

Example 2:
Collection count = 65536 - Not working fine - ZERO Records inserted & application was running successfully.

Example 3:
Collection count = n* 65536 - Not working fine - ZERO Records inserted & application was running successfully.

Example 4:
Collection count = 65540 - Not working fine - FOUR(65540-65536) Records inserted & application was running successfully.

Example 4:
Collection count = M+(N*65540) - Not working fine - M Records inserted & application was running successfully.


Following is code snippet which we are executing through Java:

[‎9/‎6/‎2019 12:09 PM]  Anumula, Rajesh [ICG-IT NE]:  
pStatement = dbConnection
					.prepareStatement("INSERT INTO temp_table1 (BRANCH,SEQNO) VALUES(?,?)");
			for(long i =1;i<=70000;i++) {
				pStatement.setString(1, "XYZ");
				pStatement.setLong(2, i);
				pStatement.addBatch();
				System.out.println(i);
			}
			int count[]=pStatement.executeBatch(); 


Following are version of our software

Oracle :Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Java :1.8
JDBC : 4.3.22.RELEASE


Please help me to understand the problem.Is the Issue is with Java or Oracle ?


Thanks
Revathi.T






Re: Huge Batch execution through the java application into the oracle DB [message #677256 is a reply to message #677255] Fri, 06 September 2019 08:27 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Why did you post this duplicate of http://orafaq.com/forum/t/206035/ , 30 minutes after the first?
Re: Huge Batch execution through the java application into the oracle DB [message #677259 is a reply to message #677256] Fri, 06 September 2019 09:30 Go to previous messageGo to next message
revathitirun
Messages: 16
Registered: May 2011
Junior Member
Hi John,

Thank for your response.Going forward we will follow the row instead of Record.
Fortunately my issue has been resolved after changing odbc jar.

Old Jar : ojdbc14-10.2.0.2.jar
New Jar : ojdbc6.ja

Hi Stevens,

I didn't realize the it has been already posted

Thanks
Revathi.T

Re: Huge Batch execution through the java application into the oracle DB [message #677260 is a reply to message #677259] Fri, 06 September 2019 10:31 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Ah, right. Thank you for providing the solution. The version I have is ojdbc8.jar Smile
Previous Topic: Help in Query: XML output to Columns using XMLTABLE
Next Topic: XQuery new namespace added
Goto Forum:
  


Current Time: Thu Mar 28 09:40:55 CDT 2024