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.

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;
/

---

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!!!

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];

---

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.

Quote:

---

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;
/

---