Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
443 views
in Technique[技术] by (71.8m points)

sql - Difference between N'String' vs U'String' literals in Oracle

What is the meaning and difference between these queries?

SELECT U'String' FROM dual;

and

SELECT N'String' FROM dual;
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

In this answer i will try to provide informations from official resources

(1) The N'' text Literal

N'' is used to convert a string to NCHAR or NVARCHAR2 datatype

According to this Oracle documentation Oracle - Literals

The syntax of text literals is as follows:

enter image description here

where N or n specifies the literal using the national character set (NCHAR or NVARCHAR2 data).

Also in this second article Oracle - Datatypes

The N'String' is used to convert a string to NCHAR datatype

From the article listed above:

The following example compares the translated_description column of the pm.product_descriptions table with a national character set string:

SELECT translated_description FROM product_descriptions
  WHERE translated_name = N'LCD Monitor 11/PM';

(2) The U'' Literal

U'' is used to handle the SQL NCHAR String Literals in Oracle Call Interface (OCI)

Based on this Oracle documentation Programming with Unicode

The Oracle Call Interface (OCI) is the lowest level API that the rest of the client-side database access products use. It provides a flexible way for C/C++ programs to access Unicode data stored in SQL CHAR and NCHAR datatypes. Using OCI, you can programmatically specify the character set (UTF-8, UTF-16, and others) for the data to be inserted or retrieved. It accesses the database through Oracle Net.

OCI is the lowest-level API for accessing a database, so it offers the best possible performance.

Handling SQL NCHAR String Literals in OCI

You can switch it on by setting the environment variable ORA_NCHAR_LITERAL_REPLACE to TRUE. You can also achieve this behavior programmatically by using the OCI_NCHAR_LITERAL_REPLACE_ON and OCI_NCHAR_LITERAL_REPLACE_OFF modes in OCIEnvCreate() and OCIEnvNlsCreate(). So, for example, OCIEnvCreate(OCI_NCHAR_LITERAL_REPLACE_ON) turns on NCHAR literal replacement, while OCIEnvCreate(OCI_NCHAR_LITERAL_REPLACE_OFF) turns it off.

[...] Note that, when the NCHAR literal replacement is turned on, OCIStmtPrepare and OCIStmtPrepare2 will transform N' literals with U' literals in the SQL text and store the resulting SQL text in the statement handle. Thus, if the application uses OCI_ATTR_STATEMENT to retrieve the SQL text from the OCI statement handle, the SQL text will return U' instead of N' as specified in the original text.


(3) Answer for your question

From datatypes perspective, there is not difference between both queries provided


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...