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

Table 1. UTL_ENCODE functions and stored procedures
Function/stored procedure Return type Description
BASE64_DECODE(r) RAW Translates a Base64 encoded string to the original RAW value.
BASE64_ENCODE(r) RAW Translates a RAW string to an encoded Base64 value.
BASE64_ENCODE(loid) TEXT Translates a TEXT string to an encoded Base64 value.
MIMEHEADER_DECODE(buf) VARCHAR2 Translates an encoded MIMEHEADER formatted string to its original value.
MIMEHEADER_ENCODE(buf, encode_charset, encoding) VARCHAR2 Converts and encodes a string in MIMEHEADER format.
QUOTED_PRINTABLE_DECODE(r) RAW Translates an encoded string to a RAW value.
QUOTED_PRINTABLE_ENCODE(r) RAW Translates an input string to a quoted-printable formatted RAW value.
TEXT_DECODE(buf, encode_charset, encoding) VARCHAR2 Decodes a string encoded by TEXT_ENCODE.
TEXT_ENCODE(buf, encode_charset, encoding) VARCHAR2 Translates a string to a user-specified character set, and then encode the string.
UUDECODE(r) RAW Translates a uuencode encoded string to a RAW value.
UUENCODE(r, type, filename, permission) RAW Translates a RAW string to an encoded uuencode value.

BASE64_DECODE

Converts a Base64 encoded string into the original value that is encoded by the BASE64_ENCODE function. Syntax:

BASE64_DECODE(r IN RAW)

This function returns a RAW value.

Parameters

Parameter Description
r The r parameter is the string that contains the Base64 encoded data that will be converted into a RAW value.

Examples

Note Before using this example, you must run 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. For more information, see the Postgres Core Documentation available at: http://www.enterprisedb.com/docs/en/9.3/pg/datatype-binary.html

The following example uses the BASE64_ENCODE function to encode a string that contains the text abc and then uses the BASE64_DECODE function to decode the string:

edb=# SELECT UTL_ENCODE.BASE64_ENCODE(CAST ('abc' AS RAW));
 base64_encode
---------------
 YWJj
(1 row)

edb=# SELECT UTL_ENCODE.BASE64_DECODE(CAST ('YWJj' AS RAW));
 base64_decode
---------------
 abc
(1 row)

BASE64_ENCODE

The BASE64_ENCODE function converts and encodes a string in Base64 format, as described in RFC 4648. This function is useful for composing MIME emails that you intend to send using the UTL_SMTP package. The BASE64_ENCODE function has two syntaxes:

BASE64_ENCODE(r IN RAW)

And

BASE64_ENCODE(loid IN OID)

This function returns a RAW value or an OID.

Parameters

Parameter Description
r The r parameter specifies the RAW string that will be converted into Base64.
loid The loid parameter specifies the ID of a large object that will be converted into Base64.

Examples

Note Before using this example, you must run 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. For more information, see the Postgres Core Documentation available at: http://www.enterprisedb.com/docs/en/9.3/pg/datatype-binary.html

The following example uses the BASE64_ENCODE function to encode a string that contains the text abc and then uses the BASE64_DECODE function to decode the string:

edb=# SELECT UTL_ENCODE.BASE64_ENCODE(CAST ('abc' AS RAW));
 base64_encode
---------------
 YWJj
(1 row)

edb=# SELECT UTL_ENCODE.BASE64_DECODE(CAST ('YWJj' AS RAW));
 base64_decode
---------------
 abc
(1 row)

MIMEHEADER_DECODE

The MIMEHEADER_DECODE function decodes values that are encoded by the MIMEHEADER_ENCODE function. Syntax:

MIMEHEADER_DECODE(buf IN VARCHAR2)

This function returns a VARCHAR2 value.

Parameters

Parameter Description
buf The buf parameter contains the value (encoded by the MIMEHEADER_ENCODE function) that will be decoded.

Examples

The following example uses the MIMEHEADER_ENCODE function to encode a string and then uses the MIMEHEADER_DECODE function to decode the string:

edb=# SELECT UTL_ENCODE.MIMEHEADER_ENCODE('What is the date?') FROM DUAL;
      mimeheader_encode
