Oracle Field case Problem __oracle

When you create a table today, the fields are enclosed in quotes, and when you select the field you tell the field to find it, as shown in the following example:

Sql> CREATE TABLE Test ("id" varchar2);

Table created.

sql> desc test;
 Name                                      Null?    Type
 -----------------------------------------------------------------------------
 ID                                                 VARCHAR2 (10)

sql> INSERT INTO test values (' 1 ');

1 row created.

Sql> commit;

Commit complete.

Sql> select ID from test;
Select ID from Test
       *
ERROR in line 1:
ORA-00904: ' ID ': invalid identifier


sql> select ' id ' from TE St;

ID
----------
1

sql> select * from test;

ID
----------
1

sql> 

After repeated experiments, if you build a table without quotes in the field, no matter how the query will not appear above the problem, after proving that the case problem. Continue the experiment:

Sql> CREATE TABLE Test (ID varchar2 ());
Table created.

Sql> desc Test
 Name                                      Null?    Type
 -----------------------------------------------------------------------------
 ID                                                 VARCHAR2 (10)

sql> INSERT INTO test values (' 2 ');   
1 row created.

Sql> commit;
Commit complete.

Sql> select ID from test;
ID
----------
2

sql> select ID from test;
ID
----------
2

sql> select * from test;
ID
----------
2

sql> select "id" from test;
Select ' id ' from test
       *
ERROR at line 1:
ORA-00904: ' ID ': invalid identifier

sql> select ' id ' from Test;
ID
----------
2

sql> 

specifically as follows:

The id=id= "id" 1th ID is converted to an uppercase ID by default, and the second ID is converted to an uppercase ID by default, and the third is not converted and, because of the quotation mark, it is uppercase, so three equals.

≠ (above line is not equal to line below)

The "id" = " ID" 1th "id" is quoted as a quotation mark and is lowercase, so reference to the field can only be referenced with an "id" and any other form will be an error.

Conclusion: Oracle fields, when not quoted, are all converted to uppercase by default.

When quoted, it is not automatically converted, what is written, what is, when the field is referenced, if the case does not match the error ORA-00904: "ID": Invalid identifier

Because of the above characteristics, 2 field names may appear as follows:

sql> drop table test;
Table dropped.

Sql> CREATE TABLE Test ("id" VARCHAR2 (), id varchar2 ());
Table created.

sql> desc test;
 Name                                      Null?    Type
 -----------------------------------------------------------------------------
 ID                                                 VARCHAR2 (10)
 ID                                                 VARCHAR2 (sql>)

insert INTO test values (' 3 ', ' 3 ');
1 row created.

Sql> select * from test;
ID         ID
--------------------
3          3