CODEWITHSUNDEEP
sqloracle-databaseunicodeplsql
ahad
ahad

Reputation: 37

How to Use Nvarchar2 Column in Parameterized SQL Query for Unicode Characters?

Hi guyz i have a problem with Nvarchar2 column in Oracle 10g, basically i want to insert Unicode characters in DB table at run-time, i google it for this and find N prefix method to insert or update Unicode characters in Nvarchar2 column like below:

Insert into nvarchar_test (c_nvarchar) VALUES (N'اب ج قر');

Characters i used to insert above are URDU language characters, above statement is working perfectly, but my requirement is that i want to pass URDU Unicode characters at run-time as variable instead of hard-coded them i tried below PL-SQL code for this:

Declare     
  urdu_characters Nvarchar2 (500);    
Begin    
  urdu_characters := 'اب ج';    
  Insert into nvarchar_test (c_nvarchar) VALUES (N urdu_characters);    
End;

As you see above code is not correct because i'm not able to use N Prefix with variable and don't know how i will i do kindly help me to sort out this issue.

Thank You !

Upvotes: 1

Views: 1208

Answers (1)

J. Chomel
J. Chomel

Reputation: 8393

From your initial question: N changes the string constant, so use it at declaration time. Here is how you must go:

Declare     
  urdu_characters Nvarchar2 (500);    
Begin    
  urdu_characters := N'اب ج';    
  Insert into nvarchar_test (c_nvarchar) VALUES (urdu_characters);    
End;

But N'اب ج' is (more or less) equivalent to CAST('اب ج' AS NVARCHAR2(..)). A possibility to include urdu chars in a pl would be to use the ASCIISTR(..) function:

Declare     
  urdu_characters Nvarchar2 (500);    
Begin    
  urdu_characters := 'اب ج';    
  Insert into nvarchar_test (c_nvarchar) VALUES (ASCIISTR(urdu_characters));    
End;

Upvotes: 2

Related Questions