OR_UPD_NOWHERE= Data Set Option

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)
Alias:	ORACLE_73_OR_ABOVE=
Default:	LIBNAME setting
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

Syntax

Details

Example

Syntax

OR_UPD_NOWHERE=YES | NO

Syntax Description

YES: 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 Oracle 7.3 and later) for update locking. If a row changes after the serializable transaction starts, the update on that row fails.

NO: 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.

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.

Example: Create and Update a Table

In this example, you create a small Oracle table, TEST. You then update it 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.