The difference between single and double quotes in Oracle

The cause of the problem arises:

Insert into T_cluster_showresult (outhostname,domainlist,iplist,classify) VALUES ("2014512-7", "www.renren.com\ Twww.baidu.com "," 192.168.2.1 "," 2 ")

The execution of this statement is unsuccessful, the error column is not allowed, after checking, because the problem of double quotes, changed to insert into T_cluster_showresult (outhostname,domainlist,iplist,classify) VALUES (' 2014512-7 ', ' www.renren.com\twww.baidu.com ', ' 192.168.2.1 ', ' 2012 ').

In general, we use single quotes for the majority of
However, when the character set is inconsistent, the difference between double and single quotes is still very large.
Also, when importing external data using an ODBC bridge, the table name is double-quoted

Let's look at a case:
I've had a problem like this before.
A friend. When you create a table, the table name is lowercase, and the table name is enclosed in double quotation marks
The table has been created, but when queried, the display table name does not exist, and you can see the table in User_tables
It is observed that the lowercase table name with double quotation marks is also lowercase after being stored in the database
Identifiers that are expanded in double quotation marks are stored in the data dictionary as the original case format
When Oracle calls, the default is to use uppercase formatting to query the data dictionary
Take the column name as an example:
Code: [Email protected]> CREATE TABLE test (a varchar2 (ten), a VARCHAR2 (10));

CREATE TABLE Test (a varchar2 (ten), a VARCHAR2 (10))
*
ERROR at line 1:
Ora-00957:duplicate Column Name
[Email protected]> CREATE TABLE test ("A" varchar2 (Ten), a VARCHAR2 (10));

Table created.

[Email protected]> SELECT table_name, COLUMN_NAME from user_tab_cols WHERE table_name = ' TEST ';

TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
TEST A
TEST A
[Email protected]> INSERT INTO test values (' 1 ', ' 1 ');

1 row created.

[Email protected]> commit;

Commit complete.

[Email protected]> select * from test;

A A
---------- ----------
1 1
In the case of a data dictionary in uppercase, verify that:
[Email protected]> 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
a more precise quote in double quotes is called "citation identifier".
For example, we create a table, when we write the CREATE statement, even if the table name is lowercase, all identifiers stored in the data dictionary are capitalized.
But to store lowercase identifiers in a data dictionary, you would use double quotes when you write the CREATE statement.
So for identifiers to be case sensitive, or to include spaces in identifiers, or to use reserved words for identifiers, you have to use quoted identifiers in double quotes.


Double quotes:
① indicates that strings inside it are strictly case-sensitive
② for special characters or keywords
③ not restricted by identifier rules
④ will be treated as a column.
⑤ when appearing in a To_char format string, the double quotes have a special effect, which is to wrap the illegal format characters.
Avoid ora-01821:date format not recognized error, TO_CHAR ignores double quotes when processing a format string

Code: [email protected]> select to_char(sysdate, ‘hh24"小时"mi"分"ss"秒"‘) AS RESULT from dual;

RESULT
------------------
17小时31分30秒
Single quotes:
① represents a string constant
The double quotes in the ② string are treated only as a normal character. In this case, double quotes do not need to appear in pairs
For example:

Code: [email protected]> select ‘hh24"小时""mi"分"""ss"秒"‘ AS RESULT from dual;

RESULT
-----------------------------
hh24"小时""mi"分"""ss"秒"
③ Dynamic sql:
In a pair of single quotes, you must have a pair of adjacent single quotes to denote a single quotation mark
The function of two adjacent single quotes, the first one is used to represent the escape character, and the next one represents the true single quotation mark
Single quotes should be used in single quotes as two consecutive single quotes instead of double quotes
For example:

Code: select ‘alter system kill session ‘‘‘‘|| sid||‘,‘||serial#|| ‘‘‘‘;‘ from v$session