Home » SQL & PL/SQL » SQL & PL/SQL » umlaut when converting blob to varchar
umlaut when converting blob to varchar [message #293737] Mon, 14 January 2008 16:38 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Hi,
I have a blob column with content
"Renamed to Münchener Lebensversicherung AG"

I want to store this information into varchar column.
So i did UTL_RAW.CAST_TO_VARCHAR2() and stored the converted value into varchar column.

Problem i am facing is with umlaut. When I convert to varchar using UTL_RAW.CAST_TO_VARCHAR2 the converted data appears as below
"Renamed to M¿nchener Lebensversicherung AG"

How to keep the the same umlaut.

Regards,
Naveen
Re: umlaut when converting blob to varchar [message #293781 is a reply to message #293737] Mon, 14 January 2008 22:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Can you provide a reproducible test case? It works for me, as demonstrated below. It probably has to do with your character set of whatever you are trying to display it through.


SCOTT@orcl_11g> CREATE TABLE your_table
  2    (blob_column	 BLOB,
  3  	varchar2_column  VARCHAR2 (60))
  4  /

Table created.

SCOTT@orcl_11g> INSERT INTO your_table (blob_column)
  2  VALUES (UTL_RAW.CAST_TO_RAW ('Renamed to Münchener Lebensversicherung AG'))
  3  /

1 row created.

SCOTT@orcl_11g> UPDATE your_table
  2  SET    varchar2_column = UTL_RAW.CAST_TO_VARCHAR2 (blob_column)
  3  /

1 row updated.

SCOTT@orcl_11g> SELECT varchar2_column FROM your_table
  2  /

VARCHAR2_COLUMN
------------------------------------------------------------
Renamed to Münchener Lebensversicherung AG

SCOTT@orcl_11g> 

Re: umlaut when converting blob to varchar [message #293785 is a reply to message #293737] Mon, 14 January 2008 23:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your current (database or client, depending on how you did the operation) character set does not contain umlaut so they have been replaced by a "replacement" character that depends on your character set but is often "¿".

Regards
Michel

[Updated on: Mon, 14 January 2008 23:29]

Report message to a moderator

Re: umlaut when converting blob to varchar [message #293932 is a reply to message #293737] Tue, 15 January 2008 13:11 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Thanks for your valuable reply.

this is waht I querried in toad
SELECT UTL_RAW.cast_to_varchar2 (comments) comments_in_string,  
       comments
  FROM w_insurer 
 WHERE no = '6482';



And I saved the output to excel document. This was the result in the excel file.

 
COMMENTS_IN_STRING	                        COMMENTS	   
Renamed to M¿nchener Lebensversicherung AG	Renamed to Münchener Lebensversicherung AG	 


Re: umlaut when converting blob to varchar [message #293940 is a reply to message #293932] Tue, 15 January 2008 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the type of comments?

Regards
Michel
Re: umlaut when converting blob to varchar [message #293942 is a reply to message #293737] Tue, 15 January 2008 13:41 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Thanks for your valuable reply.
This is what I queried in toad :



SELECT UTL_RAW.cast_to_varchar2 (comments) comments_in_string,  
       comments
  FROM w_insurer 
 WHERE no = '6482'


And, save the output to excel document. Here is the content from excel file.


COMMENTS_IN_STRING	                        COMMENTS
Renamed to M¿nchener Lebensversicherung AG	Renamed to Münchener Lebensversicherung AG


Re: umlaut when converting blob to varchar [message #293945 is a reply to message #293942] Tue, 15 January 2008 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 15 January 2008 20:30
What is the type of comments?

Regards
Michel


Re: umlaut when converting blob to varchar [message #293946 is a reply to message #293737] Tue, 15 January 2008 13:56 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

it is Blob column
Re: umlaut when converting blob to varchar [message #293954 is a reply to message #293946] Tue, 15 January 2008 14:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You need to provide more information, in order to narrow down where the problem is occurring. Where does the blob data come from and how is it loaded? You need to do a simple test, like the one that I provided, from SQL*Plus first, not on your existing data set, not through Toad, and not checking the results in Excel. You need to determine at what point the problem is occurring. It could be that the data is corrupted when it is loaded or that there is a problem with what you are selecting it through, or a problem with what you are loading it into and displaying it from. Most likely your character set somewhere along the way does not allow for the umlat character. What character set are you using?
Re: umlaut when converting blob to varchar [message #293959 is a reply to message #293737] Tue, 15 January 2008 14:34 Go to previous message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Barbara, you are correct.
Data is transformed from one database (DB1) to another database (DB2).
I checked the NLS_PARAMETERS. It is different between the databases.
One has value UTF8 (source) and target database has value AL32UTF8.

I think problem should be because of this. Let me change the parameter and test it again.

Naveen

[Updated on: Tue, 15 January 2008 14:37]

Report message to a moderator

Previous Topic: Dynamic SQL in SP
Next Topic: How to query across schemas
Goto Forum:
  


Current Time: Sat Dec 10 03:00:09 CST 2016

Total time taken to generate the page: 0.07477 seconds