------------------------------
 =? UTF8? Q? What is the date?? =
(1 row)

edb=# SELECT UTL_ENCODE.MIMEHEADER_DECODE('=? UTF8? Q? What is the date?? =') FROM DUAL;
 mimeheader_decode
-------------------
 What is the date?
(1 row)

MIMEHEADER_ENCODE

The MIMEHEADER_ENCODE function converts a string into mime header format, and then encodes the string. Syntax:

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

This function returns a VARCHAR2 value.

Parameters

Parameter Description
buf The buf parameter contains the string that will be formatted and encoded. The string is a VARCHAR2 value.
encode_charset The encode_charset parameter specifies the character set into which the string will be converted before being formatted and encoded. Default value: NULL.
encoding The encoding parameter specifies the encoding type used when encoding the string. You can specify one of the following two encoding types:
  • Specify the Q encoding type to enable quoted-printable encoding. If you do not specify a value, the MIMEHEADER_ENCODE function will use quoted-printable encoding.
  • Specify the B encoding type to enable base-64 encoding.

Examples

The following example uses the MIMEHEADER_ENCODE function to encode a string and then uses the MIMEHEADER_DECODE function to decode the string:

edb=# SELECT UTL_ENCODE.MIMEHEADER_ENCODE('What is the date?') FROM DUAL;
      mimeheader_encode
------------------------------
 =? UTF8? Q? What is the date?? =
(1 row)

edb=# SELECT UTL_ENCODE.MIMEHEADER_DECODE('=? UTF8? Q? What is the date?? =') FROM DUAL;
 mimeheader_decode
-------------------
 What is the date?
(1 row)

QUOTED_PRINTABLE_DECODE

The QUOTED_PRINTABLE_DECODE function converts an encoded quoted-printable string into a decoded RAW string. Syntax:

QUOTED_PRINTABLE_DECODE(r IN RAW)

This function returns a RAW value.

Parameters

Parameter Description
r The r parameter contains the encoded string that will be decoded. The string is a RAW value that is encoded by the QUOTED_PRINTABLE_ENCODE function.

Examples

Note Before using this example, you must run 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. For more information, see the Postgres Core Documentation available at: http://www.enterprisedb.com/docs/en/9.3/pg/datatype-binary.html.

The following example first encodes and then decodes a string:

edb=# SELECT UTL_ENCODE.QUOTED_PRINTABLE_ENCODE('E=mc2') FROM DUAL;  quoted_printable_encode
-------------------------
 E=3Dmc2
(1 row)

edb=# SELECT UTL_ENCODE.QUOTED_PRINTABLE_DECODE('E=3Dmc2') FROM DUAL;
 quoted_printable_decode
-------------------------
 E=mc2
(1 row)

QUOTED_PRINTABLE_ENCODE

The QUOTED_PRINTABLE_ENCODE function converts and encodes a string into quoted-printable format. Syntax:

QUOTED_PRINTABLE_ENCODE(r IN RAW)

This function returns a RAW value.

Parameters

Parameter Description
r The r parameter contains the string (a RAW value) that will be encoded in a quoted-printable format.

Examples

Note Before using this example, you must run 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. For more information, see the Postgres Core Documentation available at: http://www.enterprisedb.com/docs/en/9.3/pg/datatype-binary.html.

The following example first encodes and then decodes a string:

edb=# SELECT UTL_ENCODE.QUOTED_PRINTABLE_ENCODE('E=mc2') FROM DUAL;  quoted_printable_encode
-------------------------
 E=3Dmc2
(1 row)

edb=# SELECT UTL_ENCODE.QUOTED_PRINTABLE_DECODE('E=3Dmc2') FROM DUAL;
 quoted_printable_decode
-------------------------
 E=mc2
(1 row)

TEXT_DECODE

The TEXT_DECODE function converts and decodes an encoded string into the VARCHAR2 value that was originally encoded by the TEXT_ENCODE function. Syntax:

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

This function returns a VARCHAR2 value.

Parameters

Parameter Description
buf The buf parameter contains the encoded string that will be converted into the original value encoded by the TEXT_ENCODE function.
encode_charset The encode_charset parameter specifies the character set into which the string will be converted before encoding. Default value: NULL.
encoding The encoding parameter specifies the encoding type used by the TEXT_DECODE function. You can specify one of the following two encoding types:
  • UTL_ENCODE.BASE64 specifies the Base64 encoding.
  • UTL_ENCODE.QUOTED_PRINTABLE specifies the quoted printable encoding. This is the default encoding type.

Examples

The following example uses the TEXT_ENCODE function to encode a string and then uses the TEXT_DECODE function to decode the string:

edb=# SELECT UTL_ENCODE.TEXT_ENCODE('What is the date?', 'BIG5', UTL_ENCODE.BASE64) FROM DUAL;
       text_encode
--------------------------
 V2hhdCBpcyB0aGUgZGF0ZT8=
(1 row)

edb=# SELECT UTL_ENCODE.TEXT_DECODE('V2hhdCBpcyB0aGUgZGF0ZT8=', 'BIG5', UTL_ENCODE.BASE64) FROM DUAL;
    text_decode
-------------------
 What is the date?
(1 row)

TEXT_ENCODE

The TEXT_ENCODE function converts a string into a specified character set, and then encodes the string. Syntax:

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

This function returns a VARCHAR2 value.

Parameters

Parameter Description
buf The buf parameter contains the encoded string that will be converted into the specified character set and encoded by the TEXT_ENCODE function.
encode_charset The encode_charset parameter specifies the character set into which the value will be converted before encoding. Default value: NULL.
encoding The encoding parameter specifies the encoding type used by the TEXT_ENCODE function. You can specify one of the following two encoding types:
  • UTL_ENCODE.BASE64 specifies the Base64 encoding.
  • UTL_ENCODE.QUOTED_PRINTABLE specifies the quoted printable encoding. This is the default encoding type.

Examples

The following example uses the TEXT_ENCODE function to encode a string and then uses the TEXT_DECODE function to decode the string:

edb=# SELECT UTL_ENCODE.TEXT_ENCODE('What is the date?', 'BIG5', UTL_ENCODE.BASE64) FROM DUAL;
       text_encode
--------------------------
 V2hhdCBpcyB0aGUgZGF0ZT8=
(1 row)

edb=# SELECT UTL_ENCODE.TEXT_DECODE('V2hhdCBpcyB0aGUgZGF0ZT8=', 'BIG5', UTL_ENCODE.BASE64) FROM DUAL;
    text_decode
-------------------
 What is the date?
(1 row)

UUDECODE

The UUDECODE function converts and decodes a uuencode encoded string into the RAW value that was originally encoded by the UUENCODE function. Syntax:

UUDECODE(r IN RAW)

This function returns a RAW value.

Parameter

Parameter Description
r The r parameter contains the uuencoded string that will be converted into a RAW value.

Examples

Note Before using this example, you must run 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. For more information, see the Postgres Core Documentation available at: http://www.enterprisedb.com/docs/en/9.3/pg/datatype-binary.html

The following example uses the UUENCODE function to encode a string and then uses the UUDECODE function to decode the string:

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

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

UUENCODE

The UUENCODE function converts RAW data into a uuencode formatted encoded string. Syntax:

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

This function returns a RAW value.

Parameters

Parameter Description
r The r parameter contains the RAW string that will be converted into uuencode format.
type The type parameter is an INTEGER value or constant. This constant specifies the type of uuencoded string that will be returned. Default value: 1. Table 2 lists the valid values.
filename The filename parameter is a VARCHAR2 value that specifies the file name that you want to embed in the encoded form. If you do not specify a file name, the UUENCODE function will include a filename of uuencode.txt in the encoded form.
permission The permission parameter is a VARCHAR2 value that specifies the permission mode. Default value: NULL.
Table 2. The type parameter
Value Constant
1 complete
2 header_piece
3 middle_piece
4 end_piece

Examples

Note Before using this example, you must run 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. For more information, see the Postgres Core Documentation available at: http://www.enterprisedb.com/docs/en/9.3/pg/datatype-binary.html

The following example uses the UUENCODE function to encode a string and then uses the UUDECODE function to decode the string:

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

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