CODEWITHSUNDEEP
databaseoracle-databaseunicodenvarchar
अक्षय परूळेकर
अक्षय परूळेकर

Reputation: 312

How to create table with column names and Entries with Unicode Character in Oracle?

I've tried following queries,

create table विद्यार्थी(आयडी int, नाव varchar(50), वर्ग varchar(10));
insert into विद्यार्थी values(1,'अक्षय','पहिली'); 
insert into विद्यार्थी values(2,'नारायण','दुसरी');

This works well in MySQL but it doesn't work in Oracle. Oracle Gives Error as Invalid Character

Then I tried to use column names in English as follows with datatype NVARCHAR2.

create table student(id int, name nvarchar2(50), class nvarchar2(50));
insert into student values(1,'अक्षय','पहिली'); 
insert into student values(2,'नारायण','दुसरी');

No Error, but when I fire

select * from student;

After firing SELECT query, I got values from Table as inverted question marks as shown in screenshot attached. Oracle 10g and Oracle 12c, both give same result.

Unicode Representation by Oracle 10g

  1. So How can I use column names with unicode characters in Oracle?
  2. If I used English column names with datatype NVARCHAR2 and inserted unicode character values, then why I'm not getting unicode character values in result of SELECT query as show in screenshot? How to sort out this?

Upvotes: 1

Views: 2647

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59557

In Oracle this should be possible:

create table "विद्यार्थी" ("आयडी" integer, "नाव" varchar(50), "वर्ग" varchar(10));

However, I would recommend "standard" characters and then use a view with devanagari:

create table MY_TABLE(ID integer, NAME varchar(50), CLASS varchar(10));

CREATE OR REPLACE VIEW "विद्यार्थी" AS
SELECT ID AS "आयडी", NAME AS "नाव", CLASS AS "वर्ग";

NB, you could also insert data into the view with insert into "विद्यार्थी" values (1,'अक्षय','पहिली');

Do you have any other SQL client, e.g. TOAD or SQL Developer rather than APEX in internet explorer? Character encoding could be a challenge there.

Upvotes: 2

Related Questions