How to: Make Oracle Tables and fields appear case-sensitive

I believe most of them are used People with SQL Server will definitely not get usedto it when they start using Oracle, because the table names and field names that Oracle builds are automatically converted to uppercase. As shown:< XmlNamespace prefix = "o" ns = "Urn:schemas-microsoft-com:office:office"/>

??

???

to tell the truth, the first time I contacted Oracle 's time, also looked very uncomfortable, but that will also only helpless, because cannot solve. So the mentality has been thinking, if it can be shown as a case-sensitive how good ah, so I realized the heart of the willing, as shown:

??

the long-awaited effect finally realized, particularly happy, so I want to tell everyone to achieve the principle, in order to look forward to more like me the pursuit of beautiful people get ? a psychological consolation.

??

Implementation method:

no longer used Pl-sql the table directly, instead of using PD(Power desginer) to build the table. Because I found that no matter whether we are case-sensitive or not , Pl-sql will automatically help us convert the capitalization. the SQL generated automatically by PD is as follows:

CREATE TABLE "Tdepartment"?? (

??? FID?????????????????? NVARCHAR2 (??????????????????) Not NULL,

???" Ftext "?????????????? NVARCHAR2 (???????????????????) Not NULL,

???" Fparentid "?????????? NVARCHAR2 (???????????????????) Not NULL,

???" Flevel "????????????? Number,

???" Fautocode "?????????? NVARCHAR2 (100),

???" FCode "?????????????? NVARCHAR2 (50),

???" Fleaf "?????????????? Number????????????????????????? Default 1 is not NULL,

???" Fremark "???????????? NVARCHAR2 (500),

???" Fdataserverip "?????? NVARCHAR2 (100),

???" Fdataserverport "???? NVARCHAR2 (10),

???" Fmediaserverip "????? NVARCHAR2 (100),

???" Fmediaserverport "??? NVARCHAR2 (10),

???" Fdataserver "???????? NVARCHAR2 (200),

???" Fmediaserver "??????? NVARCHAR2 (200),

??? Constraint Pk_tdepartment primary KEY (FID)

);

??

(The script for the field description is omitted here.)

??

Principle Analysis:

The table name and field names are enclosed in double quotation marks in the PL auto-generated script that creates the table. double quotes in Oracle is case- sensitive,Oracle is case-insensitive , and all caps are default when double quotes are not used . Many people may mistakenly think that Oracle is case-insensitive , because when we write query statements, the case does not matter, the result is the same, it is only a superficial phenomenon, as long as you do not use double quotes to indicate case-sensitive,Oracle The script you write will be converted to uppercase by default. As can be seen from the following:

??

??

because Oracle default storage is uppercase, so the Pl-sql display is also capitalized. ?

??

??

??

precisely because of the case sensitivity, so In Oracle, you can have both case and table fields with the same name.

??

come here everybody I think everybody should understand how to make Oracle is case-sensitive and uses double quotes.

??

??

thinking: since Oracle can be case-sensitive, so why do people prefer to look at uncomfortable capitalization when they use it? In this experiment we will give an answer, as shown in the following example:

??

??

As mentioned above, if we do not add double quotes, then Oracle will convert the table name and field name to uppercase by default.

simplest query statement:SELECT * FROM Tcamera???

will error, prompt: ORA-00942:table or view does not exist??? table or attempt does not exist

at this time, we look at the hints should be easy to understand, the reason is that in User-tables in Oracle? There are no queries we want to make. tcamera[ automatically converts the "uppercase " table.

then the correct statement should be:select * from "Tcamera"??? double quotes required

??? this time feel very uncomfortable, that write query more inconvenient Ah, and not only is the table name need to use double quotation marks, if query some specific fields also need double quotation marks, then this time to developers will bring a lot of inconvenience. That's why everyone would rather choose all uppercase. Do you understand?

??

???

Power Designer the corresponding settings:

Use PD people will have a problem, since the default PD is generated with double quotation marks case-sensitive, then how to remove the double quotation marks, you can not change one? Oh, since you think of, then Sybase Team Naturally also think of, so through the following setup steps, you can remove the PD automatically generated by the double quotation marks of the script.

Find Menu Database->edit current DBMS?? will open The DBMS Property Settings window, locate the ScriptàSqlàcasesensitivityusingquote, select No, That is, case-insensitive. As shown in the following:

??

??

When you click OK, you are prompted to save your settings, click " is a " .

??

at this point, let's compare the generated SQL:?

??

??

before setting up, PD is case-sensitive by default, with double quotation marks for table names and fields , as shown in:

??

??

??

when set, it is case-insensitive, with no double quotation marks, so the resulting table and its fields will default to the Oracle is converted to uppercase. As shown in the following:

??

to Here, PD and Oracle correspond, and what kind of form do you want to set up yourself?

??

Finally, let's compare SQL Server? and the The difference between Oracle's query-specific content allows us to be more aware of the misunderstandings we may have in our usual way.

??

??

This indicates that the SQL Server is also case-insensitive in table field content. But Oracle is different, in the same situation,Oracle certainly cannot query the data because Oracle is case-sensitive. Do not believe you can go to try.

turn from:? flying Heart macro ?? 2011-12-21

How to: Make Oracle Tables and fields appear case-sensitive