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:
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