Home » RDBMS Server » Performance Tuning » Exceeding maxi,um number of cursors (11g r2, linux)
Exceeding maxi,um number of cursors [message #656924] Mon, 24 October 2016 04:50 Go to next message
qazims92
Messages: 37
Registered: July 2016
Location: Lahore Pakistan
Member
Hi Experts,

One of our developer is calling an sql update statement form java application.If number
of update statements increase 300 (value of OPEN_CURSORS) then this statement stop with error.
If we increase value of OPEN_CURSORS then it works fine. When I ran these update statements from toad if worked fine.

Need your assistnece in this regard.

Thanks & Regards,
Qazi
Re: Exceeding maxi,um number of cursors [message #656925 is a reply to message #656924] Mon, 24 October 2016 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ask the developer to close his cursors.

Re: Exceeding maxi,um number of cursors [message #656927 is a reply to message #656925] Mon, 24 October 2016 05:01 Go to previous messageGo to next message
qazims92
Messages: 37
Registered: July 2016
Location: Lahore Pakistan
Member
Thanks Michel for your kind resonse.

As per my knowledge he is not opening explicit cursors.
Does he need to close implicit cursors?

Regards,
Qazi
Re: Exceeding maxi,um number of cursors [message #656928 is a reply to message #656927] Mon, 24 October 2016 05:09 Go to previous messageGo to next message
John Watson
Messages: 7255
Registered: January 2010
Location: Global Village
Senior Member
Your programmer is not closing things correctly. For example, if not closing his PreparedStatement calls the implicit cursor will remain open.



Re: Exceeding maxi,um number of cursors [message #656929 is a reply to message #656928] Mon, 24 October 2016 05:32 Go to previous messageGo to next message
qazims92
Messages: 37
Registered: July 2016
Location: Lahore Pakistan
Member
Can you please help what to add in statement? I worked on google but find nothing.

This is my statement:

*****************************************************************************

boolean bBatchChk = false;

int iRows = sReqUnitNo.length;
try{
for (i = 0; i < iRows; i++)
{
stmt = con.prepareStatement("update audit_transaction_det_temp set Exception_Code = '"+sReqExpCode[i].trim()+"'

, Comments = '"+sReqComments[i].trim()+"' , Odometer_reading = '"+ sOdometerReading[i].trim() +"', qc_audit_result = 'D01',

key_audit_result = 'D01' where Company_Code = '"+sCmpcode.trim()+"' and transaction_no = '"+sReqAdtno.trim()+"' and unit_no

= '"+sReqUnitNo[i].trim()+"'");

int resultA = stmt.executeUpdate();

}



con.commit();
bBatchChk = true;
}
catch(Exception ex)
{
bBatchChk = false;
ex.printStackTrace();
}

*******************************************************************

Regards,
Qazi
Re: Exceeding maxi,um number of cursors [message #656930 is a reply to message #656929] Mon, 24 October 2016 05:35 Go to previous messageGo to next message
John Watson
Messages: 7255
Registered: January 2010
Location: Global Village
Senior Member
I've alreadsy told you: you need to close your prepared statements. I work as a DBA. Why am I supposed to be a better programmer than people who are paid to do development? It is your job, man, not mine Mad
Re: Exceeding maxi,um number of cursors [message #656931 is a reply to message #656930] Mon, 24 October 2016 05:51 Go to previous messageGo to next message
qazims92
Messages: 37
Registered: July 2016
Location: Lahore Pakistan
Member
Thanks John.
Re: Exceeding maxi,um number of cursors [message #656932 is a reply to message #656929] Mon, 24 October 2016 06:04 Go to previous message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is an awful code.
Use bind variables.
You need to prepare the statement only ONCE and execute it many times changing the value of the bind variables.

Edit: without speaking about SQL injection.

[Updated on: Mon, 24 October 2016 08:01]

Report message to a moderator

Previous Topic: Slow Query Sql
Next Topic: Cursor Optimization
Goto Forum:
  


Current Time: Sun Feb 18 01:56:07 CST 2018

Total time taken to generate the page: 0.59818 seconds