Oracle Transparent Gateway for SQL Server - column name problem

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_at_remote_server -- this works select column1, column2 from owner.table_at_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_at_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_at_bhp.com.au.no_junk_email Received on Thu Nov 25 1999 - 21:56:11 CST