If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

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.

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