Oracle maximum open cursor exceeded

[discusmania]

hi guys....

When i try to loop through and array to display the records and list box item, it only manage to list down until 48 records before showing this error:

Microsoft OLE DB Provider for Oracle
error '80004005'
ORA-01000: maximum open cursors exceeded


while the actual records are around 200. Below is the function to put the recordset in and array:


dim aResp()
Function disp_resp()
set rsresp=Server.createobject("ADODB.Recordset&quot
strresp="select badge from user_access where cop ='"&request("cop&quot&"' and lvl>=2 order by badge"
rsresp.open strresp,rs2,1,2
i=0
redim aResp(0)
while not rsresp.eof
redim preserve aResp(ubound(aResp)+1)
aResp(i)=rsresp("badge&quot
i=i+1
rsresp.movenext
wend

rsresp.Close
set rsresp=nothing
End Function

and this is where i call the function:


<SELECT name=&quot;cb_resp&quot; size=5 id=&quot;cb_resp&quot; style=&quot;width=200;font-size=10&quot; multiple>
<%disp_resp
for i=0 to ubound(aResp)-1
respname =chk_name(aResp(i))
%>
<option value=&quot;<%=aResp(i)%>&quot;><%=aResp(i)%>(<%=respname%>)
<%next%>
</SELECT>


anyone can help??. Please, it's urgent.

Thanks
Ron

 

[rcurva]

Your error indicates that you are hitting a maximum database parameter open_cursors. Meaning you have this number of open cursors (select statements) at the same time that exceeds your open_cursors parameter.

You could either increase this parameter by shutting down the database, update the init.ora open_cursors parameter, startup database. Or you could revise your code to minimise no. of open cursors at the same time.

Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War