OR_UPD_NOWHERE= LIBNAME Option

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

Valid in:	SAS/ACCESS LIBNAME statement
Alias:	ORACLE_73_OR_ABOVE=
Default:	YES
Data source:	Oracle
Note:	Due to the published Oracle bug 440366, an update on a row sometimes 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.
See:	Locking in the Oracle Interface, OR_UPD_NOWHERE= data set option, UPDATE_LOCK_TYPE= LIBNAME option

Syntax

Details

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 above) 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. It lets you avoid extra WHERE-clause processing and potential WHERE-clause floating-point precision problems.