SQL Developer forces capitalization when I create a table

Question

When I create a table with SQL Developer my columns and the name of the table must be capitalized. I want to use lower case but I don't know how to do it.

Answer 1

This is not something that SQL Developer does; it is done by the Oracle database and is the default behaviour.

From Oracle's Database Object Names and Qualifiers documentation:

Database Object Naming Rules

Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.

• A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.
• A nonquoted identifier is not surrounded by any punctuation.

You can use either quoted or nonquoted identifiers to name any database object.

...

9. Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive.

Which means that if you create any identifier then Oracle will convert it upper-case to store it in the data dictionary and this is the way Oracle is designed to behave.

So:

CREATE TABLE table_name (
  id INT PRIMARY KEY
);

Then if you look in the data dictionary:

SELECT table_name, column_name
FROM   user_tab_columns;

Then the output is:

TABLE_NAME | COLUMN_NAME
:--------- | :----------
TABLE_NAME | ID         

And you could do:

SELECT id FROM table_name;
SELECT ID FROM TABLE_NAME;
SELECT Id FROM TaBlE_nAmE;
SELECT "ID" FROM "TABLE_NAME";

and they would all select from that table as Oracle will implicitly convert the unquoted identifiers to upper-case.

If you quote the identifiers then Oracle will keep those identifiers in the same case as you type them but you will need to ALWAYS quote them whenever your refer to that identifier.

If you do:

CREATE TABLE "table_name" (
  "id" INT PRIMARY KEY
);

Then the data dictionary would now contain two tables, one upper case and one lower case:

SELECT table_name, column_name
FROM   user_tab_columns;

TABLE_NAME | COLUMN_NAME
:--------- | :----------
TABLE_NAME | ID         
table_name | id         

and to get data from that second table you would have to use quoted identifiers with the correct case:

SELECT "id" FROM "table_name";

db<>fiddle here

You either quote the identifiers (everywhere they are used) and can have lower-case identifiers or Oracle will implicitly convert the unquoted identifiers to upper-case.

Make things simple for yourself and, if you are manually writing SQL queries, just use unquoted identifiers and accept that behind the scenes Oracle will implicitly convert the case and that that is not a bad thing.