SQL & PL/SQL
In Part I we discussed views, stored procedures, and table functions as the three options of returning record sets from the code stored in the database. In this section I will concentrate on table functions, demonstrating several kinds of solutions that utilize this feature.
Having in my practice found many useful applications for table functions I am a big fan of this feature. Unfortunately, I don’t think it always gets the attention it deserves. Oracle documentation does a decent job of explaining how to code table functions, but the use cases they provide can lead one to believe this feature has no universal application and is meant to solve just a few specific kinds of problems.
So here I am writing even more documentation for my current Gig, and thinking once again, why not post it to OraFAQ and get double duty out of the document. So here is a discussion of the WITH clause that comes with the SELECT statement now. It is easy to use, and handy as all get out, but seems many people have not yet noticed it. Hmm... a SELECT statement that does not start with SELECT.
Analytics are magic. But as with most software products; I seem to use only one percent of the features, ninety nine percent of the time. Indeed, having built warehouses and reporting systems for the last eighteen months, I look back and see that I got a lot done with only three Analytics, SUM, LAG, and LEAD. Knowing how intimidating Analytics can look to those who have not used them, I figured I’d show the uninitiated, how to get in through the back door, with a reduced look at the capability. You can do a lot with very little. We are going to discuss just one Analytic, SUM used to create running totals.
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.
“Find out how to use DBMS_SQLTUNE package to tune SQL Statements in Oracle 10g”
Have you ever been frustrated with SQL Tuning?
Did you get lost in the maze of /+ HINTS +/ and analysis paralysis?
Pre-10g you have to be either a SQL tuning expert or should have a license to expensive tuning tools available in the market to effectively tune SQL Statements. Not the case anymore.
In 10g you can use DBMS_SQLTUNE package to get tuning recommendations for SQL Statements.
Tuning of SQL Statements using DBMS_SQLTUNE involves following 4 steps:
Every PL/SQl devloper must have tried their hands with dynamic SQl. Its an indelible part of oracle.But its generally used when we dont know at compile time about the object on which we are to perform the task. Suppose we want to insert data into a table but we get the name of the table during the execution of the procedure, in those cases we would use dynamic SQl with the table name passed as bind variable. Dynamic Sql is even used to execute DDl commands inside a PL/SQl block.
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.