Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile
• My SAS

• Print  |
• Feedback  |
• Share

  • E-mail
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

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:

1. 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 $*
   

2. Set execute attributes:

         $ chmod 755 /path/to/sas/sqlldr
   

3. 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:

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for this issue for SAS/ACCESS Interface to Oracle 9.21_M2 is available at:

A fix for this issue for SAS/ACCESS Interface to Oracle 9.3_M1 is available at: