Specifies whether SAS uses an extra WHERE clause when updating rows with no locking.
| Valid in: | DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Categories: | Data Access |
| Data Set Control | |
| Alias: | ORACLE_73_OR_ABOVE= |
| Default: | LIBNAME option value |
| Requirement: | Due to the published Oracle bug 440366, sometimes an update on a row fails even if the row has not changed. Oracle offers this solution: When you create a table, increase the number of INITRANS to at least 3 for the table. |
| Data source: | Oracle |
| See: | Locking in the Oracle Interface, OR_UPD_NOWHERE= LIBNAME option, SASTRACE= system option, SASTRACELOC= system option, UPDATE_LOCK_TYPE= data set option |
Table of Contents
specifies that 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 Oracle7.3 and later) for update locking. If a row changes after the serializable transaction starts, the update on that row fails.
specifies that 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.
Use this option when you are updating rows without locking (UPDATE_LOCK_TYPE= NOLOCK).
By default (OR_UPD_NOWHERE=YES), updates are performed in serializable transactions so that you can avoid problems with extra WHERE clause processing and potential WHERE clause floating-point precision.
In this example, you create a small Oracle table, TEST. You then update it once by using the default value (OR_UPD_NOWHERE=YES) and once by specifying OR_UPD_NOWHERE=NO.
libname oralib oracle user=myusr1 pw=mypwd1 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 SASTRACE= and SASTRACELOC= system options to send the output to the SAS log.