• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1110
  • Last Modified:

Oracle nvarchar2

I have a NVARCHAR2 column, whenever I try to insert 'œ' string, it displays as ¿.
Not really sure, why it is happening although my column type is NVARCHAR2(240).

Please help.

0
dojjol
Asked:
dojjol
  • 7
  • 6
1 Solution
 
wdosanjosCommented:
That's generally a problem with the software you are using to query the database, which cannot display the international character.  Try viewing the column value using a different software.
0
 
dojjolAuthor Commented:
Hey thanks, could you please suggest any query software I can use, I have tried Toad and SQLDeveloper., but both display ¿
0
 
htonkovCommented:
Hi!
Toad depends on version, 10.6 displays unicode, but 9.6 does not (I'm not sure for versions in between)

Did you try this select:

select unistr('\0153') from dual;

In Toad 10.6 I get correct 'œ', but in 9.6. I get 'o'

It is also possible that application you used to write to database does not support that character correctly.

Regards

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dojjolAuthor Commented:
thanks, it worked.

I need one last help on this, is there any predefined function to which I can pass 'œ' and it returns me
unicode value  - '\0153' ?
0
 
htonkovCommented:
You might consider alternative solution:

http://www.fileformat.info/info/unicode/char/153/index.htm
Just search for the character and you can find its code, and you can be sure that that IS the correct character
0
 
htonkovCommented:
There is function ASCIISTR, but I didn't get expected results in Toad because it seems that when parsing data it converted unicode to nearest ascii character before executing SQL...

select asciistr('œ'),asciistr(unistr('\0153')),unistr('\0153') from dual;

Regards...
0
 
dojjolAuthor Commented:
I am using sqldeveloper to test and my front end application is java, both of them returns inverted question mark "¿" not really sure why,

as I executed select unistr('\0153') from dual;, returns me correct result, I am pretty sure oracle supports it.

I am not really sure how to send these type of parameter values from my client  to oracle.

0
 
htonkovCommented:
If that's the case I suppose that you need to use setFormOfUse() method when inserting or updating NVARCHAR2 column.

Check this link:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch7progrunicode.htm

Chapter: Binding and Defining Java Strings to SQL NCHAR Datatypes

0
 
dojjolAuthor Commented:
Thanks htonlov.

I do it while setting the parameter, to insert or update the table row, but somehow when I fetch the row back, it gives me inverted question mark.

so I have no way to test it that if it is saved as is or as inverted question mark.
0
 
htonkovCommented:
You sad that "select unistr('\0153') from dual" got correct result... in the same application execute "select COLUMN from TABLE" where you inserted that character...

Also just to be sure (if you didnt already) try to run System.out.println("œ"), in java and see if console displays it correctly ...

0
 
dojjolAuthor Commented:
I have two parts to this problem.

a. running through sqldevloper- so if pass select * from my table where my_column = 'aaaœ' , it does not return correct result and returns me 'aaa¿'.

b. when sending requests from java with setForm, on debugging in eclipse (java), it shows me that it is storing the value as 'aaaœ' but when my front end application fetch the result to display on screen, i get 'aaa¿' on my screen. System.out.println("œ") displays "œ".

0
 
htonkovCommented:
OK, please lets wrap around the facts

Run this:

1. in sqldeveloper

delete from my_table;
insert into my_table(my_column) values (unistr('\0153'));
commit;
select my_column from my_table;

-- do you get correct character? (answer A)

select unistr('\0153') from dual;

-- do you get correct character? (answer B)

2. Now go to java
execute statement in java
"select my_column from my_table"
and output result System.out.println


-- do you get correct character? (answer C)

Answers:
A: YES/NO
B: YES/NO
C: YES/NO

0
 
dojjolAuthor Commented:
Thanks htonkov

A. YES
B. YES
C. NO with Exception
In java when I ran

String sql = "select my_column from my_table";
               ResultSet rs= stmt.executeQuery(sql);
               
               while(rs.next());
               {
                     System.out.println(rs.getString(1));
               }

I got exception

Exception in thread "main" java.sql.SQLException: Exhausted Resultset
      at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
      at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
0
 
htonkovCommented:
This code looks OK to me...
you don't have linenumber in stack trace of your code, do you maybe have several statements?


0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now