Unclosed Implicit cursor
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Unclosed Implicit cursor

  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Unhappy Unclosed Implicit cursor

    Hi experts,

    I've some problem when calling a stored procedure through JDBC.

    The stored procedure is written like this;

    v_count varchar2(12);
    IS
    select count(*)
    into v_count
    from table_A;

    open v_cur for
    select v_count, .....
    ;


    After calling the procedure and closed the corresponding callablestatement object through JDBC, it is found that an unclosed cursor is left in DB:
    select count(*) from table_A

    How come the implicit cursor never closed, am I doing something wrong? Please advise. Thx.
    Carlos

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Since you are only selecting COUNT(*) you should not do a cursor. You certainly should not try to do both. Just use the SELECT INTO statement.

    Code:
    IS
       SELECT count(*)
         INTO v_count
         FROM table_A;
    END;
    /
    this space intentionally left blank

  3. #3
    Join Date
    Dec 2004
    Posts
    3

    Arrow

    Thx.

    Let me explain a bit more.
    My stored procedure consists of 2 parts.

    The first part retrieves a number according to some conditions and stores into a variable v_count. So I have coded


    select count(*)
    into v_count
    from table_A
    where [conditions1];


    Afterwards, I've to open a cursor with this variable involved and return this cursor as the return type.

    Open cursor for
    select v_count as count, .....
    from table_B
    where [conditions2];


    So the thing is, after I closed the JDBC connection, I supposed all the opened cursor should have been closed but an un-closed cursor is left in the DB with SQL-TEXT

    select count(*)
    from table_A
    where [conditions1];


    I hope it helps to explain my situation and is it so odd that such an implicit cursor still exist with closing!!!
    Carlos

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    This logic makes no sense. Are you mearly testing whether or not data gets returned from conditions2? In any event you have an explicit cursor, notice the word "OPEN". Explicict cursors need to be explicitly closed, otherwise they remain open.

    What are you really trying to do? While you are at it take a look at Oracle PL/SQL Best Practices, by Steven Feuerstein. It is a short book, but will help you immensely.

    Code:
    Open cursor for
    select v_count as count, .....
    from table_B
    where [conditions2];
    this space intentionally left blank

  5. #5
    Join Date
    Dec 2004
    Posts
    3

    Question

    Thx gandolf989.

    Actually the result of the first SQL becomes a column of the second SQL.

    I understand explicit cursor need to be closed or remain opened and I've done that. The thing is, the explicit cursor has closed while the implicit cursor, which is the first SQL(with open cursor statement), remains opened!!

    If it is an implicit one, I could have no control over it after used, right.

    Please correct me if I'm wrong, thx.
    Carlos

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Originally posted by carloscm
    Thx gandolf989.

    Actually the result of the first SQL becomes a column of the second SQL.

    I understand explicit cursor need to be closed or remain opened and I've done that. The thing is, the explicit cursor has closed while the implicit cursor, which is the first SQL(with open cursor statement), remains opened!!

    If it is an implicit one, I could have no control over it after used, right.

    Please correct me if I'm wrong, thx.
    Try to close it and find out. Also make sure that cursors get closed in the exceptions section and reraise any unhandled errors.

    This is an example of an implicit cursor.

    Code:
       FOR v_row IN ( SELECT v_count AS count
                     FROM table_b
                    WHERE ... )
       LOOP
          do something
       END LOOP;
    
    END;
    /
    this space intentionally left blank

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