Using oracle function in toracleoutput or talend function for base64 decoding

[borrisbeck]

HI

I'm extracting an salesforce module where a column is base64 encoded, i need to decrypt and load into an oracle table.

I have this oracle function "UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(incput_column))", any idea how i can use this function in the "Advanced setting>Additional column" in tOracleputput component? or is there any talend function available to decode base64 column?

Thanks

bb

[akumar2301]

to decode base64 , you could use simple java code in tjavarow or in tmap

https://community.talend.com/t5/Design-and-Development/Decrypt-Decode-BASE64-from-JSON-extract/td-p/...

this will help.

[borrisbeck]

Hi

I did saw that link before posting it, i don't want to use the tjavarow, i did tried various combinations in the tmap, but not successful, can someone show me an example of how to use those functions in the tmap, when i tried i'm getting error "BASE64Decoder cannot be resolved" and i tried other links such as "https://stackoverflow.com/questions/2817752/java-code-to-convert-byte-to-hexadecimal", but nothing worked out.

also i found this function in oracle ""UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(RAWTOHEX ()))" when i tried it in the toracleoutput>advanced settings>additional columns"

this didn't worked as well.the main question using the above function is, how to use the incoming row from tmap in this function?

can someone show me an example of both the java functions in tmap and using the oracle function in in the toracleoutput

thanks bb

[akumar2301 · Accepted solution]

Hello ,

I am able to decode zith below expression in tMap

new String(new sun.misc.BASE64Decoder().decodeBuffer(row1.newColumn), java.nio.charset.StandardCharsets.UTF_8);

[borrisbeck]

Hi
Thanks for responding, I tried your solution, i'm receiving the input column from salesforce as "bytle[]" datatype, so at first the job was failing with the error "The method decodeBuffer(String) in the type CharacterDecoder is not applicable for the arguments (byte[])", so using the 'tConvertertype' I changed the datatype to "String" for that particular column, now the job is not failing but the decoding is not proper, i'm the getting the values as "���n�k��" in a non-readable form i'm using "CLOB"datatype in oracle...... what am i doing wrong here?

[borrisbeck]

Hi Ahbishek, 

I figured it out, instead of using tconvertertype, i used the java string function, its working fine... thank a lot, much appreciated.

new String(new sun.misc.BASE64Decoder().decodeBuffer(new String(row1.VersionData) ), java.nio.charset.StandardCharsets.UTF_8);

https://www.mkyong.com/java/how-do-convert-byte-array-to-string-in-java/