Re: Extra blank characters querying SQL server from Oracle

I got the answer from Oracle support. We set HS_FDS_MAP_NCHAR = FALSE in the gateway init.ora file and that fixed it for us.

Thanks to everyone for their time.

Ram.

On Thu, Mar 9, 2023 at 10:20 AM Ram Raman <veeeraman_at_gmail.com> wrote:

> Thanks Frank. Here:
>
> emp_id EMPNAME
> city
> ----------
> ----------------------------------------------------------------------------------------------------
> ----------
> 100 Typ=1 Len=20 CharacterSet=AL16UTF16:
> 0,77,0,0,0,117,0,0,0,116,0,0,0,104,0,0,0,117,0,0 New York
> 200 Typ=1 Len=16 CharacterSet=AL16UTF16:
> 0,71,0,0,0,97,0,0,0,114,0,0,0,121,0,0 Seattle
> 201 Typ=1 Len=16 CharacterSet=AL16UTF16:
> 0,68,0,0,0,97,0,0,0,118,0,0,0,101,0,0 Oak Brook
>
> The real data:
>
> emp_id EMPNAME city
> ---------- ---------------------------- ----------
> 100 M u t h u New York
> 200 G a r y Seattle
> 201 D a v e Oak Brook
>
>
> On Thu, Mar 9, 2023 at 5:34 AM Frank Gordon <frankagordon_at_gmail.com>
> wrote:
>
>> Hello,
>>
>> You could also try
>> SELECT DUMP(emp_name, 1010) from sql_server_table;
>>
>> Lets see what Oracle "thinks" this column type is?
>> Oracle should think its NVARCHAR not VARCHAR2.
>>
>>
>>
>> Regards,
>> Frank
>>
>>
>>
>> On Wed, Mar 8, 2023 at 7:28 PM Ram Raman <veeeraman_at_gmail.com> wrote:
>>
>>> Hi,
>>>
>>> We have configured Oracle Gateway to query SQL server from our Oracle 19
>>> database. However the results add a blank character to every character
>>> returned for columns in sql server that are defined as unicode columns, ie,
>>> nvarchar columns.
>>>
>>> Here is an example of a query result in SQL plus with the source table
>>> in sql server:
>>>
>>> emp_id emp_name city
>>> ---------- ------------------------- ----------
>>> 100 M u t h u New York
>>> 200 G a r y Seattle
>>> 201 D a v e Oak Brook
>>>
>>>
>>> The table definition on the sql server side is:
>>>
>>> CREATE TABLE [dbo].[employee](
>>> [emp_id] [smallint] NULL,
>>> [emp_name] [*nvarchar*](50) NULL,
>>> [city] [*char*](10) NULL
>>> ) ON [PRIMARY]
>>>
>>> Is there a way in Oracle to strip off the blanks automatically, in this
>>> example for the emp_name column?
>>>
>>> Thanks,
>>> Ram
>>> --
>>>
>>>
>>>
>>
>> --
>> +353-86-0695383
>>
>
>
> --
>
>
>

--

--
http://www.freelists.org/webpage/oracle-l