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=value2Etc. Etc. Etc.
The output of that split looks like this:
param1=value1Now 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 VALUEFirst up, I'll create the SQL objects (user defined types):
CREATE TYPE r_key_value AS OBJECTI could easily do this using PL/SQL tables, but I have future plans.
CREATE TYPE t_key_value AS TABLE OF r_key_value
DECLAREThis 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.
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_counter INTEGER := 0;
l_start INTEGER := 1;
l_end INTEGER := 0;
BEGINI 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.
IF SUBSTR( l_string, -1, 1 ) <> l_delimiter THEN
l_string := l_string || l_delimiter;
l_new := l_string;
p( L_NEW );
LOOPRegular 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.
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_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 );
Finally, just printing out to the console so I can see the results.
p( 'table counter: ' || l_table.COUNT );Run it and I get the following:
FOR I IN 1..l_table.COUNT LOOP
p( 'key: ' || l_table(i).key );
p( 'value: ' || l_table(i).value );
p( '' );
CJUSTICE@TESTING>@C:\TEMP\SNow 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.
table counter: 2
PL/SQL procedure successfully completed.
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?