How to use the UTL_ENCODE package to encode and decode data - PolarDB

The UTL_ENCODE package provides methods to encode and decode data.

BASE64_ENCODE

The BASE64_ENCODE function converts a RAW or TEXT string to a Base64 format string. The BASE64_ENCODE function has the following two syntaxes:

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

This function returns a value in RAW format or an OID.

Parameters

Parameter Name	Description
r	Specifies the RAW string to convert to Base64 format.
loid	Specifies the object ID of the large object to convert to Base64 format.

Example

Note

Before you run the following example, execute this command to instruct the server to ignore non-printable characters and display BYTEA or RAW values in a readable format on the screen:

SET bytea_output = escape;

For more information, see the Postgres core documentation.

You can use the BASE64_ENCODE function to convert a string to Base64 format. The following is an example:

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

The following result is returned:

 base64_encode
---------------
 YWJj
(1 row)

BASE64_DECODE

The BASE64_DECODE function decodes a Base64 format string back to the original value that was encoded by the BASE64_ENCODE function. The syntax is as follows:

BASE64_DECODE(r IN RAW)

This function returns a value in RAW format.

Parameters

Parameter Name	Description
r	The Base64 format string encoded by the BASE64_ENCODE function.

Example

Note

Before you run the following example, execute this command to instruct the server to ignore non-printable characters and display BYTEA or RAW values in a readable format on the screen:

SET bytea_output = escape;

For more information, see the Postgres core documentation.

You can use the BASE64_DECODE function to convert a Base64 format string to RAW format:

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

The following result is returned:

 base64_decode
---------------
 abc
(1 row)

MIMEHEADER_ENCODE

The MIMEHEADER_ENCODE function converts a string to Multipurpose Internet Mail Extensions (MIME) header format and then encodes the string. The syntax is as follows:

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

This function returns a value in VARCHAR2 format.

Parameters

Parameter Name	Description
buf	The string to format and encode. This string is a VARCHAR2 value.
encode_charset	Specifies the character set to which the string is converted before formatting and encoding. The default is NULL.
encoding	Specifies the encoding type to use when encoding the string. You can specify one of the following two encoding types: Specify Q to enable the Quoted-printable encoding type. Specify B to enable the Base-64 encoding type. Note The default is Quoted-printable encoding.

Example

You can use the MIMEHEADER_ENCODE function to encode a string. The following is an example:

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

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 syntax is as follows:

MIMEHEADER_DECODE(buf IN VARCHAR2)

This function returns a value in VARCHAR2 format.

Parameters

Parameter Name	Description
buf	The value to decode, which was encoded by the MIMEHEADER_ENCODE function.

Example

You can use the MIMEHEADER_DECODE function to decode a string that was encoded by the MIMEHEADER_ENCODE function. The following is an example:

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

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 into Quoted-printable format. The syntax is as follows:

QUOTED_PRINTABLE_ENCODE(r IN RAW)

This function returns a value in RAW format.

Parameters

Parameter name	Description
r	The string to encode into Quoted-printable format.

Example

Note

Before you run the following example, execute this command to instruct the server to ignore non-printable characters and display BYTEA or RAW values in a readable format on the screen:

SET bytea_output = escape;

For more information, see the Postgres core documentation.

You can use the QUOTED_PRINTABLE_ENCODE function to encode a string. The following is an example:

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

The following result is returned:

 quoted_printable_encode
-------------------------
 E=3Dmc2
(1 row)

QUOTED_PRINTABLE_DECODE

The QUOTED_PRINTABLE_DECODE function converts a string encoded in the Quoted-printable format to a decoded RAW string. The syntax is as follows:

QUOTED_PRINTABLE_DECODE(r IN RAW)

This function returns a value in RAW format.

Parameters

Parameter name	Description
r	The string encoded by the QUOTED_PRINTABLE_ENCODE function. This string is a RAW value.

Example

Note

Before you run the following example, execute this command to instruct the server to ignore non-printable characters and display BYTEA or RAW values in a readable format on the screen:

SET bytea_output = escape;

For more information, see the Postgres core documentation.

You can use the QUOTED_PRINTABLE_DECODE function to decode a string. The following is an example:

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

The following result is returned:

 quoted_printable_decode
-------------------------
 E=mc2
