Troubleshooting ora-29471

Question

Some sessions call ORA-29471 because dbms_sql does not work for these sessions. We encounter this error in our application for multiple entries.

How can this be fixed? How can we identify a specific session without access to DBMS_SQL? Do we have any session level attribute / flag?

The link below provides a way to localize this problem. Reproduce

Answer 1

Runtime error. You may not be able to guess that this will happen before this happens. Perhaps your solution is to check one block with the identifier of the cursor you want to open dbms_sql.is_open(c_id).

, , :

select a.value, s.username, s.sid, s.serial#
  from v$sesstat a, v$statname b, v$session s
 where a.statistic# = b.statistic#  and s.sid=a.sid
   and b.name = 'opened cursors current'
;

v$open_cursor :

SELECT *
  FROM v$open_cursor oc, v$session s
 WHERE oc.sid = s.sid
order by 3,2;

, -, , .

Answer 2

, ORA-29471 , DBMS_SQL.

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#i1026408

ORA-29471 DBMS_SQL : , . , DBMS_SQL , , DBMS_SQL .

dbms_session.modify_package_state, , .. , , / .

declare
  c_1 number := 5;
  l_res boolean;
begin
  l_res := dbms_sql.is_open(c_1);
end;
/

declare
  c_2 number;
begin
  c_2 := dbms_sql.open_cursor();
end;
/

begin
 dbms_session.modify_package_state(dbms_session.reinitialize);
end;
/

declare
  c_2 number;
begin
  c_2 := dbms_sql.open_cursor();
end;
/

Answer 3

, DBMS_SQL, . , . , .

- . , , DBMS_SQL. .

, DBMS_SQL . , DBMS_SQL , . : , , , , . DBMS_SQL , , SQL-. , , DBMS_SQL. . .

Answer 4

you might find the oracle error column useful.

like this:

change system set events '29471 trace name error level 1';

if ora-29471 really happened,

the information will be printed in the warning log with the corresponding diagnostic trace file.

in the trace trace file,

You can easily get the information you need.