Yep, this is another post where I’m going to talk about a feature of Oracle which I didn’t know about until very recently, and which I wish I’d heard of a lot sooner. Today’s topic is pipelined functions.
Now, I feel like I may have heard about pipelined functions once or twice over the years, but I never really understood what they could be used for. Part of the problem may be that when they’re mentioned, it’s often in the specific case of loading large amounts of data into a data warehouse environment, and most of my programming is for querying small amounts of data from ERP-type systems. But pipelined functions are not just for data warehouse situations!
If you’ve been following this blog for a while (or just read through the archives), you’re probably aware of my posts about database views and how they can be very powerful. And that hasn’t changed. But I’ve also found that, if you’re not very careful, referencing views from other views can quickly lead to massive performance problems. WriteTrack ran into this, though it was minor enough that I was able to get around it; I had a series of nested views which produced the calendars for every challenge for a specific user. Originally, the views were designed to produce the calendars for every challenge for every user, but this didn’t work well*, and I had to put v(‘APP_USER’) into the views–which meant that I couldn’t check the views through the SQL workshop, and that I couldn’t easily debug what was happening if a user complained that they were getting the wrong information.
Then I found out about pipelined functions, which were introduced by Oracle way back in 9i. A pipelined function is simply a PL/SQL function which returns a result that can be treated as a table in SQL. One of the first places I used them was in parsing an XML file; I created a view which wrapped the call to the XML parsing function, and was able to treat the data from the XML as if it were a table–all of the ugly calls to DBMS_XML were safely tucked away, making for much cleaner code (and easier APEX development!).
So, last October, I returned to my probelmatic WriteTrack views and re-wrote them as wrappers around a pipelined function. Now, instead of having four or five nested views to do all the necessary calculations (a debugging headache of its own), all the code is in one function. Which can, incidentally, produce all of the data for all users and all challenges in about a second.
* Ok, it didn’t work at all. Once there were multiple users and several different challenges, querying the overall view simply failed to return anything in less than an hour–hardly acceptable for an APEX application!