Data Set Options for Relational Databases OR_UPD_NOWHERE= Data Set Option 199
Syntax Description
YES
SAS does not use an additional WHERE clause to determine whether each row has
changed since it was read. Instead, SAS uses the SERIALIZABLE isolation level
(available with Oracle 7.3 and above) for update locking. If a row changes after the
serializable transaction starts, the update on that row fails.
NO
SAS uses an additional WHERE clause to determine whether each row has changed
since it was read. If a row has changed since being read, the update fails.
Details
Use this option when updating rows without locking
(UPDATE_LOCK_TYPE=NOLOCK).
By default (OR_UPD_NOWHERE=YES), updates are performed in serializable
transactions. This enables you to avoid extra WHERE clause processing and potential
WHERE clause floating point precision problems.
Specify OR_UPD_NOWHERE=NO for compatibility when updating a SAS Version 6
view descriptor.
ORACLE_73_OR_ABOVE is an alias for this option.
Note: Due to the published Oracle bug 440366, sometimes an update on a row fails
even if the row has not changed. Oracle offers the following solution: When creating a
table, increase the number of INITRANS to at least 3 for the table.
Example
In the following example, you create a small Oracle table called TEST and then
update the TEST table once using the default setting (OR_UPD_NOWHERE=YES) and
once specifying OR_UPD_NOWHERE=NO.
libname oralib oracle user=testuser pw=testpass update_lock_type=no;
data oralib.test; c1=1; c2=2; c3=3;
run;
options sastrace=",,,d" sastraceloc=saslog;
proc sql;
update oralib.test set c2=22;
update oralib.test(or_upd_nowhere=no) set c2=222;
quit;
This code uses the SASTRACELOC=and SASTRACE= options to send the output to
the SAS log.
See Also
To assign this option to a group of relational DBMS tables or views, see the
LIBNAME option “OR_UPD_NOWHERE= LIBNAME Option” on page 109
