Specifies text to append to an INSERT, UPDATE, or DELETE statement.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Access |
| Default: | none |
| Data source: | Oracle |
| See: | POST_DML_STMT_OPTS= data set option |
Table of Contents
specifies text to append to an UPDATE, INSERT, 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 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 tables that immediately follow ‘INSERT INTO’, ‘UPDATE’, or ‘DELETE FROM’ in your PROC SQL queries.
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= LIBNAME option to log an unlimited number of errors into the log table ERR_DML. In this case, the errors logged are those that occur while inserting records into the Oracle table ORATAB.
LIBNAME oralib1 ORACLE PATH=orclpdb USER=orauser PASSWORD=xxxx
POST_DML_STMT_OPTS=('LOG ERRORS INTO ERR_DML REJECT LIMIT UNLIMITED');
proc sql;
insert into oralib1.ORATAB
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_DML REJECT LIMIT UNLIMITED