PL/SQL exception ORA-06511 cursor already open

Question

Lately we are seeing the following error in our application log, this happens only sometimes (once in a week or so), leading us to believe that the cursor exception is thrown only by some specific input condition/unhandled exception.

Error - SQL state [24000]; error code [6511]; ORA-06511: PL/SQL: cursor already open
ORA-06512: at "PACKAGE", line 3
ORA-06512: at "APPS.TLN_AR_ONLINE_LIST2_PKG", line 17

nested exception is java.sql.SQLException: ORA-06511: PL/SQL: cursor already open
ORA-06512: at " PACKAGE ", line 3
ORA-06512: at " PACKAGE \, line 17, "providerErrorMessage":"CallableStatementCallback; uncategorized SQLException for SQL

So we opened up the package and the checked the cursor and the line number where the error is being thrown from.

1.     CURSOR c_t_num (trx_num_tran.trx_number%TYPE)
2.     IS
3.        SELECT interface_header_attribute1**
4.          FROM ra_customer_trx_all
5.         WHERE trx_number = trx_num;
6.           OPEN c_t_num (trx_list_rec.trx_number);
7.  
8.           FETCH c_t_num
9.            INTO t_rf_trx_number;
10. 
11.          IF c_t_num%NOTFOUND
12.          THEN
13.             t_rf_trx_number := NULL;
14.          END IF;
15. 
16.          CLOSE c_t_num;
17. OPEN c_t_num (p_trx_num);
18. 
19.       FETCH c_t_num
20.        INTO t_trx_number;
21. 
22.       IF c_t_num%NOTFOUND
23.       THEN
24.          t_trx_number := NULL;
25.       END IF;
26. 
27.       CLOSE c_t_num;
28. 

29. OPEN c_t_num (v_trx_num);
30. 
31.       FETCH c_t_num
32.        INTO t_trx_num_ritel;
33. 
34.       CLOSE c_t_num;
35. 
36.       RETURN (t_trx_num_ritel);
37.    END get_trx_number;

Now, the cursor is being opened at line numbers 6,17 and 29. But the exception always shows line number 3 (the select of the cursor definition) and line number 17.

Is this is an implicit type cursor (and this a legacy code) we think perhaps exception handling is the issue?

We have exception handling for no data found but none for TOO_MANY_ROWS , in all the blocks including when we open the cursor from line 17.

Can anyone help/suggest on this issue?

Answer 1

Before opening your cursor(in this example named c_main_script_1) do this:

if c_main_script_1%isopen then
    close c_main_script_1;
end if;

-Leah Jarvis