Knowledge points about double quotation marks and single quotation marks in Oracle and Linux

Generally, we use single quotes.
However, when character sets are inconsistent, double quotation marks and single quotation marks are quite different.
In addition, when using ODBC bridge to import external data, the table name is enclosed by double quotation marks.

Let's take a look at the case:
I have encountered such a problem before.
When a friend creates a table, the table name is in lower case and the table name is enclosed in double quotation marks.
The table has been created, but the table name does not exist during query. You can see this table in user_tables.
It is observed that the lower-case table names with double quotation marks are stored in the database and also in lower-case
The identifiers expanded with double quotation marks are stored in the data dictionary as the original case-sensitive format.
Oracle uses uppercase format to query data dictionaries by default.
Take the column name as an example:

Sys @ emrep> Create Table Test (A varchar2 (10), a varchar2 (10); Create Table Test (A varchar2 (10), a varchar2 (10 )) * error at line 1: ORA-00957: duplicate column namesys @ emrep> Create Table Test ("A" varchar2 (10), a varchar2 (10); table created. sys @ emrep> select table_name, column_name from user_tab_cols where table_name = 'test'; table_name column_name ---------------------------- perform TEST atest asys @ emrep> insert into test values ('1 ', '1'); 1 row created. sys @ emrep> commit; Commit complete. sys @ emrep> select * from test; A ---------- 1 1 1 is stored in the data dictionary in an uppercase mode. Verify that Sys @ emrep> Create Table test_xxxx (A varchar2 (1 ), "A" varchar2 (1); Create Table test_xxxx (A varchar2 (1), "a" varchar2 (1) * error at line 1: ORA-00957: duplicate column name

 

The more accurate double quotation marks are referred to as "Quote identifiers"
For example, when we create a table and write the create statement, even if the table name is in lower case, all the identifiers stored in the data dictionary are in upper case.
However, to store lowercase identifiers in the data dictionary, you must use double quotation marks when writing the create statement.
Therefore, to make the identifier case sensitive, or contain spaces in the identifier, or use reserved words as the identifier, you must use the quote identifier enclosed in double quotation marks.

 

Double quotation marks:
① Indicates that the internal strings are strictly case sensitive.
② Used for special characters or keywords
③ Not subject to identifier rules
④ It will be processed as a column
⑤ When it appears in the to_char Format String, double quotation marks have a special function, which is to wrap Invalid format characters.
Avoid ORA-01821: Date Format not recognized errors, to_char ignores double quotation marks when processing formatted strings

Sys @ emrep> select to_char (sysdate, 'hh24 "Hour" mi "Minute" SS "second" ') as result from dual; result--17 hours 31 minutes 30 seconds

 

Single quotes:
① Represents a String constant
② Double quotation marks in a string are processed only as a common character. Double quotation marks do not need to appear in pairs.
For example:

Sys @ emrep> select 'hh24 "Hour" "mi" Minute "" SS "second" 'As result from dual; result ----------------------------- hh24 "Hour" "mi" Minute "" SS "second"

③ Dynamic SQL:
In a statement containing a pair of single quotes, a pair of adjacent single quotes must be included to indicate a single quotation mark.
Two adjacent single quotes. The first one is used to indicate escape characters, and the last one is used to indicate true single quotes.
The single quotes must be two consecutive single quotes instead of double quotes.
For example:

select 'alter system kill session ''''|| sid||','||serial#|| '''';' from v$session

 

During Linux Shell programming
You can use double quotation marks or single quotation marks to control variables. single quotation marks and double quotation marks must be paired.
If it is a single quotation mark, the special characters in it are only common characters
If it is double quotation marks, special characters such as $ can retain the original features. For example, if Var = "Lang is $ Lang", Echo $ var. Lang is en_us.

[root@localhost ~]# name=think[root@localhost ~]# echo $namethink[root@localhost ~]# ename="$name is my English name"[root@localhost ~]# echo $enamethink is my English name[root@localhost ~]# ename='$name is my English name'[root@localhost ~]# echo $ename$name is my English name

In Oracle, only single quotes indicate strings, but in Linux, single quotes and double quotes indicate strings.