Problem Note 14873: Domain error or Floating Point Exception occurs when accessing Oracle
dates with the value: 00-000-0000
The following errors may be issued when attempting to access an Oracle
table with a date column value of 00-000-0000.
ERROR: Domain Error.
ERROR: An unknown, abnormal error has occurred during execution at
line line ## column #.
ERROR: Termination due to Floating Point Exception
where # will be a number.
In some cases, the error may only be surfaced as a DOMAIN ERROR with no
other errors. In other cases, you may actually see the FLOATING POINT
EXCEPTION.
The data value 00-000-0000 in the Oracle table is an invalid date and it
is not known how that value was entered or how to duplicate this problem
to enter this invalid date. It appears that this is a data conversion
problem because of incorrect data entered for the DATE data type.
However, we are unsure how to insert a date value of '00-000-0000' into
an Oracle table. Oracle rejects the row when we attempt this.
The question is, what can be done constructively with this value in SAS?
We could possibly generate an error in SAS similar to: "Date value
out of range". However, we would need to know how this value can
be inserted in a DATE field in order to perform validation testing.
It appears that the problem is similar to the problem documented in SAS
Note 12222. (link provided below).
#012222
Work-arounds are to code around the problem. When using the CASE
statement the only workaround is to turn off implicit passthrough by
adding the option, DIRECT_SQL=NO, to the LIBNAME statement.
If referencing a PROC SQL view created against the RDBMS data and this
error is issued, another possible work-around would be to create a SAS
data set with PROC SQL against the RDBMS data instead of a PROC SQL
view.
Operating System and Release Information
SAS System | SAS/ACCESS Interface to Oracle | 64-bit Enabled HP-UX | 9.1 TS1M3 | |
64-bit Enabled Solaris | 9.1 TS1M3 | |
Linux | 9.1 TS1M3 | |
64-bit Enabled AIX | 9.1 TS1M3 | |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
Type: | Problem Note |
Priority: | medium |
Topic: | Data Management ==> Data Sources ==> External Databases ==> Oracle SAS Reference ==> LIBNAME Engines
|
Date Modified: | 2005-07-14 12:30:08 |
Date Created: | 2005-03-29 13:40:57 |