Search code examples
databaseoracle-databasecase-sensitivequote

Need to quote table name after ODI migration

I was working Oracle Data Integrator 11g, and transferring tables between 2 oracle 11g2 database. After transfer, I need to quote table name to do transactions etc.

Here is the result of : 

SQL> select tname from tab;

TNAME
------------------------------
ogrenci2

Here, when i try "select * from ogrenci2" i get the "table or view does not exists" error. When i use "select * from "ogrenci2"" it show the data. Im not very experienced in ODI or Oracle Database but my searches on google and stack didnt do anything as a solution. Btw both databases have same character sets.

Solution

  • Apparently, ODI created tables in the new database with case-sensitive names. By default most SQL databases translate object names to upper case, so for example this statement:

    create table ogrenci2 (col1 number)

    will create a table with the uppercase name "OGRENCI2":

    SQL> select table_name from user_tables

TABLE_NAME
----------------------------
OGRENCI2

    However, supplying the table name in quotes, like so:

    create table "ogrenci2" (col1 number)

    will create the table with the literal, lowercase name 

    SQL> select table_name from user_tables

TABLE_NAME
----------------------------
ogrenci2

    Subsequently you will have to quote this table name each time you reference it, otherwise the database will convert it to upper case and then obviously fail to find it in the catalog.

    I'm not very familiar with ODI, but there may be an option that forces uppercase object names.