URLs may contain only certain characters. Spaces, accented letters, and characters with special meaning such as & and ? must be written as % codes when they are part of a value. UTL_URL.ESCAPE does this encoding and UNESCAPE reverses it, for building web service calls and links in PL/SQL.
Code for This Guide
The main examples are in the examples/pkg-raw-text folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
utl_url.escape(url, escape_reserved_chars => false, url_charset => null) utl_url.unescape(url, url_charset => null)
URL_CHARSET is the character set for the bytes of non-ASCII characters; use AL32UTF8 for the web.
Escape and Unescape
Example:
begin
dbms_output.put_line(utl_url.escape('https://nimbus.example/fares?seat=aisle 12'));
dbms_output.put_line(utl_url.escape('Zürich & Genève', true, 'AL32UTF8'));
dbms_output.put_line(utl_url.unescape('Z%C3%BCrich%20%26%20Gen%C3%A8ve', 'AL32UTF8'));
end;
/Output:
https://nimbus.example/fares?seat=aisle%2012 Z%C3%BCrich%20%26%20Gen%C3%A8ve Zürich & Genève PL/SQL procedure successfully completed.
- A whole URL keeps its : / ? = characters, and only the space becomes %20.
- With escape_reserved_chars TRUE, the & in a value becomes %26, and ü and è become their UTF-8 bytes, %C3%BC and %C3%A8.
- UNESCAPE turns the codes back into text.
Reserved Characters
Example:
begin
-- false (default): reserved characters such as / ? = & stay as they are
dbms_output.put_line(utl_url.escape('/fares?from=DXB&to=LHR'));
-- true: everything but letters, digits and - _ . ! ~ * ' ( ) is escaped
dbms_output.put_line(utl_url.escape('/fares?from=DXB&to=LHR', true));
end;
/Output:
/fares?from=DXB&to=LHR %2Ffares%3Ffrom%3DDXB%26to%3DLHR PL/SQL procedure successfully completed.
By default the path and query keep their structure. With TRUE, every / ? = and & is escaped, which is right for a single value placed in a query string, and wrong for a whole URL.
Things to Know
- Escape each parameter value separately with TRUE, then join them into the URL with unescaped ? = and &.
- Pass AL32UTF8 as the character set; without it the database character set is used.
- Escaping is for URLs only; text in HTML pages needs UTL_I18N.ESCAPE_REFERENCE.
Related Guides
Conclusion
UTL_URL.ESCAPE encodes text for URLs and UNESCAPE decodes it. Escape whole URLs with the default, each query value with escape_reserved_chars TRUE, and always name AL32UTF8 for non-ASCII text.
See my articles more often when you search Google.
