PL/SQL: Split Key-Value Pairs

In a continuation from a previous post, PL/SQL: Split URL Parameters, I give you the key/value splitting! (cheese, I know...I'm bored).

In that previous post, I was taking a URL string and splitting it based on the delimiter, which is typically the ampersand &. In ApEx it is the colon :. I'd take a wild guess and say there are others, but I'm not going to look them up. An example string looks like this:

param1=value1¶m2=value2

Etc. Etc. Etc.

The output of that split looks like this:

param1=value1
param2=value2

Now you need that string parsed. Instead of just a string being returned however, you'd like to know the name of the parameter as well, thus key/value. Desired format looks like this:

KEY         VALUE
----------- -----------
param1      value1
param2      value2

First up, I'll create the SQL objects (user defined types):

CREATE TYPE r_key_value AS OBJECT
(
  key VARCHAR2(100),
  value VARCHAR2(100)
)
/

CREATE TYPE t_key_value AS TABLE OF r_key_value
/

I could easily do this using PL/SQL tables, but I have future plans.

DECLARE
  l_string VARCHAR2(1000) := 'param1=value1¶m2=value2';
  l_table T_KEY_VALUE := T_KEY_VALUE();
  l_delimiter VARCHAR2(5) := '&';
  l_delimiter_length INTEGER := LENGTH( l_delimiter );
  l_key VARCHAR2(100);
  l_value VARCHAR2(100);
  l_keyvalue VARCHAR2(200);
  l_counter INTEGER := 0;
  l_new VARCHAR2(4000);
  l_start INTEGER := 1;
  l_end INTEGER := 0;

This will eventually turn into a Function, but I'm just doing some smoke testing now to get the logic right. More extensive testing will be performed in the future with SQLUnit.

BEGIN
  IF SUBSTR( l_string, -1, 1 ) <> l_delimiter THEN
    l_string := l_string || l_delimiter;
  END IF;

  l_new := l_string;
  p( L_NEW );

I still haven't figured out the best way to grab the last token without appending the delimiter onto the end. It feels like a kludge, but for now, it works.

  LOOP
    l_counter := l_counter + 1;
    l_end := INSTR( l_new, l_delimiter, 1 );
    l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
    EXIT WHEN l_keyvalue IS NULL;
    
    l_table.EXTEND(1);
    l_key := SUBSTR( l_keyvalue, 1, INSTR( l_keyvalue, '=' ) - 1 );
    l_value := SUBSTR( l_keyvalue, INSTR( l_keyvalue, '=' ) + 1 );
    l_table(l_counter) := R_KEY_VALUE( l_key, l_value );
    l_start := l_start + ( l_end + ( l_delimiter_length - 1 ) );
    l_new := SUBSTR( l_string, l_start );
  END LOOP;

Regular expressions would be the best fit here. Until I learn them (yes, I hear you, "Isn't this the perfect opportunity?"), I'm going to do it the hard way.

Finally, just printing out to the console so I can see the results.

  p( 'table counter:  ' || l_table.COUNT );
  FOR I IN 1..l_table.COUNT LOOP
    p( 'key:    ' || l_table(i).key );
    p( 'value:  ' || l_table(i).value );
    p( '' );
  END LOOP;

Run it and I get the following:

CJUSTICE@TESTING>@C:\TEMP\S
table counter:  2
key:    param1
value:  value1
key:    param2
value:  value2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

Now I just need to wrap this up into package form and I'm all done. That will be another post with the unit tests provided.

Update
So after rereading this post, I realized that I just confused it with the previous post on splitting URL strings. Probably the best solution would be to combine these 2 functions, or at least pipe out the key/value pairs from the main function (previous post). Did I catch a niner in there?