ORA-01000 error

Hi,

I am using Oracle 8i and I am using Oracle JDBC thin driver for the database connection.

After few queries to the database. I am getting ORA-01000 error(Maximum cursors open error).

at oracle.jdbc.ttc7.TTIoer.<processError>
at oracle.jdbc.ttc7.Oopen.<receive>
at oracle.jdbc.ttc7.TTC7protocol.<open>
at oracle.jdbc.driver.OracleStatement.<init>
at oracle.jdbc.driver.OraclePreparedStatement.<init>
ORA-01000: maximum open cursors exceeded

SQLCode : 72000

Can anyone suggest me some workaround.
Pls. cc your reply to swami@transparity.com

Cheers swamy

sswamiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sudhi022299Commented:
the program has opened more than the allotted number of cursors per user.
First is check whether the cursors are being closed in the program

Second in the init file of the database increase the value of the parameter OPEN_CURSORS , shutdown and restart your database.

Regards,
Sudhi.
0
HaukeCommented:
You must call the method 'close' on every statement object you create.

PreparedStatement stmt= db.prepareStatement( "select .."

ResultSet rs= stmt.executeQuery();

while (rs.next())
{
  ..
}

rs.close();

stmt.close();

If in this case any method call between 'prepareStatement' and 'close' raises an exception then the program doesn't reach the 'close' method and the statement handle is lost!

The default value of maximum open cursors is 50. This should be enough for a program without a handle leak.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vemulayugandharCommented:
Hi
First tune the private SQL areas,to tune this you should identify how many parse calls your application is making.

You run TKPROF or SQL_TRACE=true to see how many times parsing occurs on each and every sql statement.
for eg:if count statistic of PARSE is > than count statistic of EXECUTE,then try to increase the open_cursors or reduce parse calls with ORACLE PRECOMPILERS,OCI.

           count
PARSE      10
EXECUTE    2
FETCH
0
sswamiAuthor Commented:
ThanX a lot
Cheers swamy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.