How to insert Indian Rupee symbol in oracle 11g?

Question

I want to insert Indian Rupee symbol in oracle 11g.

I have tried:

insert into currency(name, symbol) values ('rupee', unistr('\20B9'));

but its not working.

SELECT ascii('₹') FROM dual; it gives ascii value for Rupee symbol but after insert it shows box in the row.

here you can see

SELECT chr(14844601) FROM dual;

Answer 1

Seems that you just need to change a font in SQL Developer. Check this SQLFiddle.

There are two things wrong in your question text: to get unicode symbol from it's code nchr() function must be used instead of chr(). Second is that '₹' constant string treated as varchar2 and to properly specify nvarchar2 constant you must prepend N to this literal: N'₹' .

To change font just open Tools -> Preferences ... menu

and choose Code Editor -> Fonts node. At this page you can select font from Font Name dropdown list. Copy and past rupee symbol into Sample Text field to quickly see if font are acceptable. Arial (on win7) works fine for me:

This setting changes grid font as well as font in code editor:

Actions above puts you only to a half of the way to get full rupee symbol support because select N'₹' from dual will return only question sign. This is because of query text encoding conversion done between client and server side.
To deal with this issue Oracle provides ORA_NCHAR_LITERAL_REPLACE environment setting. This setting guides client API to analyze query text for character constants prefixed with N and use special internal format to encode such a data on client side before transferring query text to server. Unfortunately SQL Developer uses Thin JDBC API which ignores this environment setting, so adding this environment variable doesn't solve a problem.

But there are another way to turn on same behavior with oracle.jdbc.convertNcharLiterals property which acts on a system and connection levels.
To turn this property on, locate ide.conf file in sqldeveloper\ide\bin directory in SQL Developer installation folder and add this line to the end of a file:

AddVMOption -Doracle.jdbc.convertNcharLiterals=true

Save file and restart SQL Developer, all things must work now:

P.S. Instructions based on SQL Developer Version 3.2, old versions may have another location of configuration file. E.g. sqldeveloper\bin\sqldeveloper.conf .