Xref: alice comp.databases.oracle.misc:47836 comp.databases.oracle.server:75598 comp.databases.oracle.tools:36263
Path: alice!news-feed.fnsi.net!enews.sgi.com!harbinger.cc.monash.edu.au!news.bhp.com.au!news.itmel.bhp.com.au!atbhp.corpmel.bhp.com.au!news
From: "Neville Sweet" <sweet.neville.nj@bhp.com.au.no_junk_email>
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools
Subject: Oracle Transparent Gateway for SQL Server - column name problem
Date: Fri, 26 Nov 1999 14:56:11 +1100
Organization: BHP
Lines: 36
Message-ID: <81l0je$c6g34@atbhp.corpmel.bhp.com.au>
X-Newsreader: Microsoft Outlook Express 4.72.3612.1700
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3612.1700

Hi,

We have a problem with Oracle Transparent Gateway 4.1 and the way that
column names are specified in DML statements. Using 'select *' works fine,
but if we instead specify a column list Oracle returns ORA-00904: invalid
column name. For example:
select * from owner.table@remote_server     -- this works
select column1, column2 from owner.table@remote_server    -- this fails

The problem appears to be due to case sensitivity, despite the fact that the
SQL Server 7.0 database is configured for case-insensitive column names and
Oracle column names are always case insensitive.
Even when specifying lower case column names in the select statement, the
ORA-00904 results.
Apparently Oracle folds the column name to upper case, because the select
succeeds when the column name is defined in SQL Server as upper case. For
whatever reason, table names are not similarly afflicted.

The workaround is to surround column names with double quotes, eg.
select "column1", "column2" from owner.table@remote_server where "column 2"
= xxx

As luck would have it, our DBA(s) used both upper and lower case when
creating the SQL Server database.
Since we can't guarantee that development and production database column
names are identical (wrt case), we would very likely need different versions
of development and production programs (which is not something I'm terribly
fond of).

Has anyone experienced this problem? Any suggestions ?

Thanks,
Neville Sweet.
Email: sweet.neville.nj@bhp.com.au.no_junk_email