The UTL_ENCODE package provides the capabilities to encode and decode data.

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.

Parameters

Parameter Description
r Specifies the RAW string that is to be converted to a Base64 encoded string.
loid Specifies the ID of a large object that is to be converted to a Base64 encoded string.

Examples

Note Before you use the following example, invoke the following command:
SET bytea_output = escape;

This command instructs the server to escape non-printable characters and display BYTEA or RAW values in readable form on your screen. For more information, see PostgreSQL core file.

Use the BASE64_ENCODE function to convert a string to a Base64 encoded string. The following code provides an example:

SELECT UTL_ENCODE.BASE64_ENCODE(CAST ('abc' AS RAW));

In the example, the following result 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. The following syntax is used:

BASE64_DECODE(r IN RAW)

The function returns a RAW value.

Parameters

Parameter Description
r Specifies the Base64 encoded string that is to be converted. The string was encoded by the BASE64_ENCODE function.

Examples

Note Before you use the following example, invoke the following command:
SET bytea_output = escape;

This command instructs the server to escape non-printable characters and display BYTEA or RAW values in readable form on your screen. For more information, see PostgreSQL core file.

Use the BASE64_DECODE function to convert a Base64 encoded string to a RAW value. The following code provides an example:

SELECT UTL_ENCODE.BASE64_DECODE(CAST ('YWJj' AS RAW));

In the example, the following result 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. The following syntax is used:

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

The function returns a VARCHAR2 value.

Parameters

Parameter Description
buf Specifies the string that is to be formatted and encoded. The string must be a VARCHAR2 value.
encode_charset Specifies 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.
encoding Specifies 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. The following code provides an example:

SELECT UTL_ENCODE.MIMEHEADER_ENCODE('What is the date?') FROM DUAL;

In the example, the following result 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. The following syntax is used:

MIMEHEADER_DECODE(buf IN VARCHAR2)

The function returns a VARCHAR2 value.

Parameters

Parameter Description
buf Specifies 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. The following code provides an example:

SELECT UTL_ENCODE.MIMEHEADER_DECODE('=?UTF8?Q?What is the date??=') FROM DUAL;

In the example, the following result is returned:

 mimeheader_decode
-------------------
 What is the date?
(1 row)

QUOTED_PRINTABLE_ENCODE

The QUOTED_PRINTABLE_ENCODE function converts and encodes a string to the quoted-printable format. The following syntax is used:

QUOTED_PRINTABLE_ENCODE(r IN RAW)

The function returns a RAW value.

Parameters

Parameter Description
r Specifies the string that is to be encoded to the quoted-printable format.

Examples

Note Before you use the following example, invoke the following command:
SET bytea_output = escape;          

This command instructs the server to escape non-printable characters and display BYTEA or RAW values in readable form on your screen. For more information, see PostgreSQL core file.

Use the QUOTED_PRINTABLE_ENCODE function to encode a string. The following code provides an example:

SELECT UTL_ENCODE.QUOTED_PRINTABLE_ENCODE('E=mc2') FROM DUAL;

In the example, the following result 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. The following syntax is used:

QUOTED_PRINTABLE_DECODE(r IN RAW)

The function returns a RAW value.

Parameters

Parameter Description
r Specifies the encoded string that is to be decoded. The string is a RAW value that was encoded by the QUOTED_PRINTABLE_ENCODE function.

Examples

Note Before you use the following example, invoke the following command:
SET bytea_output = escape;

This command instructs the server to escape non-printable characters and display BYTEA or RAW values in readable form on your screen. For more information, see PostgreSQL core file.

Use the QUOTED_PRINTABLE_DECODE function to decode a string. The following code provides an example:

SELECT UTL_ENCODE.QUOTED_PRINTABLE_DECODE('E=3Dmc2') FROM DUAL;

In the example, the following result 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 character set you specify and then encodes the result string. The following syntax is used:

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

The function returns a VARCHAR2 value.

Parameters

Parameter Description
buf Specifies the string that is to be encoded.
encode_charset Specifies 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 encodes the input string. Default value: NULL.
encoding Specifies the encoding type that is 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. The following code provides an example:

SELECT UTL_ENCODE.TEXT_ENCODE('What is the date?', 'BIG5', UTL_ENCODE.BASE64) FROM DUAL;

In the example, the following result 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. The following syntax is used:

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

The function returns a VARCHAR2 value.

Parameters

Parameter Description
buf Specifies the string that is to be decoded. The string was encoded by the TEXT_ENCODE function.
encode charset Specifies 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 encodes the input string. Default value: NULL.
encoding Specifies the encoding type that is 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

In the following example, the TEXT_ENCODE function encodes a string and the TEXT_DECODE function decodes the string:

Use the TEXT_DECODE function to decode a string. The following code provides an example:

SELECT UTL_ENCODE.TEXT_DECODE('V2hhdCBpcyB0aGUgZGF0ZT8=', 'BIG5', UTL_ENCODE.BASE64) FROM DUAL;

In the example, the following result is returned:

    text_decode
-------------------
 What is the date?
(1 row)

UUENCODE

The UUENCODE function encodes a RAW string to a uuencoded string. The following syntax is used:

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.

Parameters

Parameter Description
r Specifies the string that is to be converted to a uuencoded string.
type Specifies the type of the uuencoded string that is to be returned. Default value: 1. For more information, see Table 1.
filename Specifies 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.
permission Specifies the permission mode. Default value: NULL.
Table 1. type
Value Constant
1 complete
2 header_piece
3 middle_piece
4 end_piece

Examples

Note Before you use the following example, invoke the following command:
SET bytea_output = escape;

This command instructs the server to escape non-printable characters and display BYTEA or RAW values in readable form on your screen. For more information, see PostgreSQL core file.

Use the UUENCODE function to encode a string. The following code provides an example:

SELECT UTL_ENCODE.UUENCODE('What is the date?') FROM DUAL;

In the example, the following result 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 corresponding RAW value that was encoded by the UUENCODE function. The following syntax is used:

UUDECODE(r IN RAW)

The function returns a RAW value.

Parameters

Parameter Description
r Specifies the uuencoded string that is to be converted to its corresponding RAW value.

Examples

Note Before you use the following example, invoke the following command:
SET bytea_output = escape;

This command instructs the server to escape non-printable characters and display BYTEA or RAW values in readable form on your screen. For more information, see PostgreSQL core file.

Use the UUDECODE function to decode a string. The following code provides an example:

SELECT UTL_ENCODE.UUDECODE('begin 0 uuencode.txt\01215VAA="!I<R!T:&4@9&%T93\\`\012`\012end\012') FROM DUAL;

In the example, the following result 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 that is 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. You cannot change the value of this parameter in the console. If you want to set this parameter to on,Submit a ticket to contact technical support.
  • Set the parameter to on and specify an encoded string that is in the invalid format as an input, as shown in the following example:
    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)
  • Set the parameter to off and specify an encoded string that is in the invalid format as an input, as shown in the following example:
    select utl_encode.base64_decode(utl_raw.cast_to_raw('NjMzNDgwN==gNjMzNjMz'));

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

    ERROR:  unexpected "=" while decoding base64 sequence