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

[Li2024]

Hi 

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.

[SASKiwi]

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.

[Li2024]

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

[SASKiwi]

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.