8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
UTL_URL : Escape and unescape strings in URLs
The UTL_URL
package was introduced in Oracle 12.2 to help escape and unescape strings in URLs.
Escape
In its simplest form the ESCAPE
function escapes all illegal characters the %XX format. In this example we encrypt a URL, converting a whitespace into a "%20".
set serveroutput on define off begin dbms_output.put_line(utl_url.escape(url => 'http://oracle-base.com/my page.html')); end; / http://oracle-base.com/my%20page.html PL/SQL procedure successfully completed. SQL>
Setting the ESCAPE_RESERVED_CHARS
parameter to TRUE will escape reserved characters, as well as illegal characters. Notice how the ":" and "/" characters are also escaped in this example.
begin dbms_output.put_line(utl_url.escape( url => 'http://oracle-base.com/my page.html', escape_reserved_chars => true)); end; / http%3A%2F%2Foracle-base.com%2Fmy%20page.html PL/SQL procedure successfully completed. SQL>
In some situations it may be sensible to escape only the parameter values. In this example we use a URL with parameters, and set simple parameter values. Escaping the whole URL works fine.
declare l_param1 varchar2(10) := 'yes'; l_param2 varchar2(10) := 'no'; l_url varchar2(255); begin l_url := 'http://oracle-base.com/my-service?param1=' || l_param1 || '¶m2=' || l_param2; dbms_output.put_line(utl_url.escape(url => l_url)); end; / http://oracle-base.com/my-service?param1=yes¶m2=no PL/SQL procedure successfully completed. SQL>
This time we make the second parameter value more complex, including "&", which is a reserved character. This time the output is not correct and the "&" in the second parameter should be escaped.
declare l_param1 varchar2(10) := 'yes'; l_param2 varchar2(10) := 'yes&no'; l_url varchar2(255); begin l_url := 'http://oracle-base.com/my-service?param1=' || l_param1 || '¶m2=' || l_param2; dbms_output.put_line(utl_url.escape(url => l_url)); end; / http://oracle-base.com/my-service?param1=yes¶m2=yes&no PL/SQL procedure successfully completed. SQL>
We don't want to escape all reserved characters, so we need to escape the parameter values separately to get the correct result.
declare l_param1 varchar2(10) := 'yes'; l_param2 varchar2(10) := 'yes&no'; l_url varchar2(255); begin l_url := 'http://oracle-base.com/my-service?param1=' || utl_url.escape(l_param1, true) || '¶m2=' || utl_url.escape(l_param2, true); dbms_output.put_line(l_url); end; / http://oracle-base.com/my-service?param1=yes¶m2=yes%26no PL/SQL procedure successfully completed. SQL>
Unescape
The UNESCAPE
function does the reverse of the ESCAPE
function. In this example we unescape all the URLs we previously escaped.
begin dbms_output.put_line(utl_url.unescape('http://oracle-base.com/my%20page.html')); dbms_output.put_line(utl_url.unescape('http%3A%2F%2Foracle-base.com%2Fmy%20page.html')); dbms_output.put_line(utl_url.unescape('http://oracle-base.com/my-service?param1=yes¶m2=no')); dbms_output.put_line(utl_url.unescape('http://oracle-base.com/my-service?param1=yes¶m2=yes&no')); dbms_output.put_line(utl_url.unescape('http://oracle-base.com/my-service?param1=yes¶m2=yes%26no')); end; / http://oracle-base.com/my page.html http://oracle-base.com/my page.html http://oracle-base.com/my-service?param1=yes¶m2=no http://oracle-base.com/my-service?param1=yes¶m2=yes&no http://oracle-base.com/my-service?param1=yes¶m2=yes&no PL/SQL procedure successfully completed. SQL>
In a real situation, you would probably have a call into your code containing escaped parameter values, which you subsequently unescape.
create or replace procedure my_proc ( p_param1 IN VARCHAR2, p_param2 IN VARCHAR2 ) as l_param1 varchar2(32767); l_param2 varchar2(32767); begin l_param1 := utl_url.unescape(p_param1); l_param2 := utl_url.unescape(p_param2); -- Do something with the parameter values. end; /
For more information see:
Hope this helps. Regards Tim...