(1 row)

TEXT_ENCODE

The TEXT_ENCODE function converts a string to a specified character set and then encodes the string. The syntax is as follows:

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

This function returns a value in VARCHAR2 format.

Parameters

Parameter name	Description
buf	The string to encode.
encode_charset	Specifies the character set for the conversion. The default is NULL.
encoding	Specifies the encoding type used by TEXT_ENCODE. You can specify one of the following two encoding types: Specify Q to enable the Quoted-printable encoding type. Specify B to enable the Base-64 encoding type. Note The default is Quoted-printable encoding.

Example

You can use the TEXT_ENCODE function to encode a string. The following is an example:

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

The following result is returned:

       text_encode
--------------------------
 V2hhdCBpcyB0aGUgZGF0ZT8=
(1 row)

TEXT_DECODE

The TEXT_DECODE function decodes a string that was encoded by the TEXT_ENCODE function into VARCHAR2 format. The syntax is as follows:

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

This function returns a value in VARCHAR2 format.

Parameters

Parameter Name	Description
buf	The string encoded by the TEXT_ENCODE function.
encode charset	Specifies the character set for the conversion. The default is NULL.
encoding	Specifies the encoding type used by TEXT_DECODE. You can specify one of the following two encoding types: UTL_ENCODE.QUOTED_PRINTABLE specifies Quoted-printable encoding. UTL_ENCODE.BASE64 specifies Base-64 encoding. Note The default is Quoted-printable encoding.

Example

You can use the TEXT_DECODE function to decode a string. The following is an example:

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

The following result is returned:

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

UUENCODE

The UUENCODE function converts RAW data to a UUENCODE format string. The syntax is as follows:

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

This function returns a value in RAW format.

Parameters

Parameter name	Description
r	The string to convert to UUENCODE format.
type	Specifies the type of the UUENCODED string to return. The default is 1. For more information, see Types.
filename	Specifies the file name you want to embed in the encoded form. If you do not specify a file name, UUENCODE includes the file name `uuencode.txt` in the encoded form.
permission	Specifies the permission pattern. The default is NULL.

Table 1. Types

Value	Constant
1	complete
2	header_piece
3	middle_piece
4	end_piece

Example

Note

Before you run the following example, execute this command to instruct the server to ignore non-printable characters and display BYTEA or RAW values in a readable format on the screen:

SET bytea_output = escape;

For more information, see the Postgres core documentation.

You can use the UUENCODE function to encode a string. The following is an example:

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

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 decodes a UUENCODE string back to the original RAW value that was encoded by the UUENCODE function. The syntax is as follows:

UUDECODE(r IN RAW)

This function returns a value in RAW format.

Parameters

Parameter Name	Description
r	Contains the UUENCODED string to convert to a RAW value.

Example

Note

Before you run the following example, execute this command to instruct the server to ignore non-printable characters and display BYTEA or RAW values in a readable format on the screen:

SET bytea_output = escape;

For more information, see the Postgres core documentation.

You can use the UUDECODE function to decode a string. The following is an example:

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

The following result is returned:

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

polar_enable_base64_decode

If the polar_enable_base64_decode parameter is set to on, the BASE64_DECODE function can automatically detect incorrectly formatted encoded strings and decode the valid part of the string. For more information, see BASE64_DECODE.

Note

The polar_enable_base64_decode parameter is set to on by default. You can modify parameters in the console. For more information, see Set cluster parameters.

If the polar_enable_base64_decode parameter is set to on, you can pass an incorrectly formatted encoded string. The following is an example:
```
SELECT utl_encode.base64_decode(utl_raw.cast_to_raw('NjMzNDgwN==gNjMzNjMz'));
```
The BASE64_DECODE function decodes the valid part of the string. The following example shows a normal return:
```
 base64_decode 
---------------
 633480
(1 row)
```
If the polar_enable_base64_decode parameter is set to off, you can pass an incorrectly formatted encoded string. The following is an example:
```
SELECT utl_encode.base64_decode(utl_raw.cast_to_raw('NjMzNDgwN==gNjMzNjMz'));
```
The BASE64_DECODE function cannot decode the incorrectly formatted string. The following example shows an error return:
```
ERROR:  unexpected "=" while decoding base64 sequence
```