Re: update failing for no apparent reason?

In article <9qq7hto3vnj1kknbsrqlhbgt179sab2ltg_at_4ax.com>, Robert says...
>
>Can anyone explain the following behaviour?
>
>SQL> update office_table set ot_state = 13 where ot_mtp_id = 369 and
>ot_state = 2;
>update office_table set ot_state = 13 where ot_mtp_id = 369 and
>ot_state = 2
> *
>ERROR at line 1:
>ORA-01403: no data found
>

You have a trigger on office_table that is getting a 1403 when you update that column.

Updates by themselves NEVER through a 1403 -- if your update updates zero rows -- that is NOT an error, that is success! Consider:

tkyte_at_TKYTE816> create table t ( x int ); Table created.

tkyte_at_TKYTE816> update t set x = 5;
0 rows updated.

That was Successful. Now, add a trigger:

tkyte_at_TKYTE816> create or replace trigger t_trigger   2 before update on t
  3 declare
  4 x dual.dummy%type;
  5 begin
  6 select * into x from dual where 1=0;   7 end;
  8 /
Trigger created.

tkyte_at_TKYTE816> insert into t values ( 1 ); 1 row created.

tkyte_at_TKYTE816> update t set x = 5;
update t set x = 5

       *
ERROR at line 1:
ORA-01403: no data found

there is your error.

Go look for a trigger with a SELECT INTO that is failing

>
>SQL> select count(*) from office_table where ot_mtp_id = 369 and
>ot_state = 2;
>
> COUNT(*)
>----------
> 1
>
>SQL> update office_table set ot_office = 'TEST' where ot_mtp_id = 369
>and ot_state = 2;
>
>1 row updated.
>
>SQL> rollback;
>
>Rollback complete.
>
>SQL> select ot_office_id from office_table where ot_mtp_id = 369 and
>ot_state = 2;
>
>OT_OFFICE_ID
>------------
> 748
>
>SQL> update office_table set ot_state = 13 where ot_office_id = 748;
>update office_table set ot_state = 13 where ot_office_id = 748
> *
>ERROR at line 1:
>ORA-01403: no data found
>
>
>SQL> select count(*) from office_table where ot_office_id = 748;
>
> COUNT(*)
>----------
> 1
>
>
>SQL> update office_table set ot_state = 13 where ot_office_id in (
>select ot_office_id from office_table where ot_mtp_id = 369 and
>ot_state = 2 );
>update office_table set ot_state = 13 where ot_office_id in ( select
>ot_office_id from office_table where ot_mtp_id = 369 and ot_state = 2
>)
> *
>ERROR at line 1:
>ORA-01403: no data found
>
>
>I am at a loss here. It seems like I can update the row as long as I
>don't try to update the ot_state column; if I try that, it tells me
>that there's no such row. That's regardless of whether I also refer
>to ot_state in the where clause (although I don't think that should
>matter anyway). Anybody have any idea what could be wrong?
>
>This is with Oracle 8i on an HP-UX machine.
>
>Bob Vesterman.

Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/ Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Received on Tue May 29 2001 - 15:12:28 CDT