Open Cursors in Oracle 10g Database and Web-Based Applications
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Open Cursors in Oracle 10g Database and Web-Based Applications

  1. #1
    Join Date
    Mar 2007
    Posts
    2

    Question Open Cursors in Oracle 10g Database and Web-Based Applications

    Dear All,
    First of all, i'm indonesian..i'm sorry if i don't speak English correctly
    i am a technical consultant in a fully web-based application for HR Modules. We're running a project in a company who run Oracle 10g as their database.
    Previously, we have ran this application at another company using SQLServer and MySQL DB, and had no problem.
    So i assume, java programming for this web based application is effective, because SQLServer didn't consume much memory. For comparison, SQLServer is able to process 6000rows without errors.
    But when we run the same process in Oracle, with lesser data (only 240 data), we found error ORA-01000 maximum open cursors exceeded
    I try to expand open_cursors to 3000, but error still occured.
    Please help..any idea?
    I have read previous thread about this also..someone said that setting open_cursors to 300 is big enough for application..then what's wrong with this?

    Thank you very much in advance

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    well you either have an application which doesnt close its cursors, or it isnt set big enough.

    e.g. my db has it set to 2000 for my app, so try increasing it until the error gooes away or look at the app code also check out v$open_cursor to see whats going on

  3. #3
    Join Date
    Jun 2006
    Location
    Chennai, INDIA
    Posts
    72

    Wink

    Hi,
    Check the java code whether the result set is closed?

  4. #4
    Join Date
    Mar 2006
    Location
    hyderabad
    Posts
    4
    Hi i think the connection pool you are using to connect with the database has not been refreshed.
    Means once the work is finished you need to close the connections.

  5. #5
    Join Date
    Mar 2007
    Posts
    2

    Lightbulb

    Thanks for your help. I haven't done anything to solve this problem because i have this little confusion
    I'm new at java. I want to ask a small problem, maybe you can help
    If i create a PreparedStatement and a ResultSet. Do we need to close both of them?
    For example, i wrote this simple method :

    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
    ps = conn.prepareStatement (
    "SELECT employee_id FROM employee_tbl "
    );
    rs = ps.executeQuery();

    }catch(Exception ex) {

    }finally {
    ps.close();
    rs.close(); --> do we need to close this?
    }

    if the answer is yes, then, i know what is wrong ..because in my java code, they haven't set the ResultSet closed.

    Thanks before.

  6. #6
    Join Date
    Jun 2006
    Location
    Chennai, INDIA
    Posts
    72

    Wink

    Hi,
    Close and test it in your dev env.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    cant remmeber which way it is but either close the statement closes the result set or the other way round.

    Give it a try anyway

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width