Data Set Options for Relational Databases

OR_UPD_NOWHERE= Data Set Option

Specifies whether SAS uses an extra WHERE clause when updating rows with no locking.

Alias:	ORACLE_73_OR_ABOVE=
Default value:	LIBNAME setting
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Oracle

Syntax
Syntax Description
Details
Example
See Also

Syntax

OR_UPD_NOWHERE=YES | NO

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 later) 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 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.

Specify OR_UPD_NOWHERE=NO for compatibility when you are updating a SAS 6 view descriptor.

Note: 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. [cautionend]

Example

In this example, you create a small Oracle table, TEST, and then update the TEST table once by using the default setting (OR_UPD_NOWHERE=YES) and once by 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 SASTRACE= and SASTRACELOC= system options to send the output to the SAS log.