How I can create a table with oracle but with small characters?

oracle quoted-identifier
user1384279 picture user1384279 · Nov 12, 2012 · Viewed 9.4k times · Source

How I can create a table with oracle but with small characters, when I create a table with small characters it converts auto to capital characters.

Answer

a_horse_with_no_name picture a_horse_with_no_name · Nov 12, 2012

Folding (non-quoted) table names to upper case is required by the ANSI SQL standard.

You can create tables (and columns) with lowercase names using a quoted identifier (again this follows the SQL standard):

CREATE TABLE "foo" 
(
   "id"          integer,
   "SomeColumn"  varchar(100)
);

I would however strongly advise you, to not do that.

Once you have your tables created that way, you have to always use double quotes because any non-quoted name will (following the rules for SQL identifiers) again be folded to upper-case and thus won't match the name as it is stored in the system catalogs.

Therefor the following statement will not work:

SELECT id, somecolumn FROM foo;

You have to use a quoted identifier:

SELECT "id", "SomeColumn" FROM "foo";

For more details on (quoted) identifiers, please read the chapter Database Object Naming Rules in the manual.

Related questions

Oracle ORA-00942: table or view does not exist when it exists

I'm trying to get used to Oracle, installed express one and created by 3rd part program some tables. And when I log in into sqlplus I can not simply use SELECT * FROM table.... SQL> SELECT * FROM tab; TNAME TABTYPE …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More