topic Re: ORA-01438 value larger than specified precision allowed for this column in SAS Programming

ORA-01438 value larger than specified precision allowed for this column

Kaladhan — Thu, 27 Jul 2017 13:44:24 GMT

Hello,

I want to append into Oracle a record that contains a numeric value from SAS.

The value in SAS is defined as NUMERIC(8) with a format/informat of 19.2. It's an amount field, so it contains value like 120.14.

In Oracle, the field is defined as NUMBER(19, 2). When I view the table in SAS, it says it's NUMERIC(8) with a format/informat of 21.2.

When I try the append, I have the following error:
ERROR: ERROR: ERROR: ERROR: ORACLE execute error: ORA-01438: value larger than specified precision allowed for this column.
With the occurrence of the above ERROR, the error limit of 1 set by the ERRLIMIT= option has been reached. ROLLBACK has been
issued(Any Rows processed after the last COMMIT are lost). Total rows processed: 1 Rows failed : 1
ROLLBACK has been issued(Any Rows processed after the last COMMIT are lost).

I understand the error. It means I'm trying to pass a SAS value that is bigger than what Oracle would allow. I don't know how exactly to correct this in this case. Should I alter the Oracle table to allow bigger values? Should I change the SAS format?

Any help would be appreciated.

Re: ORA-01438 value larger than specified precision allowed for this column

Tom — Thu, 27 Jul 2017 14:04:58 GMT

SAS stores numbers as 64 bit floating point numbers. There are not enough bits to store 19 decimal digits. But since it it floating point you can easily store a number that is larger than 10**19.

Find the maximum value in the data you are trying to store and see if that is the issue.

I guess another possibility is that you are trying to store too many decimal digits? Perhaps your value is somthing like 12.125 and Oracle is complainging that has no place to store the 5? If that is the problem then you could try rounding the value to 2 decimal places. Formats just impact how it is display, the ROUND() function will change the actual value.

Re: ORA-01438 value larger than specified precision allowed for this column

Kaladhan — Thu, 27 Jul 2017 14:26:43 GMT

... I identified the wrong field as the problem. Another field was defined too small.