ORA-1410 on UPDATE

Interesting topic appeared on Oracle forum @SQL.ru. In short, an issue is a PL/SQL code, which produces “ORA-01410: invalid ROWID” exception when it shouldn’t. 

 

Here is the code simplified as much as possible: 

drop table t cascade constraints purge;
create table t as select * from all_objects;
exec dbms_stats.gather_table_stats(user, 't');

create or replace procedure invalid_rowid(p_i number default 0
                                        , p_obj_name varchar2) is
    cursor c is
        select t.rowid
          from t
         where t.object_name = upper(p_obj_name)
           for update;
    l_c c%rowtype;
begin
    open c;
    loop
        fetch c into l_c;
        exit when c%notfound;
        if p_i = 0 then
            invalid_rowid(1, 'DUAL');
            update t set t.owner = 'not exists' where current of c;
        end if;
    end loop;
    close c;
end;
/
sho err 

select substr(value,1,100) from v$diag_info where name = 'Default Trace File';
--alter session set events '10046 trace name context forever, level 4'; 

exec invalid_rowid(p_obj_name => 'ALL_TABLES'); 

--exit

And it’s unexpected output: 

select substr(value,1,100) from v$diag_info where name = 'Default Trace File'; 

SUBSTR(VALUE,1,100)
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/test11g2/test11g2/trace/test11g2_ora_12256.trc 

alter session set events '10046 trace name context forever, level 4'; 

Session altered. 

SQL>
exec invalid_rowid(p_obj_name => 'ALL_TABLES');ES');
BEGIN invalid_rowid(p_obj_name => 'ALL_TABLES'); END; 

*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at "TIM.INVALID_ROWID", line 16
ORA-06512: at line 1 

The 10046 trace, when enabled, contains following: 

CLOSE #2:c=0,e=3,dep=0,type=1,tim=1260906859427056
=====================
PARSING IN CURSOR #3 len=55 dep=0 uid=60 oct=47 lid=60 tim=1260906859427447 hv=2579967490 ad='388b12c8' sqlid='0wnk6wucwfbh2'
BEGIN invalid_rowid(p_obj_name => 'ALL_TABLES'); END;
END OF STMT
PARSE #3:c=0,e=104,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1260906859427446
=====================
PARSING IN CURSOR #2 len=66 dep=1 uid=60 oct=3 lid=60 tim=1260906859427847 hv=2737952794 ad='388b1638' sqlid='1sr17gajm3q0u'
SELECT T.ROWID FROM T WHERE T.OBJECT_NAME = UPPER(:B1 ) FOR UPDATE
END OF STMT
PARSE #2:c=1000,e=160,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=250223868,tim=1260906859427847
BINDS #2:
 Bind#0
  oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=01 csi=871 siz=32 off=0
  kxsbbbfp=009bc73c  bln=32  avl=10  flg=05
  value="ALL_TABLES"
EXEC #2:c=16998,e=32438,p=0,cr=891,cu=4,mis=0,r=0,dep=1,og=1,plh=250223868,tim=1260906859460328
FETCH #2:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=250223868,tim=1260906859460515
=====================
PARSING IN CURSOR #4 len=66 dep=1 uid=60 oct=3 lid=60 tim=1260906859460583 hv=2737952794 ad='388b1638' sqlid='1sr17gajm3q0u'
SELECT T.ROWID FROM T WHERE T.OBJECT_NAME = UPPER(:B1 ) FOR UPDATE
END OF STMT
PARSE #4:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=250223868,tim=1260906859460582
BINDS #4:
 Bind#0
  oacdty=01 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=01 csi=871 siz=32 off=0
  kxsbbbfp=009b739c  bln=32  avl=04  flg=05
  value="DUAL"
EXEC #4:c=4999,e=4939,p=0,cr=891,cu=2,mis=0,r=0,dep=1,og=1,plh=250223868,tim=1260906859465582
FETCH #4:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=250223868,tim=1260906859465705
FETCH #4:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=250223868,tim=1260906859465730
FETCH #4:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=250223868,tim=1260906859465749
STAT #4 id=1 cnt=2 pid=0 pos=1 obj=0 op='FOR UPDATE  (cr=891 pr=0 pw=0 time=0 us)'
STAT #4 id=2 cnt=4 pid=1 pos=1 obj=0 op='BUFFER SORT (cr=891 pr=0 pw=0 time=2 us)'
STAT #4 id=3 cnt=2 pid=2 pos=1 obj=64761 op='TABLE ACCESS FULL T (cr=891 pr=0 pw=0 time=0 us cost=250 size=74 card=2)'
CLOSE #4:c=0,e=1,dep=1,type=3,tim=1260906859465972
CLOSE #4:c=0,e=3,dep=1,type=0,tim=1260906859466001
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='FOR UPDATE  (cr=891 pr=0 pw=0 time=0 us)'
STAT #2 id=2 cnt=3 pid=1 pos=1 obj=0 op='BUFFER SORT (cr=891 pr=0 pw=0 time=1 us)'
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=64761 op='TABLE ACCESS FULL T (cr=891 pr=0 pw=0 time=1 us cost=250 size=74 card=2)'
CLOSE #2:c=0,e=75,dep=1,type=3,tim=1260906859466114
EXEC #3:c=22997,e=38644,p=0,cr=1782,cu=6,mis=0,r=0,dep=0,og=1,plh=0,tim=1260906859466144
ERROR #3:err=1410 tim=1260906859466157
XCTEND rlbk=0, rd_only=1, tim=1260906859468060

Notable peculiarities of the trace excerpt: 

1. No EXEC call to the cursor denoting an UPDATE statement, yet it is reported as the root cause of an exception (WTF#1)
2. Cursor #4 is being closed twice – first with type=3, and then with type=0 (WTF#2). What does parameter type mean in the context of the CLOSE cursor call is not clear. Note 39817.1 says nothing particular about it.
3. The transaction is reported as COMMITed (WTF#3)

PS. that was 11.2.0.1; the same behavior is observed on 10.2.0.3.

Update #1

Thanks to Maxim Demenko WTF#2  seems to be resolved: here is stated that

type Type of close operation:
0: hard close; the cursor is not put into the server-side cursor cache
1: the cursor is cached in a previously empty slot of the server-side
cursor cache, since it executed at least 3 times
2: the cursor is placed in a slot of the server-side cursor cache at the
expense of aging out another cursor, since it executed at least 3 times
3: the cursor remains in the server-side cursor cache

About these ads