The UTL_ENCODE package provides encoding and decoding capabilities.

BASE64_ENCODE

The BASE64_ENCODE function converts a RAW string or a TEXT string to a Base64-encoded string. You can use the following two types of syntax for the BASE64_ENCODE function:

  • BASE64_ENCODE(r IN RAW)
  • BASE64_ENCODE(loid IN OID)

This function returns a RAW value or an object ID.

Parameter

ParameterDescription
rSpecifies the RAW string that is to be converted to a Base64-encoded string.
loidSpecifies the ID of a large object that is to be converted to a Base64-encoded string.

Examples

Note Before you run the following example, you must run the following command to instruct the server to escape non-printable characters and display BYTEA or RAW values in readable form on your screen: 
SET bytea_output = escape;

For more information, see PostgreSQL core file.

Use the BASE64_ENCODE function to convert a string to a Base64-encoded string. Example:

SELECT UTL_ENCODE.BASE64_ENCODE(CAST ('abc' AS RAW));
A similar output is returned:
 base64_encode
---------------
 YWJj
(1 row)

BASE64_DECODE

The BASE64_DECODE function converts a Base64-encoded string to its original value that was encoded by the BASE64_ENCODE function. Syntax:

BASE64_DECODE(r IN RAW)

The function returns a RAW value.

Parameter

ParameterDescription
rThe string after it is encoded by using the BASE64_ENCODE function.

Examples

Note Before you run the following example, you must run the following command to instruct the server to escape non-printable characters and display BYTEA or RAW values in readable form on your screen: 
SET bytea_output = escape;

For more information, see PostgreSQL core file.

Use the BASE64_DECODE function to convert a Base64-encoded string to a RAW value.

SELECT UTL_ENCODE.BASE64_DECODE(CAST ('YWJj' AS RAW));
A similar output is returned:
 base64_decode
---------------
 abc
(1 row)

MIMEHEADER_ENCODE

The MIMEHEADER_ENCODE function converts a string to the MIME header format and then encodes the string.

MIMEHEADER_ENCODE(buf IN VARCHAR2, encode_charset IN VARCHAR2 DEFAULT NULL, encoding IN INTEGER DEFAULT NULL)

The function returns a VARCHAR2 value.

Parameter

ParameterDescription
bufSpecifies the string to be formatted and encoded. The string must be a VARCHAR2 value.
encode_charsetSpecifies the character set that you want to use for the result string converted from the input string. This function converts the input string to the corresponding string that uses the specified character set before the function formats and encodes the input string. Default value: NULL.
encodingSpecifies the encoding type that is used to encode the string. Valid values:
  • Q: quoted-printable encoding
  • B: Base64-encoding
Note By default, quoted-printable encoding is used.

Examples

Use the MIMEHEADER_ENCODE function to encode a string. Example:

SELECT UTL_ENCODE.MIMEHEADER_ENCODE('What is the date?') FROM DUAL;
A similar output is returned:
      mimeheader_encode
------------------------------
 =?UTF8?Q?What is the date??=
(1 row)

MIMEHEADER_DECODE

The MIMEHEADER_DECODE function decodes a value that was encoded by the MIMEHEADER_ENCODE function. Syntax:

MIMEHEADER_DECODE(buf IN VARCHAR2)

The function returns a VARCHAR2 value.

Parameter

ParameterDescription
bufSpecifies the value that is to be decoded. The value was encoded by the MIMEHEADER_ENCODE function.

Examples

Use the MIMEHEADER_DECODE function to decode a string that was encoded by the MIMEHEADER_ENCODE function. Example:

SELECT UTL_ENCODE.MIMEHEADER_DECODE('=?UTF8?Q?What is the date??=') FROM DUAL;
A similar output is returned:
 mimeheader_decode
-------------------
 What is the date?
(1 row)

QUOTED_PRINTABLE_ENCODE

The QUOTED_PRINTABLE_ENCODE function converts a string to and encodes the string in the quoted-printable format. Syntax:

QUOTED_PRINTABLE_ENCODE(r IN RAW)

The function returns a RAW value.

Parameter

ParameterDescription
rSpecifies the string to be encoded in the quoted-printable format.

Examples

Note Before you run the following example, you must run the following command to instruct the server to escape non-printable characters and display BYTEA or RAW values in readable form on your screen: 
SET bytea_output = escape;

For more information, see PostgreSQL core file.

Use the QUOTED_PRINTABLE_ENCODE function to encode a string. Example:

SELECT UTL_ENCODE.QUOTED_PRINTABLE_ENCODE('E=mc2') FROM DUAL;
A similar output is returned:
 quoted_printable_encode
-------------------------
 E=3Dmc2
(1 row)

QUOTED_PRINTABLE_DECODE

The QUOTED_PRINTABLE_DECODE function decodes an encoded quoted-printable string to a RAW string. Syntax:

QUOTED_PRINTABLE_DECODE(r IN RAW)

The function returns a RAW value.

Parameter

ParameterDescription
rSpecifies the string to be encoded by using the QUOTED_PRINTABLE_ENCODE function. The string is a RAW value.

Examples

Note Before you run the following example, you must run the following command to instruct the server to escape non-printable characters and display BYTEA or RAW values in readable form on your screen: 
SET bytea_output = escape;

For more information, see PostgreSQL core file.

Use the QUOTED_PRINTABLE_DECODE function to decode a string. Example:

SELECT UTL_ENCODE.QUOTED_PRINTABLE_DECODE('E=3Dmc2') FROM DUAL;
A similar output is returned:
 quoted_printable_decode
-------------------------
 E=mc2
(1 row)

TEXT_ENCODE

