Oracle table names and case sensitivity

[tonifakhry]

Hello,

I am trying to deploy my Oracle database with Teiid , if the names of tables and fields are in UpperCase, there is no problem and the deploy is successfull,

but when the names are in lower case, I am getting this error :

An error has occurred. Please try again! TEIID11010 java.sql.SQLException: ORA-00942: table or view does not exist

By default, Oracle identifiers (table names, column names, etc.) are case-insensitive. To make them case-sensitive we must use quotes around them (eg: SELECT * FROM "My_Table" WHERE "my_field" = 1).

And so if a table is created in this manner "My_Table" (upper and lower case), I must use quotes around the table name while querying otherwise I will get "ORA-00942: table or view does not exist" error :

SELECT * FROM My_Table will return  : ORA-00942: table or view does not exist.

Your help would be appreciated

Regards

[shawkins]

How are you defining the metadata - via an import, Designer, DDL?

[tonifakhry]

We are defining the metadata via embedded Teiid programming ( DDL).

[shawkins]

We don't automatically quote based upon the table/column names, rather if you have a using table/column names that need quoting, then use the name in source option:

create foreign table tbl (...) options (nameinsource '"quoted name"')

See also DDL Metadata - Teiid 8.8 (draft) - Project Documentation Editor

Using a Designer or dynamic vdb import should automatically handle the necessary quoting.