Tuesday, June 23, 2009

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?

1 comment:

Anonymous said...

Check out the apex util functions string_to_table and table_to_string