Specifies whether SAS uses an extra WHERE clause when updating rows with no locking.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Set Control |
| Alias: | ORACLE_73_OR_ABOVE= |
| Default: | YES |
| Requirement: | 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. |
| Data source: | Oracle |
| See: | Locking in the Oracle Interface, OR_UPD_NOWHERE= data set option, UPDATE_LOCK_TYPE= LIBNAME 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 above) 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. It lets you avoid extra WHERE-clause processing and potential WHERE-clause floating-point precision problems.