|NVARCHAR2 to RAW Different behaviour SQL and PLSQL [message #589707]
||Wed, 10 July 2013 05:36
Registered: June 2011
I have the following Table with NVARCHAR2 column. |
The Character set in DB is
CREATE TABLE TEST2
The NLS language and Character set is given below.
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.
INTO TABLE "HUBDB_REL2"."TEST2"
FIELDS TERMINATED BY ','
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
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';
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);
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_DATA,
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.
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.
[Updated on: Wed, 10 July 2013 05:38]
Report message to a moderator