DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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

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.


Accepted Solutions
Solution
‎07-27-2017 10:26 AM
Occasional Contributor
Posts: 8

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

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

View solution in original post


All Replies
Super User
Super User
Posts: 7,050

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

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.

Solution
‎07-27-2017 10:26 AM
Occasional Contributor
Posts: 8

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 179 views
  • 0 likes
  • 2 in conversation