Wednesday, March 14, 2007

Update ... returning into misunderstandings

I don't know why, but for some time i have been living in the belief that the following statement would end up in an ORA-01403 no data found error:

update some_table
set some_column = some_value
where 1 = 0
returning some_other_column into some_variable;
Probably this is due to the fact that the documentation, up to date, doesn't cover this specific situation (correct me if i am wrong) and much is left to the imagination of the programmer.

Hence, if you are still convinced that the sql statement above should cause a run time exception, as it would happen with

select some_column
into some_variable
from some_table
where 1 = 0;
ORA-01403: no data found

well, you'd be better off checking your programs right now, especially if you work in a nuclear plant or at some missile shield project :-)

As perhaps Oscar Wilde would say if he had had a chance to be a PL/SQL programmer, "there is only one thing in the world worse than a program raising an unexpected exception, and that is a program not raising an expected exception."

Indeed, the update will run smoothly and guess what, some_variable will not be updated at all, that means, it will retain its previous value, if any.

In other words, if some_variable's value prior to executing the update was 'X', it will still be 'X' after executing the update.
This means that if you want to be sure that this variable holds a consistent value after the update, whatever the outcome of update will be, you should take no chances and reset the variable yourself, before executing that statement.

some_variable := null;
update ...
returning ... into...;

Interestingly enough, if the where condition in the update doesn't result in a unique fetch, you'll get:
ORA-01422: exact fetch returns more than
requested number of rows
If you trap this error and inspect the content of some_variable, you'll see it contains the value of the column retrieved from the first matching record.
And i guess that in certain situations this could be a useful feature.

If you need to handle multiple rows in one shot, remember that you can use the UPDATE ... BULK COLLECT INTO syntax:

declare
type my_array_type is
table of some_table.some_column%type
index by binary_integer;
my_array my_array_type;
begin
...
update some_table
set some_column = some_value
where ...
returning some_expression bulk collect into my_array;

end;

No nukes, please!

4 comments:

Patrick Wolf said...

Hi,

if you want to make sure that at least one record is updated (because you did a primary key update) you should add a

IF SQL%ROWCOUNT < 1 THEN RAISE NO_DATA_FOUND; END IF;

after the update statement. Just to be on the save side.

Patrick

Anonymous said...

I just found described UPDATE...RETURNING behaviour myself and googled this page in attempt to find any documentation.
However in my environment which is Oracle client 9.2.0.6.0 on Windows XP and server 10.1.0.4.0 on RedHat Enterprise Linux 2.1 results are slightly different and I consider the difference is quite important to post here:
update some_tableset some_column = some_valuewhere 1 = 0returning some_other_column into some_variable;
will not leave previous state of some_variable, it will RESET variable to NULL, erasing it's previous value. I presumed (and my code handled both) NO DATA FOUND exception and retention of old value, reset to NULL was competely surprising. I can not reproduce by running code inside Oracle SQLdeveloper as PL/SQL anonymous block, but inside stored procedure in package body variable is reset to NULL (confirmed by putting DBMS_OUTPUT.PUT_LINE just before and after update statement).
Hope this information will help someone.
/McSim

Byte64 said...

Thanks for sharing your information McSim.

Flavio

Anonymous said...

Thank you "Patrick" for the "sql%rowcount" tip --- I've been searching for a way to MAKE sql tell me "no data found" for the update I was needing to make sure I did. "How to make Oracle return a no_data_found warning for an Update statement".

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles