The reason that I'd like a PL/SQL cookbook is that there are large sets of problems that routinely need to be solved in PL/SQL, but the language is so low level (though they just added some regex support in 10g; bravo!) that doing these routine tasks and making sure they're correctly implemented can be difficult and tedious. This is especially true when it's a programmer from a different language who's used to higher levels of abstraction (like, for example, the good folks who author CPAN modules provide)--it'd be well worth my $70 to make sure that I never had to deal with a problem like, say, unescaping a string.
For that's the problem I recently had to solve. Essentially, we have a string that looks like this: yellow,apple
. This string represents two values, which need to be put in different places by splitting them up into 'yellow' and 'apple'. All well and good until the possiblity of embedded commas arises, for it's possible that the desired end values were 'yellow,blue' and 'apple,banana'. The answer, of course, is to escape the commas on the way in (turning the second input into something like this: yellow:,blue,apple:,banana
, and when processing to unescape those special characters (both the comma and the escape character, which in the example is the colon). That's what these three functions do. They take a string like the above examples and parse it into a table, to be iterated over at your leisure.
/* ------------------- function splitit ------------------*/
FUNCTION splitit(p_str VARCHAR2, p_del VARCHAR2 := ',',p_idx PLS_INTEGER, p_esc VARCHAR2
:= ':')
RETURN INTEGER
IS
l_idx PLS_INTEGER;
l_chars_before VARCHAR2(32767);
l_escape_char VARCHAR2(1) := p_esc;
l_chars_before_count PLS_INTEGER := 0;
BEGIN
<>
LOOP
l_idx := instr(p_str,p_del, p_idx);
IF l_idx > 0 then
WHILE substr(p_str, l_idx-l_chars_before_count-1, 1) = l_escape_char LOOP
l_chars_before_count := l_chars_before_count +1;
END LOOP;
IF mod(l_chars_before_count, 2) = 0 THEN
-- if chars_before_count is even, then we're at a segment boundary
RETURN l_idx;
ELSE
-- if odd, then we're at an escaped delimiter, want to move past
RETURN splitit(p_str, p_del, l_idx+1, p_esc);
END IF;
l_chars_before_count := 0;
ELSE
RETURN l_idx;
EXIT outer;
END IF;
END LOOP;
END splitit;
/* ------------------- function splitit ------------------*/
/* ------------------- function unescape ------------------*/
FUNCTION unescape(p_str VARCHAR2, p_del VARCHAR2 := ',', p_esc VARCHAR2 := ':')
RETURN VARCHAR2
IS
l_str VARCHAR2(32767);
BEGIN
l_str := replace(p_str, p_esc||p_del, p_del);
l_str := replace(l_str, p_esc||p_esc, p_esc);
RETURN l_str;
END unescape;
/* ------------------- function unescape ------------------*/
/* ------------------- function split ------------------*/
FUNCTION split(p_list VARCHAR2, p_del VARCHAR2 := ',')
RETURN split_tbl
IS
l_idx PLS_INTEGER;
split_idx PLS_INTEGER := 0;
l_list VARCHAR2(32767) := p_list;
l_chars_before VARCHAR2(32767);
l_escape_char VARCHAR2(1) := ':';
l_array split_tbl := split_tbl('','','','','','','','','','');
BEGIN
l_list := p_list;
LOOP
split_idx := split_idx + 1;
IF split_idx > 10 then
EXIT;
END IF;
l_idx := splitit(l_list, p_del, 1, l_escape_char);
IF l_idx > 0 then
l_array(split_idx) := unescape(substr(l_list,1,l_idx-1), p_del,
l_escape_char);
l_list := substr(l_list,l_idx+length(p_del));
ELSE
l_array(split_idx) := l_list;
EXIT;
END IF;
END LOOP;
RETURN l_array;
END split;
/* ------------------- function split ------------------*/
/* in the header file, split_tbl is defined */
TYPE split_tbl IS TABLE of varchar2(32767)
Not all of this code is mine--I built on a solution from a colleague. But I hope this saves one other person from the afternoon I just endured. And if you are a PL/SQL expert and care to critique this solution, please feel free.
Posted by moore at November 12, 2005 09:46 AM