Specifies text to append to an INSERT, UPDATE, or DELETE statement.
| Valid in: | DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Category: | Data Access |
| Default: | None |
| Data source: | Oracle |
| See: | POST_DML_STMT_OPTS= LIBNAME option |
Table of Contents
specifies text to append to an INSERT, UPDATE, or DELETE statement. SAS/ACCESS does not verify the validity of the text that you provide.
Typically, you use POST_DML_STMT_OPTS= for logging errors that might occur during data manipulation (DML). In this case, you must have created an Oracle log table either outside SAS or by using explicit SQL code with PROC SQL. The log table records errors that might occur during data manipulation (DML). The text that you provide for the POST_DML_STMT_OPTS= LIBNAME option should take this form (information within ‘< >’ is optional):
LOG ERRORS <INTO <schema.>table-name> <('simple-expression')>
<REJECT LIMIT integer | UNLIMITED>This option acts only on the table that immediately follows ‘INSERT INTO’, ‘UPDATE’, or ‘DELETE FROM’ in your PROC SQL query.
To see the SQL code that is generated, use the SASTRACE and SASTRACELOG system options, as shown here:
option sastraceloc=saslog sastrace=',,,d';Here is an example that uses the POST_DML_STMT_OPTS= data set option to log an unlimited number of insert errors into the table ERR_ORATAB:
proc sql;
insert into oralib1.ORATAB (post_dml_stmt_opts = 'LOG ERRORS INTO ERR_ORATAB
REJECT LIMIT UNLIMITED')
select *
from oralib1.CLASS;
quit;The example above generates the following SQL code:
insert into ORATAB ("NAME", "SEX", "AGE", "HEIGHT", "WEIGHT")
select TXT_2."NAME", TXT_2."SEX", TXT_2."AGE", TXT_2."HEIGHT", TXT_2."WEIGHT"
from CLASS TXT_2 LOG ERRORS INTO ERR_ORATAB REJECT LIMIT UNLIMITED