Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

November 10, 2005

String manipulation in PL/SQL

I've had to put my managerial hat to one side for a while. There's some very broken code in our system and I find myself the only person with the skills and time to fix it. It's the most serious PL/SQL programming I've done in a while and I'm beginning to remember why. These days if I want to get something done I'll reach for Python, and a day or so into this PL/SQL coding I'm pining for it already.

My first problem is with string handling. I want to do something as innocuous as count the number of commas in a string. In Python it's;

>>> myString.find(',')

In PL/SQL, well let's just say that PL/SQL doesn't have the robust string handling support that Python enjoys. The original author, who shall remain nameless to protect the incompetent, simply loops through each of the characters in the string incrementing a local variable if they are a comma. Not entirely efficient methinks. Mind you, Google isn't a fount of knowledge on the subject either, it's best offer is this snippet from Chapter 11 of Oracle PL/SQL Programming from O'Reilly. Which, summarised and less general than the example allows me to do something like;

FUNCTION count_occurrences(p_my_string IN VARCHAR2, p_character IN VARCHAR2) RETURN NUMBER IS
  l_search_loc NUMBER := 1;
  l_search_again BOOLEAN := TRUE;
  l_return_value NUMBER := 0;
BEGIN
  WHILE l_search_again LOOP
    l_search_loc := instr(p_my_string, p_character, l_search_loc, 1);
     l_search_again := l_search_loc > 0;
     IF l_search_again THEN
       l_return_value := l_return_value + 1;
       l_search_loc := l_search_loc + length(p_character);
     END IF;
   END LOOP;
   return l_return_value END;

It's more efficient than looping through the provided word or phrase but still a lot of code to do some simple string manipulation. I'd write it in Python, but Oracle doesn't let you write trigger code in proper programming languages ;-)

Posted by Andy Todd at November 10, 2005 04:06 PM

Comments

So, move your logic out of your database and into your model where it belongs!

Posted by: Simon Brunning on November 10, 2005 07:21 PM

hardly elegant, but:

l_return_value := length(p_my_string) - length(translate(p_my_string, chr(0) || p_character , chr(0)))

Posted by: Paddy on November 10, 2005 07:48 PM

(I think you mean myString.count(',') .)

Posted by: xtian on November 10, 2005 07:53 PM

> So, move your logic out of the database and into your model where it belongs!

If you pay for Oracle then you're building a database application. In which case the database is the place for business logic. It is the core of your application. If you can't write it in PL/SQL then write a Java or C stored procedure. Though for string handling I'd use extproc_perl :-)

http://www.smashing.org/extproc_perl/userguide.html

Moving logic to the model should only be considered if the model is the only avenue into the database and outside of trivial webapps this is rarely the case.

Posted by: Jaysey on November 10, 2005 08:22 PM

Slightly less inelegant (maybe) than the translate option is

l_return_value := length(p_my_string) - length(replace(p_my_string,p_character))

(using the fact that replace without a 3rd argument deletes the character you're replacing).

But yes, string handling in PL/SQL is pretty verbose and low-level. Whether that translates to worse performance, I'm not sure, but it certainly makes things less maintainable than Python...

Posted by: Paul Moore on November 10, 2005 09:08 PM

...
myString.find(',')
...

seek for first occurence of comma, not a comma counter ;)

myString.count(',') is the right choice!

Posted by: Denis on November 11, 2005 05:58 PM

I very much feel Andy's pain on this. My opinion of PL/SQL has plummeted since I started with Python.

It's funny how much and how strenuously people disagree on where the business logic "belongs". People who come from straight programming backgrounds generally feel like nothing really belongs in the database except data.

I come from the database side of things. Applications come and go, and you may allow multiple applications to access the same database - indeed, unless you lock it down carefully, people may use SQL or SQL-based applications without even telling you they are. So, I feel, if the business logic is important at all, it should be welded straight to the data, where it can't be avoided, get thrown away with an obsolete application, or get out of sync between different apps.

Except then you can't write the logic in Python (not on Oracle, anyway). Phooey.

Posted by: Catherine Devlin on December 2, 2005 02:50 AM