, , - PolarDB - Alibaba Cloud Documentation Center

You can use the UTL_URL package to process and manage Uniform Resource Locators (URLs). This topic describes how to use the UTL_URL package to encode and decode URLs.

Prerequisites

Your PolarDB for PostgreSQL (Compatible with Oracle) cluster runs the following database engine version:

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 (revision version 2.0.14.27.0 or later)

Note

You can execute the following statement to query the database engine revision version of a PolarDB for PostgreSQL (Compatible with Oracle) cluster:

SHOW polar_version;

Functions in the UTL_URL package

Function	Description
ESCAPE Function	Encodes a URL by escaping special characters within the URL. Each special character is replaced by a percent sign (%) followed by two hexadecimal digits that represent the ASCII code of the character.
UNESCAPE Function	Decodes a URL to its original format.

ESCAPE Function

The function converts each special character in a URL into a percent sign (%) followed by two hexadecimal digits that represent the ASCII code of the character.

Syntax

FUNCTION escape(url                     in varchar2,
                escape_reserved_chars   in boolean default false,
                url_charset             in varchar2 default null)
RETURN varchar2;

Parameters

Parameter	Description
`url`	The URL that you want to encode.
`escape_reserved_chars`	Specifies whether to encode the reserved characters in the URL. Default value: `false`. Note The characters in a URL are divided into the following types: Non-reserved characters: `A-Z a-z 0-9 - _ . ! ~ * ' ()` Reserved characters: `; / ? : @ & = + $ , []`
`url_charset`	The character set of the URL. Default value: NULL.

Example

Encode a URL that contains special characters.

DECLARE
    res varchar2;
BEGIN
    res := utl_url.escape(url => 'https://www.aliyun.com/数据库',
                          escape_reserved_chars => false,
                          url_charset => 'utf8');
    dbms_output.put_line(res);
END;

Sample result:

https://www.aliyun.com/%E6%95%B0%E6%8D%AE%E5%BA%93

UNESCAPE Function

The function decodes an encoded URL to its original format.

Syntax

FUNCTION unescape(url           in varchar2,
                  url_charset   in varchar2 default null)
RETURN varchar2;

Parameters

Parameter	Description
`url`	The URL that you want to decode.
`url_charset`	The character set of the URL. Default value: NULL.

Example

Encode a URL that contains special characters and then decode the URL.

DECLARE
    res varchar2;
BEGIN
    res := utl_url.escape(url => 'https://www.aliyun.com/数据库',
                          escape_reserved_chars => false,
                          url_charset => 'utf8');
    dbms_output.put_line('URL after escape:' || res);
    res := utl_url.unescape(url => res,
                            url_charset => 'utf8');
    dbms_output.put_line('URL after unescape:' || res);
END;

Sample result:

URL after escape:https://www.aliyun.com/%E6%95%B0%E6%8D%AE%E5%BA%93
URL after unescape:https://www.aliyun.com/数据库