Home » SQL & PL/SQL » SQL & PL/SQL » Dumping a table from SAS to ORacle
Dumping a table from SAS to ORacle [message #224633] Wed, 14 March 2007 20:00 Go to next message
Messages: 73
Registered: January 2007
I got the following errors and have no idea on how to fix it.

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

It seemed to me what caused the problems are three columns - all of which are dates - in the dataset that are exported to Oracle.
In SAS, dates are represented in Number with format MMDDYY10., length 10.

There're also two currency columns in SAS, of format 18.2

Could someone please shed some light on this?

Re: Dumping a table from SAS to ORacle [message #224680 is a reply to message #224633] Thu, 15 March 2007 02:22 Go to previous message
Messages: 21122
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Number which represent dates in SAS should be converted into a date using the TO_DATE function:
TO_DATE(sas_date, 'mmddyy')
"10", as a date column (?) length doesn't have a meaning in Oracle. But, if 10 is part of every SAS date, first remove '10' and then apply MMDDYY format to it.

However, this is not the reason for the error you got - it has something to do with currency columns. In SAS, they are numerics (18, 2). In Oracle, it would mean that those columns have 18 "places" for digits, and 2 of them are to be used behind decimal point. What is those columns datatype in Oracle? You'll see it using "DESCRIBE this_table" in SQL*Plus. Perhaps you'll have to alter Oracle table as
ALTER TABLE this_table MODIFY currency_column NUMBER(20, 2)
Previous Topic: ORA-03001: unimplemented feature
Next Topic: Top 20% of Customers
Goto Forum:

Current Time: Thu Jun 22 15:45:04 CDT 2017

Total time taken to generate the page: 0.24525 seconds