The UTL_URL package provides a method to escape invalid and reserved characters within a URL.

Table 1. UTL_URL functions and procedures
Function/stored procedure Return type Description
ESCAPE(url, escape reserved chars, url_charset) VARCHAR2 Escapes any invalid and reserved characters in a URL.
UNESCAPE(url, url charset) VARCHAR2 Converts a URL to its original form.

If the call to a function includes an invalid URL, the UTL_URL package will return the BAD_URL exception.

ESCAPE

The ESCAPE function escapes invalid and reserved characters within a URL. Syntax:

ESCAPE(url VARCHAR2, escape_reserved_chars BOOLEAN, url_charset VARCHAR2)

Reserved characters are replaced with a percent sign (%), followed by the two-digit hexadecimal code of the ASCII value for the escaped character.

Parameters

Parameter Description
url url specifies the Uniform Resource Locator (URL) that UTL_URL will escape.
escape_reserved_chars escape_reserved_chars is a BOOLEAN value that instructs the ESCAPE function to escape reserved and invalid characters.
  • If escaped_reserved_chars is set to FALSE, the ESCAPE function will only escape the invalid characters in the specified URL.
  • If escape_reserved_chars is set to TRUE, the ESCAPE function will escape both the invalid characters and the reserved characters in the specified URL. By default, escape_reserved_chars is set to FALSE.

For more information about valid characters within a URL, see Table 2.

Some characters are valid in some parts of a URL, while invalid in others. For more information about rules related to invalid characters, see RFC 2396. For more information about examples of characters that are considered to be invalid in any part of a URL, see Table 3.

For more information about characters that are considered to be reserved by the ESCAPE function, see Table 4. If escape_reserved_chars is set to TRUE, the ESCAPE function will escape the reserved characters.

url_charset url_charset specifies a character set to which a given character will be converted before it is escaped. If url_charset is NULL, the character will not be converted. The default value of url_charset is ISO-8859-1.
Table 2. Valid characters
Uppercase letters A through Z Lowercase letters a through z Digits 0 through 9
Asterisk (*) Exclamation point (!) Hyphen (-)
Opening parenthesis (() Period (.) Closing parenthesis ())
Single-quote (') Tilde (~) Underscore (_)
Table 3. Invalid characters
Invalid character Escape sequence
Space ( ) %20
Curly braces ({ or }) %7b and %7d
Hash mark (#) %23
Table 4. Reserved characters
Reserved character Escape sequence
Ampersand (&) %5C
At sign (@) %25
Colon (:) %3a
Comma (,) %2c
Dollar sign ($) %24
Equal sign (=) %3d
Plus sign (+) %2b
Question mark (?) %3f
Semicolon (;) %3b
Slash (/) %2f

Examples

The following anonymous block uses the ESCAPE function to escape the spaces in the URL:

DECLARE
  result varchar2(400);
BEGIN
 result := UTL_URL.ESCAPE('http://www.example.com/Using the ESCAPE function.html');
  DBMS_OUTPUT.PUT_LINE(result);
END;

The escaped URL is:

http://www.example.com/Using%20the%20ESCAPE%20function.html

If you include a value of TRUE for the escape_reserved_chars parameter when calling the function:

DECLARE
  result varchar2(400);
BEGIN
 result := UTL_URL.ESCAPE('http://www.example.com/Using the ESCAPE function.html', TRUE);
  DBMS_OUTPUT.PUT_LINE(result);
END;

The ESCAPE function escapes the reserved characters and the invalid characters in the URL:

http%3A%2F%2Fwww.example.com%2FUsing%20the%20ESCAPE%20function.html

UNESCAPE

The UNESCAPE function removes escape characters added to a URL by the ESCAPE function, converting the URL to its original form. Syntax:

UNESCAPE(url VARCHAR2, url_charset VARCHAR2)

Parameters

Parameter Description
url url specifies the Uniform Resource Locator (URL) that UTL_URL will unescape.
url_charset After a character is unescaped, the character is assumed to be in url_charset encoding. Before the character is returned, the character will be converted from url_charset encoding to database encoding. If url_charset is NULL, the character will not be converted. The default value of url_charset is ISO-8859-1.

Examples

The following anonymous block uses the ESCAPE function to escape the blank spaces in the URL:

DECLARE
  result varchar2(400);
BEGIN
 result := UTL_URL.UNESCAPE('http://www.example.com/Using%20the%20UNESCAPE%20function.html');
  DBMS_OUTPUT.PUT_LINE(result);
END;

The unescaped URL is:

http://www.example.com/Using the UNESCAPE function.html