topic CLI execute error: [Oracle][ODBC][Ora]ORA-01438: How to target defect column? in SAS Programming

CLI execute error: [Oracle][ODBC][Ora]ORA-01438: How to target defect column?

Li2024 — Wed, 12 Jun 2024 19:39:40 GMT

I got the error below when I tried to use

proc sql;

inert into

command to insert table into oracle server.

I know the my format is not align with defined tables. However I have 300 columns and I don't know which one caused the problem.

Is there anyway can let me target the defect columns.

Thank you

ERROR: CLI execute error: [Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed
for this column
ERROR: ROLLBACK issued due to errors for data set LIB.MY_TABLE.DATA.

Re: CLI execute error: [Oracle][ODBC][Ora]ORA-01438: How to target defect column?

SASKiwi — Wed, 12 Jun 2024 20:03:14 GMT

How many of your columns are numeric? I suspect this is a problem where you are trying to insert a decimal number into an integer column or something similar. I suggest you extract the non-decimal numeric columns from the Oracle schema then compare these with the SAS numerics you are trying to insert.

Re: CLI execute error: [Oracle][ODBC][Ora]ORA-01438: How to target defect column?

Li2024 — Wed, 12 Jun 2024 20:10:46 GMT

I have at least 250 numerical columns.

I defined format, informat and length.Do you know which one caused this error?

Do you mean this error is caused by length of decimal only?

Thank you

Re: CLI execute error: [Oracle][ODBC][Ora]ORA-01438: How to target defect column?

SASKiwi — Wed, 12 Jun 2024 21:23:47 GMT

You can check up on the default Oracle data type translations here: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p06jk0u30uhuj5n18fqw9sxr25lk.htm

How are you connecting to Oracle - using a LIBNAME statement or by some other method?

I'm afraid you will probably need to run some tests to identify which columns are causing the errors. You could try inserting a subset of the columns to isolate the problematic ones.