OraFAQ Forum: SQL & PL/SQL » umlaut when converting blob to varchar

Home » SQL & PL/SQL » SQL & PL/SQL » umlaut when converting blob to varchar

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

umlaut when converting blob to varchar [message #293737]

Mon, 14 January 2008 16:38

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

Report message to a moderator

Re: umlaut when converting blob to varchar [message #293781 is a reply to message #293737]

Mon, 14 January 2008 22:56

Barbara Boehmer
Messages: 9106
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>

Report message to a moderator

Re: umlaut when converting blob to varchar [message #293785 is a reply to message #293737]

Mon, 14 January 2008 23:29

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

Re: umlaut when converting blob to varchar [message #293940 is a reply to message #293932]

Tue, 15 January 2008 13:30

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What is the type of comments?

Regards
Michel

Report message to a moderator

Re: umlaut when converting blob to varchar [message #293942 is a reply to message #293737]

Tue, 15 January 2008 13:41

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

Report message to a moderator

Re: umlaut when converting blob to varchar [message #293945 is a reply to message #293942]

Tue, 15 January 2008 13:53

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Tue, 15 January 2008 20:30

What is the type of comments?

Regards
Michel

Report message to a moderator

Re: umlaut when converting blob to varchar [message #293946 is a reply to message #293737]

Tue, 15 January 2008 13:56

navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA

Senior Member

it is Blob column

Report message to a moderator

Re: umlaut when converting blob to varchar [message #293954 is a reply to message #293946]

Tue, 15 January 2008 14:17

Barbara Boehmer
Messages: 9106
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?

Report message to a moderator

Re: umlaut when converting blob to varchar [message #293959 is a reply to message #293737]

Tue, 15 January 2008 14:34

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 21 05:44:23 CDT 2025