The LIBNAME Statement for Relational Databases

OR_UPD_NOWHERE= LIBNAME Option

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

Alias:	ORACLE_73_OR_ABOVE=
Default value:	YES
Valid in:	SAS/ACCESS LIBNAME statement
DBMS support:	Oracle

Syntax
Syntax Description
Details
See Also

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.

Note: Due to the published Oracle bug 440366, an update on a row sometimes 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. [cautionend]