Data Set Options for Relational Databases |
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 |
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.
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.
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.
See Also |
To assign this option to a group of relational DBMS tables or views, see the OR_UPD_NOWHERE= LIBNAME Option.
Locking in the Oracle Interface
UPDATE_LOCK_TYPE= Data Set Option
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.