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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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
--
