How to make oracle table names case insensitive?

Question

I have a query like this: $this->db->select("SELECT * FROM foo"); Now if the table name is Foo, and I execute this script on MySQL istance all working good (table name on MySQL is even with lowercase letters, so: foo. Buy if I execute this on Oracle that have the table name as Foo. I'll get:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'test_db.foo' doesn't exist

'Cause on Oracle istance is called Foo not foo. Some idea to fix this?

http://stackoverflow.com/questions/7425153/reason-why-oracle-is-case-sensitive says that table names are case-insensitive unless you put them in quotes. — Barmar, Apr 11 '16 at 21:07
This is strange, I don't surround the table name in the quote. — Dillinger, Apr 11 '16 at 21:09
Not so strange, as all unquoted names are changed to same case (all uppercase by default) on input . So if your table name is not _FOO_, you will experience this error. — rpy, Apr 11 '16 at 21:12
This also happean if I pass FOO in my query. And I create the table without any quotes. — Dillinger, Apr 11 '16 at 21:15
I assume the problem is caused by missing privileges rather than capitalization of tables names. — Wernfried Domscheit, Apr 12 '16 at 05:55

MT0 · Accepted Answer · 2016-04-11T22:31:09.087

[TL;DR] The simplest thing to do is to never use double quotes around object names and just let oracle manage the case-sensitivity in its default manner.

Oracle databases are, by default, case sensitive; however, they will also, by default, convert everything to upper-case so that the case sensitivity is abstracted from you, the user.

CREATE TABLE tEsT ( column_name NUMBER );

Then:

SELECT COUNT(*) FROM test;
SELECT COUNT(*) FROM Test;
SELECT COUNT(*) FROM TEST;
SELECT COUNT(*) FROM tEsT;

Will all give the same output and:

SELECT * FROM USER_TABLES;

Outputs:

TABLE_NAME
----------
TEST

(Note the table name is in upper case).

If you use double quotes then oracle will respect your use of case in the table name:

CREATE TABLE "tEsT" ( column_name NUMBER );

and:

SELECT * FROM USER_TABLES;

Outputs:

TABLE_NAME
----------
TEST 
tEsT

(Note: there are now two tables named TEST and tEsT and oracle has respected the case sensitivity of the second one - the one created with quotes).

(Also note: SELECT * FROM tEsT will select from the first table, which has been converted to upper-case, but SELECT * FROM "tEsT" is required to select from the second even though the queries are identical apart form the quotes).

Nope, still the same error. I have created the table with no quote around then name. Anyway when I execute show create table table name I noticed a backtick around the table name — Dillinger, Apr 12 '16 at 07:36

How to make oracle table names case insensitive?

1 Answers1

Linked

Related