SAS DI Table loader oracle

[Kashishjoker]

Hello All-
 
I'm getting an below error while loading the data into oracle table with the help of SAS DI table loader
 
ODBC_168: Executed: on connection 6
Prepared statement ODBC_124

ODBC: ROLLBACK performed on connection 6.
NOTE: There were 1441748 observations read from the data set WORK.W8BZ164Z.
NOTE: 1441747 observations added.
NOTE: The data set table has . observations and 40 variables.
ODBC: COMMIT performed on connection 6.

Summary Statistics for ODBC are:
Total SQL execution seconds were: 978.921050
Total SQL prepare seconds were: 0.002722
Total SQL describe seconds were: 0.002161
Total seconds used by the ODBC ACCESS engine were 988.840234

ODBC: COMMIT performed on connection 6.
ERROR: CLI disconnect failed: [Oracle][ODBC]Invalid transaction state.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
 
Please guide me 

[LinusH]

What libname and data set options are in effect for this step?

Try to add this temporary to the code so you might see more in the log:

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

[Kashishjoker]

Hello,

Thanks for revert.
 As suggested I have added the option, can you please suggest now.

ODBC_168: Executed: on connection 6
Prepared statement ODBC_124

ODBC: ROLLBACK performed on connection 6.
NOTE: There were 1441748 observations read from the data set WORK.W8BZ164Z.
NOTE: 1441747 observations added.
NOTE: The data set table has . observations and 40 variables.
ODBC: COMMIT performed on connection 6.

Summary Statistics for ODBC are:
Total SQL execution seconds were: 978.921050
Total SQL prepare seconds were: 0.002722
Total SQL describe seconds were: 0.002161
Total seconds used by the ODBC ACCESS engine were 988.840234

ODBC: COMMIT performed on connection 6.
ERROR: CLI disconnect failed: [Oracle][ODBC]Invalid transaction state.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 16:28.87
user cpu time 19.62 seconds
system cpu time 1.18 seconds
memory 107196.37k
OS Memory 125096.00k
Timestamp 05/03/2021 06:26:02 PM
Step Count 89 Switch Count 1
Page Faults 0
Page Reclaims 291072
Page Swaps 0
Voluntary Context Switches 174
Involuntary Context Switches 429
Block Input Operations 8
Block Output Operations 16

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2021-03-05T18:26:02,165+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 1131565056| _DISARM| 19304448| _DISARM| 12| _DISARM| 36| _DISARM| 24| _DISARM| 706637512| _DISARM| 20.800000|
_DISARM| 988.870710| _DISARM| 1930567173.295236| _DISARM| 1930568162.165946| _DISARM| 19.620000| _DISARM| | _ENDDISARM
MPRINT(ETLS_LOADER): ;

[Kashishjoker]

LIBNAME *** ODBC DATAsrc=ODBC SCHEMA=***** USER=*** PASSWORD="*****" INSERTBUFF=32767;

[LinusH · Accepted solution]

And there is no data set options in your proc append?

Two ideas:

• you seem to read one more record than gets inserted. Is there a data problem in that record? What constraints do you have on the oracle table?
• You might hit some kind of limit in Oracle, check with your DBA to see if he/she can find anything in the Oracle logs that could help.