Conversion of NCLOB to Unicode/HEX/BASE64

• My Oracle 12c Database characterset is WE8ISO8859P1

• I have a table with column as NCLOB , and want to extract the data 3 different encoding

  • BASE64
  • HEX
  • UNICODE

• I tried writing some function , the output is correct , but why new line is present in the encoded string.

Please find the details of the function as below

        WITH 
  FUNCTION EncodeBASE64(InClearChar IN  CLOB) RETURN CLOB IS

    dest_lob BLOB;  
    lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
    dest_offset INTEGER := 1;
    src_offset INTEGER := 1;
    read_offset INTEGER := 1;
    warning INTEGER;
    ClobLen INTEGER := DBMS_LOB.GETLENGTH(InClearChar);

    amount INTEGER := 1440; -- must be a whole multiple of 3
    -- size of a whole multiple of 48 is beneficial to get NEW_LINE after each 64 characters 
    buffer RAW(1440);
    res CLOB := EMPTY_CLOB();

     BEGIN

    IF InClearChar IS NULL OR NVL(ClobLen, 0) = 0 THEN 
        RETURN NULL;
    ELSIF ClobLen <= 24000 THEN
        RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(InClearChar)));
    END IF;
    -- UTL_ENCODE.BASE64_ENCODE is limited to 32k/(3/4), process in chunks if bigger    

    DBMS_LOB.CREATETEMPORARY(dest_lob, TRUE);
    DBMS_LOB.CONVERTTOBLOB(dest_lob, InClearChar, DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset, 
    DBMS_LOB.DEFAULT_CSID, lang_context, warning);
    LOOP
        EXIT WHEN read_offset >= dest_offset;
        DBMS_LOB.READ(dest_lob, amount, read_offset, buffer);
        res := res || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(buffer));       
        read_offset := read_offset + amount;
    END LOOP;
    DBMS_LOB.FREETEMPORARY(dest_lob);
    RETURN res;

    END EncodeBASE64;
    FUNCTION castToHex(InClearChar IN  NCLOB) RETURN CLOB IS

    dest_lob BLOB;  
    lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
    dest_offset INTEGER := 1;
    src_offset INTEGER := 1;
    read_offset INTEGER := 1;
    warning INTEGER;
    ClobLen INTEGER := DBMS_LOB.GETLENGTH(InClearChar);

    amount INTEGER := 1440; -- must be a whole multiple of 3
    -- size of a whole multiple of 48 is beneficial to get NEW_LINE after each 64 characters 
    buffer RAW(1440);
    res CLOB := EMPTY_CLOB();

BEGIN

    IF InClearChar IS NULL OR NVL(ClobLen, 0) = 0 THEN 
        RETURN NULL;
    ELSIF ClobLen <= 24000 THEN
        RETURN RAWTOHEX(UTL_RAW.CAST_TO_RAW(InClearChar));
    END IF;
    -- UTL_ENCODE.BASE64_ENCODE is limited to 32k/(3/4), process in chunks if bigger    

    DBMS_LOB.CREATETEMPORARY(dest_lob, TRUE);
    DBMS_LOB.CONVERTTOBLOB(dest_lob, InClearChar, DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset, 
    DBMS_LOB.DEFAULT_CSID, lang_context, warning);
    LOOP
        EXIT WHEN read_offset >= dest_offset;
        DBMS_LOB.READ(dest_lob, amount, read_offset, buffer);
        res := res || RAWTOHEX(buffer);       
        read_offset := read_offset + amount;
    END LOOP;
    DBMS_LOB.FREETEMPORARY(dest_lob);
    RETURN res;

    END castToHex;
   
SELECT EncodeBASE64('uživaÄ Ê Í Õ Ø A B C D Etele, kteří temp in the town  A B C D Etele, kteří') as encodedBASE64
from dual;

OUTPUT encodedBASE64 -> dcW+aXZhw4Qgw4ogw40gw5Ugw5ggQSBCIEMgRCBFdGVsZSwga3RlxZnDrSB0ZW1w IGluIHRoZSB0b3duICBBIEIgQyBEIEV0ZWxlLCBrdGXFmcOt

Question 1 - When I use this encodedBASE64 online to convert to String, it is returning the text correctly , but why new line is present in the encoded string , even If I remove the new line output is not changed

Question 2 - How to convert this string to unicode format ,please help