{"id":292,"date":"2005-11-12T09:46:12","date_gmt":"2005-11-12T15:46:12","guid":{"rendered":"http:\/\/www.mooreds.com\/wordpress\/?p=292"},"modified":"2007-01-07T23:03:48","modified_gmt":"2007-01-08T05:03:48","slug":"unescaping-a-string-with-plsql","status":"publish","type":"post","link":"https:\/\/www.mooreds.com\/wordpress\/archives\/292","title":{"rendered":"unescaping a string with PL\/SQL"},"content":{"rendered":"

I’ve written about PL\/SQL before<\/a>, but I’ve recently started working on a project that uses it heavily. Given the amount of code written for Oracle databases, I’m rather suprised that there’s not a PL\/SQL Cookbook, where, like the Perl Cookbook<\/a> and the Java Cookbook<\/a> (more cookbooks from O’Reilly are listed here<\/a>). There is an Oracle Cookbook<\/a>, but based on a quick scan of Amazon, it’s is focused, as you’d expect, more on the database design than on PL\/SQL programming. (Interestingly, there is a Oracle+PHP cookbook<\/a>, and a PL\/SQL sample code page<\/a> but neither of those is quite what I’m looking for.)<\/p>\n

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<\/a>; 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<\/a> 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.<\/p>\n

For that’s the problem I recently had to solve. Essentially, we have a string that looks like this: yellow,apple<\/code>. 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<\/code>, 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.<\/p>\n

<\/code><\/p>\n
\/* ------------------- function splitit ------------------*\/\r\nFUNCTION splitit(p_str VARCHAR2, p_del VARCHAR2  := ',',p_idx PLS_INTEGER, p_esc VARCHAR2\r\n\r\n:= ':')\r\nRETURN INTEGER\r\nIS\r\nl_idx       PLS_INTEGER;\r\nl_chars_before      VARCHAR2(32767);\r\nl_escape_char       VARCHAR2(1) := p_esc;\r\nl_chars_before_count        PLS_INTEGER := 0;\r\nBEGIN\r\n>\r\nLOOP\r\nl_idx := instr(p_str,p_del, p_idx);\r\nIF l_idx > 0 then\r\nWHILE substr(p_str, l_idx-l_chars_before_count-1, 1) = l_escape_char LOOP\r\nl_chars_before_count := l_chars_before_count +1;\r\nEND LOOP;\r\n\r\nIF mod(l_chars_before_count, 2) = 0 THEN\r\n-- if chars_before_count is even, then we're at a segment boundary\r\nRETURN l_idx;\r\nELSE\r\n-- if odd, then we're at an escaped delimiter, want to move past\r\nRETURN splitit(p_str, p_del, l_idx+1, p_esc);\r\nEND IF;\r\nl_chars_before_count := 0;\r\nELSE\r\nRETURN l_idx;\r\nEXIT outer;\r\nEND IF;\r\nEND LOOP;\r\nEND splitit;\r\n\/* ------------------- function splitit ------------------*\/\r\n\r\n\/* ------------------- function unescape ------------------*\/\r\n\r\nFUNCTION unescape(p_str VARCHAR2, p_del VARCHAR2 := ',', p_esc VARCHAR2 := ':')\r\nRETURN VARCHAR2\r\nIS\r\nl_str VARCHAR2(32767);\r\nBEGIN\r\nl_str := replace(p_str, p_esc||p_del, p_del);\r\nl_str := replace(l_str, p_esc||p_esc, p_esc);\r\nRETURN l_str;\r\nEND unescape;\r\n\/* ------------------- function unescape ------------------*\/\r\n\r\n\/* ------------------- function split ------------------*\/\r\n\r\nFUNCTION split(p_list VARCHAR2, p_del VARCHAR2 := ',')\r\nRETURN split_tbl\r\nIS\r\nl_idx       PLS_INTEGER;\r\nsplit_idx   PLS_INTEGER     := 0;\r\nl_list      VARCHAR2(32767) := p_list;\r\nl_chars_before      VARCHAR2(32767);\r\nl_escape_char       VARCHAR2(1) := ':';\r\nl_array split_tbl := split_tbl('','','','','','','','','','');\r\nBEGIN\r\nl_list := p_list;\r\nLOOP\r\nsplit_idx := split_idx + 1;\r\nIF split_idx > 10 then\r\nEXIT;\r\nEND IF;\r\n\r\nl_idx := splitit(l_list, p_del, 1, l_escape_char);\r\nIF l_idx > 0 then\r\nl_array(split_idx) := unescape(substr(l_list,1,l_idx-1), p_del,\r\n\r\nl_escape_char);\r\nl_list := substr(l_list,l_idx+length(p_del));\r\nELSE\r\nl_array(split_idx) := l_list;\r\nEXIT;\r\nEND IF;\r\nEND LOOP;\r\nRETURN l_array;\r\nEND split;\r\n\/* ------------------- function split ------------------*\/\r\n\r\n\/* in the header file, split_tbl is defined *\/\r\nTYPE split_tbl IS TABLE of varchar2(32767)<\/pre>\n<\/div>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
I’ve written about PL\/SQL before, but I’ve recently started working on a project that uses it heavily. Given the amount of code written for Oracle databases, I’m rather suprised that […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,15,6],"tags":[],"class_list":["post-292","post","type-post","status-publish","format-standard","hentry","category-databases","category-oracle","category-programming"],"_links":{"self":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts\/292","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/comments?post=292"}],"version-history":[{"count":0,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts\/292\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/media?parent=292"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/categories?post=292"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/tags?post=292"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}