Kevin Meade's blog
My current client has the crew here doing a good deal of data pivoting in migrating data between several Oracle Systems. I figured to turn some heads in a code review by providing a solution for data pivots which is not based on "locally favored" traditional 3GL coding practices but instead employs what is to me the more natural way of doing it for an Oracle database. I was right, heads did turn. Since I now have to document it, I figured why not post it on OraFAQ once and then tell them its online, come here and read it. So this is an example of pivoting in SQL.
My newest project needed to create a record keeping component that would keep track of balancing results over time. Sounded good, meant my customers could look back over time to see how things went, and it provided persistent evidence of good results. I figured on adding two routines (save/get) to a logging package already in existence to save balancing data via an autonomous transaction and retrieve it when needed. But in writing these routines it dawned on me that they would destroy the reusable nature of the logging package. Finally, a real life use for ANYDATA.
Been doing a lot with Historical Perspective lately. This has caused me to think a bit about the different variations of lookup that can be called a Point In Time query. My customers have found this discussion useful in understanding the details of how their report programs find specific rows in time. It is helpful to them because it provides an understanding as to why a specific row shows up on a report, and thus allows them to create more exacting definitions of what they want. Plus I find it is a good primer for newbies on staff to read so they don't make the same mistakes we made when we first started doing PIT queries. Maybe you can use it too.
One of my recent clients had a real data twisting process to validate. It didn't help much that their source had been corrected by many "ONE-SHOTS" over the years to deal with bugs, and accounting methods mandated by Act of Congress. What we needed was a way to see changes in a stream of related rows. In the end I created for them, a solution that allowed developers to pick two rows from a table at random and compare them in SQL with a result set returned showing only differences. Its a simple thing, but kind of neat, and very useful. So I'd like to share it with you.
Recently my friends at work saw me doing things with indexes they didn't understand. They saw what looked like unneeded columns in some indexes, and funny expressions they didn't know could be done in an index. So I explained to them the benefits of INDEX ONLY ACCESS, and FUNCTION BASED INDEXES. They had not seen these two techniques before so I figured maybe other people would be interested too. Here is it, a short discussion on what are fast becoming my two favorite tuning techniques.
Selects, Cursors, RefCursors, Cursor Expressions, some simple examples of rowset manipulation in SQL and PL/SQLSubmitted by Kevin Meade on Thu, 2007-01-11 12:58
So a buddy of mine, Ray, asked me for some examples of how data could be returned from a PL/SQL procedure. After a short discussion and some fiddling in SQL*Plus, we produced a neat document with some easy examples of what he could do. I still don't know how he is hooking this up to his coding tools, but it is a good demonstration of alternatives for manipulating sets of rows so I figured I'd post it. Hope someone besides Ray finds it useful. Ray's original question was "Kev, is there a way to return data from PL/SQL code so I can use it like a set of rows?". If you are an advanced PL/SQL developer you probabely already know this stuff but then again, it might be worth a five minute look for you.
I don’t want to learn XML. I don’t want to learn XPATH. I don’t want to learn XQUERY. I don’t want to learn XSLT. I don’t want to learn XSQL. I don’t want to learn XSU. XDB has some way cool stuff. But to-date, I have been pretty successful in not learning things I don’t want to learn. So you might think that when the time came for my databases to start sharing data via a XML transport, I would be in trouble. Fortunately, God gave me at least a few smarts when he loaded me up with laziness. In this article we will discuss one way to move XML formatted data in and out of Oracle using what we already know: object views, instead of triggers, collections, and PL/SQL packages.
If I had to recommend THE one most exploitable skill to have for an Oracle Developer or DBA, it would have to be HOW TO PLAY GOLF. I have observed few other talents give regular Mary and Joe, greater access to people from all levels of the corporate world. You will meet everyone from the janitor, to management elite without the fear or intimidation that we sometimes feel in these circles. For some reason, on the fairway all are equal; the only things that matter are your swing and your handicap. Alas, I never learned to play golf, so I must settle for imparting an actual job skill. And for that, my best first choice would be SQL FROM SQL.
Recently a friend asked me a simple question; "How do I display the results of a REFCURSOR through SQL*Plus?". The answer is of course is just as simple as the question, you "SELECT" it like anything else. He just had not seen the syntax before. Being me though, I wouldn't let him get away with such an easy offing, so I sent him instead a quick write-up on REFCURSORS and, the just as interesting, CURSOR EXPRESSIONS. This write-up shows various coding samples of how to use these to great effect. He liked it. He said it should be written up somewhere, and I knew just where. Hope you like it too. If you do, please add something in a reply, especially if you have a better or unique or interesting way of exploiting these features. I'd like to read about it, maybe even steal your code...
PL/SQL is an excellent language for Oracle. Integrated with the database, highly useful extensions to SQL, extremely powerful when exploiting Oracle Objects, and in spite of all this, it is still way easy to learn. But like any procedural programming language, one can get lazy with it, tending to accept the first solution arrived at. Looping constructs in particular seem to be used as crutches rather than necessary components of a solution. Bad looping causing performance issues is a problem that liters the PL/SQL landscape. But it is easy to spot and fix.