RE: Extra blank characters querying SQL server from Oracle

From: Clay Jackson <"Clay>
Date: Wed, 8 Mar 2023 22:02:14 +0000
Message-ID: <CO1PR19MB4984A745C447D33F1E6FF8929BB49_at_CO1PR19MB4984.namprd19.prod.outlook.com>

Of the top of my head, this looks like either a bug or a character set issue. Is one database or the other using a double-byte character set.

Clay Jackson

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Ram Raman Sent: Wednesday, March 8, 2023 1:43 PM
To: mark.powell2_at_dxc.com
Cc: ORACLE-L <oracle-l_at_freelists.org> Subject: Re: Extra blank characters querying SQL server from Oracle

CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.

Thanks Mark. Gateway is the same ver as Oracle (19) per the manual recommendation. Sql server is Std Edition 2019.

Left trim, right trim and trim functions are not helping in this case. REPLACE, REGEXP_REPLACE also do not work.

Ram.

On Wed, Mar 8, 2023 at 1:54 PM Powell, Mark <mark.powell2_at_dxc.com<mailto:mark.powell2_at_dxc.com>> wrote: What version of the Oracle Gateway is in use? Which driver? What version is the target SQL Server?

You could as a workaround and if no one else provides a better solution use an ltrim (or trim) function on the select.

Mark Powell
Database Administration
(313) 592-5148

---

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Ram Raman <veeeraman_at_gmail.com<mailto:veeeraman_at_gmail.com>> Sent: Wednesday, March 8, 2023 2:27 PM
To: ORACLE-L <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: Extra blank characters querying SQL server from Oracle

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

--

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

Received on Wed Mar 08 2023 - 23:02:14 CET