Make Oracle case sensitive Table Name field name Object Name

Source: Internet
Author: User
I. solution 1. Add double quotation marks to the table name, field name, and object name to make oracle case sensitive. 2. This causes another problem: in database operations, double quotation marks must be added to the corresponding table name, field name, and object name in the SQL statement. Solution: Use escape. For example, Stringsqlselect * fromuser

I. solution 1. Add double quotation marks to the table name, field name, and object name to make oracle case sensitive. 2. This causes another problem: in database operations, double quotation marks must be added to the corresponding table name, field name, and object name in the SQL statement. Solution: Use \ escape. For example, String SQL = select * from user

I. Solutions

1. Adding double quotation marks to the table name, field name, and object name makes oracle case sensitive.

2. This causes another problem: in database operations, double quotation marks must be added to the corresponding table name, field name, and object name in the SQL statement.

Solution: Use "\" escape. For example:

String SQL = "select * from userinfo where\ "LoginId \"=? And loginpwd =? ";


Ii. Details

Generally, Oracle is not case-sensitive during ORACLE development or management, in general, ORALCE converts all lowercase letters to uppercase letters for processing. Therefore, it is case insensitive. However, ORACLE has a complete set of image name processing methods. This article analyzes and explores the processing mechanism of ORACLE image name sensitivity from examples.

But many people have learned about it at work,ORACLE can use quotation marks when creating an image. If no quotation marks are used, special characters are not allowed and names starting with letters can only be used. If quotation marks are added, you can use any character in the image name, including the start of a number, an underscore, a comma, and so on. When creating a script for SQL Server, it is usually enclosed in quotation marks. Therefore, it is often said that the script cannot be accessed after it is run in ORACLE.

Note:

1. This document uses ORACLE9.2 as the testing and analysis version.

2. The ORACLE object names mentioned in this article include the table name, view name, field name, and function name in ORACLE.

The test results are as follows:

Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0 Connected as ***** SQL> create table mytable1 2 (3 C1 VARCHAR2 (6) 4 ); table created SQL> select * from "MYTABLE1"; C1 ------ SQL> select * from MYtable1; C1 ------ SQL> select * from "mytable1"; select * from "mytable1" ORA-00942: SQL> drop table mytable1; Table dropped SQL> create table "mytable1" 2 (3 C1 VARCHAR2 (6) 4 ); table created SQL> select * from "mytable1"; C1 ------ SQL> select * from mytable1; select * from mytable1 ORA-00942: Table or view does not exist SQL> select * from MYTABLE1; select * from MYTABLE1 ORA-00942: The table or view does not exist

Test result summary:

√ Indicates allowed access, and × indicates not allowed access.

Read and create mytable1 MYTABLE1 "mytable1" "MYTABLE1" mytable1 √ × √ "MYTABLE1" ×√ × "mytable1" √ × √

Summary:

Read create lowercase letters upper case letters quotation marks lower case letters plus quotation marks upper case letters √ add quotation marks lower case letters ×√ × add quotation marks upper case letters √ ×√

Analysis conclusion:

If no quotation marks are added to the image creation process in ORACLE, the lowercase letters of the image name are converted to uppercase letters for storage when the image is saved to the data dictionary. For example, mytable is converted to MYTABLE; if quotation marks are added during creation, the actual characters in the quotation marks are stored.

If no quotation marks are added for access, lowercase letters are converted into uppercase letters for access. For example, mytable is converted to MYTABLE. If quotation marks are added, actual characters in quotation marks are used for access.

When reading a data dictionary, ORACLE considers the image name to be case sensitive if it contains lowercase letters or characters starting with a letter or a Chinese character, and requires quotation marks during access.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.