| NVARCHAR2 to RAW Different behaviour SQL and PLSQL [message #589707] |
Wed, 10 July 2013 05:36  |
 |
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
