Cursor Invalidation Error (ORA-01001) Raised After Error Such as ORA-01461 Raised and Does Not Resolve (Doc ID 978182.1)

Last updated on JULY 17, 2017

Applies to:

PL/SQL - Version: 10.2.0.1 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 15-Aug-2011***

Symptoms

When calling a function which has a pragma autonomous transaction, if an error is raised such as ORA-01461, then all further calls will return ORA-01001.In this scenario, if the SUBSTR function was not used or there was no defined autonomous transaction, the ORA-01001 did not occur.

CREATE TABLE INTERFACE_LOG (
id_log NUMBER,
comment_call VARCHAR2(1024));

CREATE SEQUENCE INTERFACE_LOG_SEQ
Start with 1 NOCACHE;

CREATE OR REPLACE function info
(p_comment in varchar2 default 'CALLED') return number
IS
v_id_log number;
pragma autonomous_transaction;
begin
select interface_log_seq.nextval into v_id_log from dual;
insert into interface_log(id_log, comment_call )
values (v_id_log, substr(p_comment, 1, 1000));
 
-- COMMIT (due to pragma autonomous_transaction)
commit;
return v_id_log;
end;
/

select info ('ciao' ) from dual ;

VAR g_out_num number;
BEGIN
:g_out_num := info (rpad('a', 4000, 'z')||'more');
END;
/
/ --execute again to raise ORA-01001

--now all subsequent calls to the function, even select, raise ORA-01001 too
select info ('ciao' ) from dual ;



The issue can also be demonstrated with a procedure, such as the following scenario:
CREATE TABLE ix_Note_Test(notes VARCHAR2(4000));

Create or replace PROCEDURE IX_NOTE_TEST_NOTE_UPD (p_notes VARCHAR2)
As
BEGIN
  Update ix_Note_Test set Notes = p_notes;
END;
/

-- This works...
begin
  ix_Note_Test_Note_upd ('BARNEY');
end;
/
-- this causes an ora-1461 (intentionally, in this case)
begin
  ix_Note_Test_Note_upd (rpad('a',4000,'a')||'z');
end;
/
-- this now gets ora-1001, and continues to until the session is restarted begin
  ix_Note_Test_Note_upd ('BARNEY');
end;
/



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms