Subsequent Iterations of the Same CURSOR Fails with ORA-1001 INVALID CURSOR (Doc ID 562458.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 10.2.0.1 to 11.1.0.6 [Release 10.2 to 11.1]
Information in this document applies to any platform.
***Checked for relevance on 16-Nov-2012***


Symptoms

A procedure executes successfully until it encounters an error.  Subsequent iterations of the same procedure fails with "ORA-01001: invalid cursor".  The session has to be terminated.

For example:


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