One project every month - making stuff better ...

Parsing URLs from plsql

Just the other day I needed to parse a URL, and could not find one that acted like I was used to in python. So tired of always missing out in plsql, I want to do something about it. So yesterday I decided to create a package with different URL utilities that I need from time to time. The package is called url_ninja. It can split an URL into the different components (scheme, authority, path, parameters, query, fragments) and back again to a complete URL. It can parse an array into a query string and it can parse a query string into an array.

To install the package simply download the files form my github repository and install them in the database. Below is a simple piece of plsql that demonstrates all the capabilities of the package as of now:

set define off
set serverout on


	-- Array to test building a query string
	query_list url_ninja.parm_list;
	-- The URL we are going to demonstrate with (including basic authentication for demo purposes)
	url varchar2(4000) := '';
	-- The parsed record that will be returned when we par seht URL
	parsed_url url_ninja.url_rec;
	-- Add parameters to the array that will build our query string
	query_list('x1') := '123';
	query_list('x2') := '234';
	-- Build the query string from the array
	url := url_ninja.build_get_url(url, query_list);
	-- Add a fragment to demonstrate fragments
	url := url || '#Afragment';
	-- Output the full URL before we parse it
	dbms_output.put_line('Full URL: ' || url);
	-- Parse the URL into individual components
	parsed_url := url_ninja.urlparse(url);
	-- Output all the parsed components one by one
	dbms_output.put_line('Scheme: ' || parsed_url.scheme);
	dbms_output.put_line('Authority: ' || parsed_url.authority);
	dbms_output.put_line('Path: ' || parsed_url.path);
	dbms_output.put_line('Parameters: ' || parsed_url.parameters);
	dbms_output.put_line('Query: ' || parsed_url.query);
	dbms_output.put_line('Fragment: ' || parsed_url.fragment);
	dbms_output.put_line('Hostname: ' || parsed_url.hostname);
	dbms_output.put_line('Port: ' || parsed_url.port);
	dbms_output.put_line('Username: ' || parsed_url.username);
	-- Re-assemble the URL from the individual components
	url := url_ninja.urlunparse(parsed_url);
	-- Output the re-assembled URL
	dbms_output.put_line('Full URL: ' || url);

Next up is adding more Schemes (mailto, file, ftp, imap etc), and adding the following methods:

  • List supported schemes - Table function that lists all supported schemes
  • Join - Joining path elements to a URL
  • Extract - A method to extract URI's from a text

If you have any suggestions, to other python, ruby, nodejs or whatever packages, that you want ported to plsql, send me suggestions on email: morten(at)

Tagged in : UTL_URL