The TEXT_ENCODE function converts an input string to a string that uses the specified character set and then encodes the result string. Syntax:

TEXT_DECODE(buf IN VARCHAR2, encode_charset IN VARCHAR2 DEFAULT NULL, encoding IN PLS_INTEGER DEFAULT NULL)

The function returns a VARCHAR2 value.

Parameter

ParameterDescription
bufSpecifies the string to be encoded.
encode_charsetSpecifies the character set specified for conversion. Default value: NULL.
encodingSpecifies the encoding type to be used by the TEXT_ENCODE function. Valid values:
  • Q: quoted-printable encoding
  • B: Base64 encoding
Note By default, quoted-printable encoding is used.

Examples

Use the TEXT_ENCODE function to encode a string. Example:

SELECT UTL_ENCODE.TEXT_ENCODE('What is the date?', 'BIG5', UTL_ENCODE.BASE64) FROM DUAL;
A similar output is returned:
       text_encode
--------------------------
 V2hhdCBpcyB0aGUgZGF0ZT8=
(1 row)

TEXT_DECODE

The TEXT_DECODE function converts and decodes an encoded string to its corresponding VARCHAR2 value that was encoded by the TEXT_ENCODE function. Syntax:

TEXT_DECODE(buf IN VARCHAR2, encode_charset IN VARCHAR2 DEFAULT NULL, encoding IN PLS_INTEGER DEFAULT NULL)

The function returns a VARCHAR2 value.

Parameter

ParameterDescription
bufSpecifies the string encoded by using the TEXT_ENCODE function.
encode charsetSpecifies the character set specified for conversion. Default value: NULL.
encodingSpecifies the encoding type to be used by the TEXT_DECODE function. Valid values:
  • UTL_ENCODE.QUOTED_PRINTABLE: quoted-printable encoding
  • UTL_ENCODE.BASE64: Base64 encoding
Note By default, quoted-printable encoding is used.

Examples

Use the TEXT_DECODE function to decode a string. Example:

SELECT UTL_ENCODE.TEXT_DECODE('V2hhdCBpcyB0aGUgZGF0ZT8=', 'BIG5', UTL_ENCODE.BASE64) FROM DUAL;
A similar output is returned:
    text_decode
-------------------
 What is the date?
(1 row)

UUENCODE

The UUENCODE function encodes a RAW string to a uuencoded string. Syntax:

UUENCODE(r IN RAW, type IN INTEGER DEFAULT 1, filename IN VARCHAR2 DEFAULT NULL, permission IN VARCHAR2 DEFAULT NULL)

The function returns a RAW value.

Parameter

ParameterDescription
rSpecifies the string to be converted to a uuencoded string.
typeSpecifies the type of the UUENCODED string to be returned. Default value: 1. For more information, see Type.
filenameSpecifies the file name that you want to include in the uuencoded string. If you do not specify a file name, the UUENCODE function includes the file name uuencode.txt in the encoded string.
permissionSpecifies the permission mode. Default value: NULL.
Table 1. Type
Valid ValueConstant
1complete
2header_piece
3middle_piece
4end_piece

Examples

Note Before you run the following example, you must run the following command to instruct the server to escape non-printable characters and display BYTEA or RAW values in readable form on your screen: 
SET bytea_output = escape;

For more information, see PostgreSQL core file.

Use the UUENCODE function to encode a string. Example:

SELECT UTL_ENCODE.UUENCODE('What is the date?') FROM DUAL;
A similar output is returned:
                              uuencode
--------------------------------------------------------------------
 begin 0 uuencode.txt\01215VAA="!I<R!T:&4@9&%T93\\`\012`\012end\012
(1 row)

UUDECODE

The UUDECODE function converts and decodes a uuencoded string to its RAW value that was encoded by the UUENCODE function. Syntax:

UUDECODE(r IN RAW)

The function returns a RAW value.

Parameter

ParameterDescription
rSpecifies the UUENCODED string to be converted to its RAW value.

Examples

Note Before you run the following example, you must run the following command to instruct the server to escape non-printable characters and display BYTEA or RAW values in readable form on your screen: 
SET bytea_output = escape;

For more information, see PostgreSQL core file.

Use the UUDECODE function to decode a string. Example:

SELECT UTL_ENCODE.UUDECODE('begin 0 uuencode.txt\01215VAA="!I<R!T:&4@9&%T93\\`\012`\012end\012') FROM DUAL;
A similar output is returned:
     uudecode
-------------------
 What is the date?
(1 row)

polar_enable_base64_decode

If you set the polar_enable_base64_decode parameter to on, the BASE64_DECODE function automatically identifies your input encoded string in the invalid format. Then, the function decodes the valid first part of the encoded string. For more information about the BASE64_DECODE function, see BASE64_DECODE.

Note By default, the polar_enable_base64_decode parameter is set to off and cannot be modified in the console. To use this feature, go to the Quota Center. Click Apply in the Actions column corresponding to polar_enable_base64_decode.
  • The following code shows an example of an encoded string passed in after the polar_enable_base64_decode parameter is set to on:
    select utl_encode.base64_decode(utl_raw.cast_to_raw('NjMzNDgwN==gNjMzNjMz'));

    The BASE64_DECODE function decodes the valid first part of the encoded string. The following sample success response is returned:

     base64_decode  
----------------
 \x363333343830
(1 row)
  • The following code shows an example of an encoded string passed in after the polar_enable_base64_decode parameter is set to off:
    select utl_encode.base64_decode(utl_raw.cast_to_raw('NjMzNDgwN==gNjMzNjMz'));

    The BASE64_DECODE function cannot decode the encoded string in the invalid format. A similar error response is returned:

    ERROR:  unexpected "=" while decoding base64 sequence