OraFAQ Forum: Performance Tuning » Exceeding maxi,um number of cursors

Home » RDBMS Server » Performance Tuning » Exceeding maxi,um number of cursors (11g r2, linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Exceeding maxi,um number of cursors [message #656924]

Mon, 24 October 2016 04:50

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

Report message to a moderator

Re: Exceeding maxi,um number of cursors [message #656925 is a reply to message #656924]

Mon, 24 October 2016 04:54

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Ask the developer to close his cursors.

Report message to a moderator

Re: Exceeding maxi,um number of cursors [message #656927 is a reply to message #656925]

Mon, 24 October 2016 05:01

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

Report message to a moderator

Re: Exceeding maxi,um number of cursors [message #656928 is a reply to message #656927]

Mon, 24 October 2016 05:09

John Watson
Messages: 8976
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.

Report message to a moderator

Re: Exceeding maxi,um number of cursors [message #656929 is a reply to message #656928]

Mon, 24 October 2016 05:32

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

Report message to a moderator

Re: Exceeding maxi,um number of cursors [message #656930 is a reply to message #656929]

Mon, 24 October 2016 05:35

John Watson
Messages: 8976
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

Report message to a moderator

Re: Exceeding maxi,um number of cursors [message #656931 is a reply to message #656930]

Mon, 24 October 2016 05:51

qazims92
Messages: 37
Registered: July 2016
Location: Lahore Pakistan

Member

Thanks John.

Report message to a moderator

Re: Exceeding maxi,um number of cursors [message #656932 is a reply to message #656929]

Mon, 24 October 2016 06:04

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 01:55:10 CDT 2025