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

Table 1. HTTP functions and stored procedures
Function or stored procedure Return value type Description
ESCAPE(url, escape reserved chars, url_charset) VARCHAR2 The ESCAPE function escapes invalid and reserved characters within a URL.
UNESCAPE(url, url charset) VARCHAR2 The UNESCAPE function converts a URL to its original form.
Note If the call to a function includes an invalid URL, the UTL_URL package returns the BAD_URL exception.

ESCAPE

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

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 Specifies the URL that is to be converted by using the ESCAPE function.
escape reserved chars Specifies whether the ESCAPE function converts reserved and invalid characters. Data type: BOOLEAN. Default value: FALSE.
  • If escape_reserved_chars is set to FALSE, the ESCAPE function coverts only invalid characters in the specified URL.
  • If escape_reserved_chars is set to TRUE, the ESCAPE function coverts reserved and invalid characters in the specified URL.

Valid characters in a URL include letters, digits, and special characters such as asterisks (*), exclamation points (!), hyphens (-), parenthesis (), periods (.), apostrophes ('), tildes (~), and underscores (_).

Some characters contained in some parts of a URL are considered to be valid, whereas the characters in other parts are considered to be invalid. For more information, see RFC 2396. For more information about examples of characters that are considered to be invalid in a URL, see Table 2.

For more information about reserved characters that can be used in the ESCAPE function, see Table 3. When escape_reserved_chars is set to TRUE, the ESCAPE function converts reserved characters.

url_charset Specifies the character set to which the provided character is to be converted before it is escaped. By default, url_charset is set to ISO-8859-1. When url_charset is set to NULL, the character is not replaced.
Table 2. Invalid characters
Invalid character Escape sequence
a blank space ( ) %20
curly braces ({ or }) %7b and %7d
hash mark (#) %23
Table 3. 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
semi-colon (;) %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;

An example of the URL after conversion:

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

When you call the ESCAPE function, if you set escape_reserved_chars to TRUE, the ESCAPE function converts the reserved and invalid characters in the URL.

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;
A similar output is displayed:
http%3A%2F%2Fwww.example.com%2FUsing%20the%20ESCAPE%20function.html

UNESCAPE

The UNESCAPE function is used to remove the converted characters added to the URL by using the ESCAPE function to ensure that the URL is in the original format. Syntax:

UNESCAPE(url VARCHAR2, url_charset VARCHAR2)

Parameter

Parameter Description
url Specifies the URL that the UNESCAPE function is to convert.
url_charset After a character is converted, assume that the character is encoded by using url_charset, the url_charset-encoded character is converted to a character encoded by database encoding before the converted character is returned to the original value. By default, url charset is set to ISO-8 85 9-1. When url_charset is set to NULL, the character is not converted.

Examples

The following anonymous block uses the ESCAPE function to escape the 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;

An example of the URL after conversion:

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