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!