Easysoft Data Access

Why do I get error "ORA-00942: table or view does not exist" when using DG4ODBC to connect to SQL Server?

Article:
01024
Last Reviewed:
11th August 2011
Revision:
1

You can receive this error for the following reasons:

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:

  1. On the DG4ODBC machine, 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
  2. Use 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.

  3. Run the following query:
    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:

    1. SortRules, a string identifying the alphabet or language whose sorting rules are applied when dictionary sorting is specified. For example, Latin1_General.
    2. CaseSensitivity, CI or CS.
    3. AccentSensitivity, 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.

See Also
Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.