8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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 || '&param2=' || l_param2;
  dbms_output.put_line(utl_url.escape(url => l_url));
end;
/
http://oracle-base.com/my-service?param1=yes&param2=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 || '&param2=' || l_param2;
  dbms_output.put_line(utl_url.escape(url => l_url));
end;
/
http://oracle-base.com/my-service?param1=yes&param2=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) ||
           '&param2=' || utl_url.escape(l_param2, true);
  dbms_output.put_line(l_url);
end;
/
http://oracle-base.com/my-service?param1=yes&param2=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&param2=no'));
  dbms_output.put_line(utl_url.unescape('http://oracle-base.com/my-service?param1=yes&param2=yes&no'));
  dbms_output.put_line(utl_url.unescape('http://oracle-base.com/my-service?param1=yes&param2=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&param2=no
http://oracle-base.com/my-service?param1=yes&param2=yes&no
http://oracle-base.com/my-service?param1=yes&param2=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...

Back to the Top.