BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kashishjoker
Fluorite | Level 6
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 
1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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.
Data never sleeps

View solution in original post

4 REPLIES 4
LinusH
Tourmaline | Level 20

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;
Data never sleeps
Kashishjoker
Fluorite | Level 6

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
Fluorite | Level 6

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

LinusH
Tourmaline | Level 20

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.
Data never sleeps

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1477 views
  • 0 likes
  • 2 in conversation