none
Problems with Oracle character setting

    Question

  • Hi there,

    I am using an ADO Oracle Connector to store Oracle data to an SQL Server.
    When I map the input data to the OLEDB destination I get the Error msg that
    unicode (DT_WSTR) cannot be mapped to 1292 character code (DT_STR).

    To solve this I put a Data Conversion Transformation between Source and Destination.
    That would mean a lot of work if I couldn't map the data directly from Source to Destination
    for all the tables of my project.

    I checked the NLS-Settings from the Oracle db:
    select * from sys.v_$nls_parameters
    NLS_CHARACTERSET -> WE8ISO8859P1

    It's really a mystery to me why the DataReader converts Latin-1 to unicode which has to be
    converted to Latin-1 again.
    Could someone please help me out?

    Fridtjof
    Friday, December 09, 2005 8:31 PM

Answers

  • The problem here is with managed code.  The CLR doesn't have a ANSI type string so all strings are converted to Unicode.  So in actuality, it is not the datareader that does the conversion but Oracle's ADO.NET connector.  Obviously, knowing this doesn't really help you out all that much though.  Is there any way you can make the SQL Server table be unicode instead of ANSI.  If not then data conversion is the only solution other than to get an OLEDB driver for Oracle, since OLEDB does support ANSI directly.

    HTH,
    Matt
    Friday, December 09, 2005 10:46 PM
    Moderator

All replies

  • The problem here is with managed code.  The CLR doesn't have a ANSI type string so all strings are converted to Unicode.  So in actuality, it is not the datareader that does the conversion but Oracle's ADO.NET connector.  Obviously, knowing this doesn't really help you out all that much though.  Is there any way you can make the SQL Server table be unicode instead of ANSI.  If not then data conversion is the only solution other than to get an OLEDB driver for Oracle, since OLEDB does support ANSI directly.

    HTH,
    Matt
    Friday, December 09, 2005 10:46 PM
    Moderator
  • Matt,

    you're right. I've tried to download Oracle data via OLEDB which nags that it cannot read Oracle's character setting and assumes it to be 1292 (or was it 1252?). But it downloads the data correctly.

    Unfortunately I cannot set the password property in a Package configuration. See this post:
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=161564&SiteID=1

    This way I'm not getting much further. One have to ponder between the different restrictions :(
    Thanks anyway
    Fridtjof
    Saturday, December 10, 2005 9:37 AM