Encoding and decoding capabilities-UTL_ENCODE - PolarDB - Alibaba Cloud - PolarDB

Functions in this package

Function	Returns	Description
BASE64_ENCODE	`RAW`	Encodes a RAW string or large object to Base64
BASE64_DECODE	`RAW`	Decodes a Base64-encoded RAW string
MIMEHEADER_ENCODE	`VARCHAR2`	Encodes a string into MIME header format
MIMEHEADER_DECODE	`VARCHAR2`	Decodes a MIME header-encoded string
QUOTED_PRINTABLE_ENCODE	`RAW`	Encodes a RAW string in quoted-printable format
QUOTED_PRINTABLE_DECODE	`RAW`	Decodes a quoted-printable-encoded RAW string
TEXT_ENCODE	`VARCHAR2`	Converts a string to a specified character set and encodes it
TEXT_DECODE	`VARCHAR2`	Decodes a string encoded by TEXT_ENCODE
UUENCODE	`RAW`	Encodes a RAW string in uuencode format
UUDECODE	`RAW`	Decodes a uuencoded RAW string

Prerequisites

Before running examples that return RAW or BYTEA values, run the following command to display results in readable form:

SET bytea_output = escape;

For more information, see PostgreSQL core file.

BASE64_ENCODE

Encodes a RAW string or a large object to a Base64-encoded string. Two syntax forms are supported:

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

Returns a RAW value or an object ID.

Parameters

Parameter	Description
`r`	The RAW string to encode to Base64.
`loid`	The ID of a large object to encode to Base64.

Example

Encode the string abc to Base64, then decode it back to verify the round trip:

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

Output:

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

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

Output:

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

BASE64_DECODE

Decodes a Base64-encoded string to its original RAW value.

BASE64_DECODE(r IN RAW)

Returns a RAW value.

Parameters

Parameter	Description
`r`	A Base64-encoded string produced by `BASE64_ENCODE`.

Example

See the round-trip example in BASE64_ENCODE.

MIMEHEADER_ENCODE

Encodes a string into MIME header format. The output follows this structure:

=?<charset>?<encoding>?<encoded text>?=

For example: =?UTF8?Q?What is the date??=

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

Returns a VARCHAR2 value.

Parameters

Parameter	Description
`buf`	The `VARCHAR2` string to format and encode.
`encode_charset`	The character set to convert the input string to before encoding. Default: `NULL`.
`encoding`	The encoding method. Valid values: `Q` (quoted-printable) or `B` (Base64). Default: `Q`.

Example

Encode a string to MIME header format, then decode it back:

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

Output:

      mimeheader_encode
------------------------------
 =?UTF8?Q?What is the date??=
(1 row)

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

Output:

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

MIMEHEADER_DECODE

Decodes a string that was encoded by MIMEHEADER_ENCODE.

MIMEHEADER_DECODE(buf IN VARCHAR2)

Returns a VARCHAR2 value.

Parameters

Parameter	Description
`buf`	A MIME header-encoded string produced by `MIMEHEADER_ENCODE`.

Example

See the round-trip example in MIMEHEADER_ENCODE.

QUOTED_PRINTABLE_ENCODE

Encodes a RAW string in quoted-printable format.

QUOTED_PRINTABLE_ENCODE(r IN RAW)

Returns a RAW value.

Parameters

Parameter	Description
`r`	The RAW string to encode in quoted-printable format.

Example

Encode a string in quoted-printable format, then decode it back:

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

Output:

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

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

Output:

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

QUOTED_PRINTABLE_DECODE

Decodes a quoted-printable-encoded string to its original RAW value.

QUOTED_PRINTABLE_DECODE(r IN RAW)

Returns a RAW value.

Parameters

Parameter	Description
`r`	A quoted-printable-encoded RAW string produced by `QUOTED_PRINTABLE_ENCODE`.

Example

See the round-trip example in QUOTED_PRINTABLE_ENCODE.

TEXT_ENCODE

Converts a string to the specified character set and encodes the result.

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

Returns a VARCHAR2 value.

Parameters

Parameter	Description
`buf`	The string to encode.
`encode_charset`	The character set to convert the string to before encoding. Default: `NULL`.
`encoding`	The encoding method. Valid values: `Q` (quoted-printable) or `B` (Base64). Default: `Q`.

Example

Encode a string using the BIG5 character set and Base64 encoding, then decode it back:

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

Output:

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

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

Output:

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

TEXT_DECODE

Decodes a string that was encoded by TEXT_ENCODE.

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

Returns a VARCHAR2 value.

Parameters

Parameter	Description
`buf`	A string encoded by `TEXT_ENCODE`.
`encode_charset`	The character set used for conversion. Default: `NULL`.
`encoding`	The encoding method used by `TEXT_ENCODE`. Valid values: `UTL_ENCODE.QUOTED_PRINTABLE` or `UTL_ENCODE.BASE64`. Default: `UTL_ENCODE.QUOTED_PRINTABLE`.

Example

See the round-trip example in TEXT_ENCODE.

UUENCODE

Encodes a RAW string in uuencode format.

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

Returns a RAW value.

Parameters

Parameter	Description
`r`	The RAW string to encode.
`type`	The type of uuencoded output to return. Default: `1`. See the following table for valid values.
`filename`	The file name to embed in the uuencoded string. If not specified, defaults to `uuencode.txt`.
`permission`	The file permission mode to embed in the uuencoded string. Default: `NULL`.

Type values

Value	Constant
`1`	`complete`
`2`	`header_piece`
`3`	`middle_piece`
`4`	`end_piece`

Example

Encode a string in uuencode format, then decode it back:

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

Output:

                              uuencode
--------------------------------------------------------------------
 begin 0 uuencode.txt\01215VAA="!I<R!T:&4@9&%T93\\`\012`\012end\012
(1 row)

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

Output:

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

UUDECODE

Decodes a uuencoded string to its original RAW value.

UUDECODE(r IN RAW)

Returns a RAW value.

Parameters

Parameter	Description
`r`	A uuencoded RAW string produced by `UUENCODE`.

Example

See the round-trip example in UUENCODE.

polar_enable_base64_decode

When polar_enable_base64_decode is set to on (the default), BASE64_DECODE automatically identifies input strings in an invalid Base64 format and decodes the valid leading portion of the string.

Note

By default, polar_enable_base64_decode is set to on. To change this setting, see Configure cluster parameters.

When polar_enable_base64_decode is on

BASE64_DECODE decodes the valid leading portion of an invalid Base64 string:

SELECT utl_encode.base64_decode(utl_raw.cast_to_raw('NjMzNDgwN==gNjMzNjMz'));

Output:

base64_decode
---------------
 633480
(1 row)

When polar_enable_base64_decode is off

BASE64_DECODE returns an error for any invalid Base64 string:

SELECT utl_encode.base64_decode(utl_raw.cast_to_raw('NjMzNDgwN==gNjMzNjMz'));

Output:

ERROR:  unexpected "=" while decoding base64 sequence