Problem Note 43761: Bulkloading DATE or DATETIME data types into Oracle might fail with error ORA-26041
The following error might occur when you use the BULKLOAD option with the SAS/ACCESS® Interface to Oracle:
ERROR: ORA-26041: DATETIME/INTERVAL datatype conversion error.
The problem can occur when you are running Oracle with a non-English locale and bulk loading a SAS data set that contains DATE or DATETIME data types.
To circumvent the issue, set the Oracle date locale environment (NLS_DATE_LANGUAGE) to English or American.
On UNIX platforms, a shell-script can be used as a wrapper, to set the environment before calling Oracle BULKLOAD:
- Create a file /path/to/sas/sqlldr with the following contents
(/path/to/sas designates the SAS installation directory):
#!/bin/ksh
NLS_DATE_LANGUAGE=AMERICAN
export NLS_DATE_LANGUAGE
$ORACLE_HOME/bin/sqlldr $*
- Set execute attributes:
$ chmod 755 /path/to/sas/sqlldr
- Ensure that the directory /path/to/sas is inserted first into the UNIX PATH environment variable.
Edit /path/to/sas/bin/sasenv_local and add the following:
PATH=/path/to/sas:$PATH
export PATH
This problem might present itself in SAS® Digital Marketing if the response table is held in Oracle in a non-English locale. The update to the response history table in the SAS® Marketing Automation Common Data library fails. The following error message appears in the sascustintelreporting log file:
INFO [00000208] 4:sastrust@saspw - Record 1: Rejected - Error on table CI_INTABLE152701, column RESPONSE_DTTM.
INFO [00000208] 4:sastrust@saspw - ORA-26041: DATETIME/INTERVAL datatype conversion error
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
SAS System | SAS/ACCESS Interface to Oracle | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows XP 64-bit Edition | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft® Windows® for x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2003 Standard Edition | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2003 for x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2008 | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2008 for x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows XP Professional | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Enterprise 32 bit | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Enterprise x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Home Premium 32 bit | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Home Premium x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Professional 32 bit | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Professional x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Ultimate 32 bit | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Ultimate x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Windows Vista | 9.2 TS2M3 | 9.2 TS2M3 |
Windows Vista for x64 | 9.2 TS2M3 | 9.2 TS2M3 |
64-bit Enabled AIX | 9.2 TS2M3 | 9.2 TS2M3 |
64-bit Enabled HP-UX | 9.2 TS2M3 | 9.2 TS2M3 |
64-bit Enabled Solaris | 9.2 TS2M3 | 9.2 TS2M3 |
HP-UX IPF | 9.2 TS2M3 | 9.2 TS2M3 |
Linux | 9.2 TS2M3 | 9.2 TS2M3 |
Linux for x64 | 9.2 TS2M3 | 9.2 TS2M3 |
OpenVMS on HP Integrity | 9.2 TS2M3 | 9.2 TS2M3 |
Solaris for x64 | 9.2 TS2M3 | 9.2 TS2M3 |
*
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.
When running Oracle with a non-English locale, bulkloading of DATE and DATETIME data types from SAS might fail.
Type: | Problem Note |
Priority: | medium |
Date Modified: | 2011-10-18 11:38:19 |
Date Created: | 2011-07-18 04:28:01 |