Home » Open Source » Programming Interfaces » NVARCHAR2 to RAW Different behaviour SQL and PLSQL (Oracle 11g, Windows 7)
NVARCHAR2 to RAW Different behaviour SQL and PLSQL [message #589707] Wed, 10 July 2013 05:36 Go to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
I have the following Table with NVARCHAR2 column.
The Character set in DB is


CREATE TABLE TEST2
(
  TEST_CHAR  NVARCHAR2(100)
)



The NLS language and Character set is given below.


NLS_LANGUAGE	AMERICAN
NLS_TERRITORY	AMERICA
NLS_CHARACTERSET	WE8MSWIN1252
NLS_NCHAR_CHARACTERSET	AL16UTF16



I have loaded the below data using SQL LOADER . The below data is in a UTF-8 text file.

ÁÀÂÄÃĂÅĀĄĈĆĊČÇÐĎÉÈÊËĒĚĖĘĔĜĞĠĢĤĦ



And the UTF data is preserved well while loading through SQL LOADER

Below is my SQL LOADER control file.


LOAD DATA 
CHARACTERSET  'UTF8'
INFILE 'data_load_2.txt' 
BADFILE 'data_load_2.bad'
DISCARDFILE 'data_load_2.dsc'

INTO TABLE "HUBDB_REL2"."TEST2"
INSERT
FIELDS TERMINATED BY ','
(TEST_CHAR)




The problem I am facing when the data is loaded from DELL BOOMI a cloud application the data is lost ie., after insertion the data is coming as 'ÁÀÂÄÃAÅAACCCCÇÐDÉÈÊËEEEEEGGGGHH'

In DELL BOOMI the file encoding is set as UTF8. Boomi will read from the plain file and map to its variables , at this stage also data is coming perfectly, only when BOOMI talks to ORACLE 11g through JDBC this is getting lost.

Earlier in BOOMI we had plain insert, so I tried to modify this as below. But here the Conversion to UTL_RAW is failing and giving wrong value, compared to the conversion I do from direct SQL using TOAD in oracle.


BOOMI Procedure BLOCK

declare 

 v_source_data long raw;
 v_nchar_cs varchar2(30) := 'AMERICAN_AMERICA.'||nls_charset_name(nls_charset_id('NCHAR_CS'));
 v_boomi_cs varchar2(30) := 'AMERICAN_AMERICA.AL32UTF8';
 v_nsource_data  NVARCHAR2(100);

begin

 v_source_data:=utl_raw.cast_to_raw(convert(?,'AL32UTF8'));
 
 SELECT utl_raw.cast_to_nvarchar2(utl_raw.convert(v_source_data,v_nchar_cs, v_boomi_cs))
 INTO v_nsource_data FROM DUAL;

 insert into test2 values(v_nsource_data);
 commit;

end;

Above the question mark (?) is the parameter which will be having the value 'ÁÀÂÄÃĂÅĀĄĈĆĊČÇÐĎÉÈÊËĒĚĖĘĔĜĞĠĢĤĦ'



The below I tried in SQL the Database column is giving different RAW value and STRING is giving different RAW value. Need advise on this behaviour.



SELECT utl_raw.cast_to_raw(convert(TEST_CHAR,'AL32UTF8')) RAW1
       , TEST_CHAR
       , 'ÁÀÂÄÃĂÅĀĄĈĆĊČÇÐĎÉÈÊËĒĚĖĘĔĜĞĠĢĤĦ' TEST_DATA,
       utl_raw.cast_to_raw(convert('ÁÀÂÄÃĂÅĀĄĈĆĊČÇÐĎÉÈÊËĒĚĖĘĔĜĞĠĢĤĦ','AL32UTF8')) RAW2
       FROM TEST2


Below is the output of the above QUERY. My apologies, I am running this in TOAD and the data is too long to get in with formatting output.


RAW1
----------
C381C380C382C384C383C482C385C480C484C488C486C48AC48CC387C390C48EC389C388C38AC38BC492C49AC496C498C494C49CC49EC4A0C4A2C4A4C4A6

TEST_CHAR
---------
ÁÀÂÄÃĂÅĀĄĈĆĊČÇÐĎÉÈÊËĒĚĖĘĔĜĞĠĢĤĦ

TEST_DATA
---------
ÁÀÂÄÃAÅAACCCCÇÐDÉÈÊËEEEEEGGGGHH

RAW2
--------
C381C380C382C384C38341C385414143434343C387C39044C389C388C38AC38B4545454545474747474848




TEST_CHAR is from the Table column data, and TEST_DATA is the one given as string in query.

Can you please advise, what can be wrong here.

Different RAW output for table data and string data.


Thanks,
Ninan.

[Updated on: Wed, 10 July 2013 05:38]

Report message to a moderator

Re: NVARCHAR2 to RAW Different behaviour SQL and PLSQL [message #589711 is a reply to message #589707] Wed, 10 July 2013 05:41 Go to previous messageGo to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
By mistake i posted in OPEN DATABASE INTERFACES. Can you please help to move this message to SQL & PLSQL
Re: NVARCHAR2 to RAW Different behaviour SQL and PLSQL [message #589714 is a reply to message #589707] Wed, 10 July 2013 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Try
SELECT utl_raw.cast_to_raw(convert(TEST_CHAR,'AL32UTF8')) RAW1
, TEST_CHAR
, N'ÁÀÂÄÃĂÅĀĄĈĆĊČÇÐĎÉÈÊËĒĚĖĘĔĜĞĠĢĤĦ' TEST_DATA
FROM TEST2
2/ If it does not work then your TOAD is not configured for Unicode (or at least these characters).

Regards
Michel
Re: NVARCHAR2 to RAW Different behaviour SQL and PLSQL [message #589718 is a reply to message #589714] Wed, 10 July 2013 05:51 Go to previous messageGo to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
No luck with that also. It displays ÁÀÂÄÃAÅAACCCCÇÐDÉÈÊËEEEEEGGGGHH

How do I configure TOAD for Unicode.

But the point here is TOAD is displaying the data from TABLE column correctly as ÁÀÂÄÃĂÅĀĄĈĆĊČÇÐĎÉÈÊËĒĚĖĘĔĜĞĠĢĤĦ

I am confused what can be the issue here.

But I tried this Query to find why the DELL BOOMI application (where the Encoding is configured as UTF8) is not working properly. The DELL BOOMI uses JDBC settings. Earlier they had mentioned to change the DB characterset, but I found it is not required since the SQL LOADER is loading correctly with UTF8 encoding format.
Re: NVARCHAR2 to RAW Different behaviour SQL and PLSQL [message #589724 is a reply to message #589718] Wed, 10 July 2013 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is then in the input.
If you type Ĕ and it returns E then the terminal does not support the character.

Regards
Michel
Re: NVARCHAR2 to RAW Different behaviour SQL and PLSQL [message #589732 is a reply to message #589724] Wed, 10 July 2013 06:38 Go to previous messageGo to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
By, Terminal you mean the local system. My operating system is Windows 7. Anything to do with the Character set of Operating system?

Re: NVARCHAR2 to RAW Different behaviour SQL and PLSQL [message #589744 is a reply to message #589732] Wed, 10 July 2013 07:31 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends not only on your OS but on your tool.
Just an example: open Notepad, type some text with characters like above, save the file, open a DOS box and use TYPE to display the content of the file...

Regards
Michel

Previous Topic: DOS script for adding an ODBC data source
Next Topic: Migrating Sql server backup to Oracle database
Goto Forum:
  


Current Time: Thu Mar 28 09:38:26 CDT 2024