• Print |
• E-mail  |
• Bookmark

When using the option DBSASTYPE=(VAR="CHAR17")), values are padded with
'00'x (binary 0) rather than blanks when using Oracle 9 in SAS 8.2.
Also, numbers of varying lengths are not only being padded with a binary
zero when they are converted.  It is possible that additional numbers
will be added to the end of the field.   For example, a field in Oracle
contains the numerical value of 10000076.  When the field is converted
in SAS after using DBSASTYPE, it appears as 100000765, plus the Binary
zeros.  The problem of appending the extra digit and binary zeros only
appears to happen on existing tables.

This problem was discovered after upgrading from Oracle 8 to Oracle
9.2.x.x when using the DATA Step SET statement to read an Oracle table:

   SET ora.table(DBSASTYPE=(acnum="CHAR17")) ;

This statement is a workaround for Oracle numeric precision issues when
reading Oracle fields of datatype NUMBER into SAS.

With Oracle 8, the numbers were padded with a blank.  But with Oracle 9,
they are padded with a binary zero ('00'x).  As SAS can't handle
some 16 and 17 digit numbers exactly, then the conversion should be
happening in Oracle.  This is an Oracle issue and development has
addressed it with Oracle.

This problem does not exist with SAS 9.  To resolve or workaround
this problem, use one of the following options:

  * Change for format from CHAR17 to CHAR16, if possible.

  * Use Oracle 8 instead of Oracle 9

  * Use SAS 9 with Oracle 9

  * Get a patch from Oracle for Oracle 9

  * Apply the HOTFIX below for SAS 8.2 which circumvents the changes
    in Oracle9.


 A Technical Support hot fix for Release 8.2 (TS2M0) for this
issue is available at:

http://www.sas.com/techsup/download/hotfix/82_sbcs_prod_list.html#012675

Operating System and Release Information

A fix for Release 8.2 (TS2M0) for this issue is available at: