You can receive this error for the following reasons:
To check whether the table or view exists, use the following SQL:
select *
from all_objects
where object_type in ('TABLE','VIEW')
and object_name = 'object';
where object is the table or view you are trying to access. If you get "No data found" when running this query, the table or view does not exist.
This article provides a workaround if your SQL Server database has a case sensitive collation.
By default, Oracle converts object names in SQL statements to uppercase. The following example query illustrate the effect of this:
select mycolumn from mytable@mylink
select "MYCOLUMN" from "MYTABLE"
This first statement has been executed by a user in an Oracle application such as SQL*Plus. The second statement is what Oracle actually sends to SQL Server. If the SQL Server database has a case sensitive collation, mycolumn is not the same as MYCOLUMN and mytable is not the same as MYTABLE. This query will therefore fail with the error ORA-00942: table or view does not exist unless the target table and column actually have uppercase names.
To check whether your database has a case sensitive collation, use isql, the sample ODBC application included with the unixODBC distribution that the Easysoft ODBC-SQL Server Driver contains:
cd to the installation_dir/easysoft/unixODBC/bin directory, where installation_dir is the Easysoft installation directory, by default /usr/local. For example:
$ cd /usr/local/easysoft/unixODBC/bin
isql to connect to your Easysoft ODBC-SQL Server Driver data source. For example:
$ ./isql.sh -v MY_DATA_SOURCE
Replace MY_DATA_SOURCE with the name of your data source.
SELECT DATABASEPROPERTYEX('MY_DATABASE', 'Collation') SQLCollation;
Replace MY_DATABASE with the name of your database.
If the collation name returned by this query contains CS (for example, Latin1_General_CS_AS), your database has a case sensitive collation.
SQL Server collation names have three parts:
Latin1_General.CI or CS.AI for accent insensitive collations or AS for accent sensitive collations. For databases with an accent sensitive collation, á is not the same as a.To prevent Oracle from capitalising object names, enclose object names in your SQL statements with quotation marks ("). For example:
select "mycolumn" from "mytable"@mylink
If you do this, Oracle will preserve the specified capitalisation for object names.
© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle® is a registered trademark of Oracle® Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.
