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

Question

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.

Record is getting converted to upper case or table columns are getting converted into lower upper case? — Jacob, Nov 12 '12 at 10:43
when i execute this query "create table t ( a number, b varchar2(10) )" — user1384279, Nov 12 '12 at 10:48
the name of the table and columns became capital characters not small characters — user1384279, Nov 12 '12 at 10:49
@user1384279 I doubt you can change that, Oracle creates tables, columns in upper case. — Jacob, Nov 12 '12 at 10:53

score 27 · Accepted Answer · 2018-11-15T15:35:36.963

27

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.

edited Nov 15 '18 at 15:35

answered Nov 12 '12 at 11:08

2

agreed with a horse with no name' I've seen and had to debug production code that used this, as the guys who wrote the app were Java developers that for some reason LOVE using camel case table and column names, as that's the way they type identifiers in Java. it's an absolute nightmare to maintain this in oracle. you almost feel like throwing the keyboard through the monitor when you see hundreds of views that all contain mixed case columns + view names and you get asked to tune some sql against them. – DazzaL Nov 12 '12 at 12:04
Agreed, great explanation - short and sweet and directly to the point! – Josh P Sep 09 '19 at 14:48

score 0 · Answer 2 · edited Nov 12 '12 at 11:41

Enclose table name in quotation marks ("). Also create your table like this

create table "t" ( a number, b varchar2(10) );

Now your table name is t in lowercase. You have to use quotation marks always, when you access your table. For example

select * from "t";

You can use same construct for other objects (columns, indexes, ...).

Anyway, SQL is case insensitive, you need a good reason to use case dependent object names.

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

2 Answers2

Linked