SQL & PL/SQL
Submitted by Kevin Meade on Thu, 2007-10-11 10:58
I have been asked several times recently, how to create "dynamic" views such that a view can be created using "variables". Now I see this question asked on the OraFaq message board, so I thought maybe this is worth posting as an article, so here it is. My answer comes in the form of an Email reply to a friend (why bother to rewrite a prefectly good answer). Please feel free to abuse my answer if you think it deficient, but you better be posting your own alternatives if you do so.
Submitted by Kevin Meade on Sun, 2007-09-09 13:48
In short an Autonomous Transaction lets a job, commit some data to the database as an ON-THE-SIDE event, without committing data in the MAIN-EVENT. Sounds useful and it can be. But the Autonomous Transaction can also be dangerous. An Autonomous Transaction is kind of like a teenage daughter (I have two) "Oh DAD! You just don't understand me". A lack of understanding is a foundation for trouble. Maybe we can’t understand our teenagers, but we can understand Autonomous Transactions a little better.
Submitted by vjain on Tue, 2007-07-24 19:12
If you are on Oracle 9i or higher, you have external tables and pipelined table functions available to meet your ETL needs. But in order to utilize these tools in a dynamic ETL environment, you need to design PL/SQL procedures that can support the loading of files dynamically. This article will provide you with one design that has proven to be very robust and scalable.
Submitted by Art Trifonov on Mon, 2007-06-25 12:42
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.
Submitted by Art Trifonov on Sun, 2007-06-24 13:01
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.
Submitted by Kevin Meade on Tue, 2007-06-19 15:59
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.
Submitted by Kevin Meade on Sun, 2007-06-17 20:40
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.
Submitted by Kevin Meade on Thu, 2007-06-14 20:09
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.
Submitted by Kevin Meade on Fri, 2007-05-25 16:55
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.
Submitted by KrishnaBoppana on Mon, 2007-04-16 14:35
“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: