Case Problems with Oracle objects

Some people may not be accustomed to table names, fields are all capitalized, and prefer lowercase or case-mixed form, if so, you write the script in Oracle should pay attention to, not handled well, this will bring you "endless trouble."

Create a new test table in the database (the database version is Oracle 10.2.0.1.0), and the table name is lowercase test. The script looks like this:

CREATE TABLE Test

（

ID Number (10),

Name VARCHAR2 (20),

Sex VARCHAR (2)

）

--Cannot find the corresponding data

SELECT * FROM user_tables WHERE table_name = ' Test '

--use uppercase to find the corresponding data

SELECT * FROM user_tables WHERE table_name = ' TEST '

--Cannot find the corresponding data

SELECT * FROM all_tab_columns WHERE table_name = ' test ';

SELECT * FROM user_tab_columns WHERE table_name = ' TEST ';

Next we use double quotes to create a new table (the role of ORACLE "" is to force case sensitivity, and the keyword to do field when the "") script looks like this:

CREATE TABLE "Test1"

（

"id" number (10),

"Name" VARCHAR2 (20),

"SEX" VARCHAR (2)

）

SELECT * FROM user_tables WHERE table_name = ' test1 ';

SELECT * FROM dba_tables WHERE table_name = ' test1 ';

SELECT * FROM user_tab_columns WHERE table_name = ' test1 ';

SELECT * from "Test1";

--ora-00942: Table or attempt does not exist

SELECT * from Test1;

--ora-00904: ' NAME ': invalid identifier

SELECT ID, Name, SEX from "test1";

--ora-00904: "ID": Invalid identifier

Select ID, "Name", SEX from "test1";

SELECT "id", "Name", SEX from "test1";

--ora-00942: Table or attempt does not exist

SELECT "id", "Name", SEX from Test1;

The above example may make it strange to think that this phenomenon does not exist in SQL Server. This is because when Oracle is building a table or field, if there are no double quotes, Oracle converts the table name, field name into uppercase letters, and then writes to the data dictionary. When accessing a data dictionary, there is no double quote Oracle converts it to uppercase and then looks in the data field. If you add double quotes, you can be case-sensitive. Not only keywords are case-insensitive, function names, procedure names, table names, variable names in pl/sql blocks, user names, passwords, and so on are case-insensitive.

As shown above, you must add "" on the field, otherwise there is an error, where the fire. And sometimes people are very careless and forgetful. So in Oracle, it is recommended to use all caps, which may start to make you uncomfortable, but just get used to it. It's better than you use "" to bring a lot of hidden trouble behind the development.

In Oracle, where custom scripts, fields, and so on are capitalized, some say they can be more efficient, so there is less overhead of casting uppercase (without double quotes), and some people say that these costs are negligible and do not have any performance problems. (Of course the script is all in uppercase, so it's OK to avoid the same script being parsed multiple times because of the case problem).

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/