Issue converting AL32UTF8 to Base64

user_17834573 0
Viewed 98

Why joining base64-encoded parts of a string in WE8ISO8859P1 charset gives the same result as base64-encoding the full string, but the same does not happen in AL32UTF8?

WITH charset_names AS (
    SELECT 'WE8ISO8859P1' charset_name FROM dual
    UNION ALL
    SELECT 'AL32UTF8' FROM dual
),
encoded_strings AS (
    SELECT charset_name, 'FULL' style, NULL nivel, utl_encode.text_encode('DESCRIÇÃO_DA_REQUISIÇÃO', charset_name, 1) encoded_string
    FROM
        charset_names
    UNION ALL
    SELECT charset_name, 'SPLITTED', nivel, utl_encode.text_encode(substr('DESCRIÇÃO_DA_REQUISIÇÃO', (nivel-1)*3+1, 3), charset_name, 1) encoded_string
    FROM
        charset_names,
        (
            SELECT level nivel
            FROM dual
            CONNECT BY level <= 8
        )
)
SELECT charset_name, style, LISTAGG(encoded_string) WITHIN GROUP (ORDER BY charset_name, style, nivel)
FROM encoded_strings
GROUP BY style, charset_name
ORDER BY charset_name, style

Query results:

AL32UTF8        FULL        REVTQ1JJw4fDg09fREFfUkVRVUlTScOHw4NP       ok
AL32UTF8        SPLITTED    REVTQ1JJw4fDg08=X0RBX1JFUVVJU0nDhw==w4NP   invalid
WE8ISO8859P1    FULL        REVTQ1JJx8NPX0RBX1JFUVVJU0nHw08=           ok
WE8ISO8859P1    SPLITTED    REVTQ1JJx8NPX0RBX1JFUVVJU0nHw08=           ok

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production Database charset: WE8ISO8859P1

utf-8 base64 plsql oracle sql
1 Answers

BASE64 encoding takes 3 Bytes and and transforms it into 4 ASCII characters. If your input data is not whole multiply of 3 then BASE64 is padded with =

Character set WE8ISO8859P1 (aka. ISO-8859-1) is a single-Byte character set, i.e. every character is one Byte. In AL32UTF8 (aka. UTF-8) one character can be 1 to 4 Bytes

Characters DESCRI use 1 Byte in UTF-8 each. These are 6 characters => 6 Bytes = 2*3 Bytes which are translated to 8 BASE64 characters. Thus the first 8 characters REVTQ1JJ are the same.

You may try SUBSTRB() instead of SUBSTR(), it may work but I did not test because your query does not make much sense to me. Perhaps you are looking for this: Base64 encoding and decoding in oracle

user_30272660
Related