SQL & PL/SQL
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.
A common sight in databases is a table that contains the start and end values of a range - usually dates. One or both of the dates are typically part of the primary key; sometimes they are the entire key. Some examples:
- History tables
History tables record the changes made to a row over time. Instead of updating a row and losing the prior values of each non-key attribute, a new row in inserted. Each row is differentiated by two columns that record the date the row was created (
START_DATE) and the date it was superceded (
Here is a Function that convert NUMBERS into WORDS:
[code]CREATE OR REPLACE FUNCTION f_words (p_amount IN Number) RETURN Varchar2 IS
--Author : Bhupinder Singh
--Creation Date : 05/03/2007
--Purpose : This Function returns amount in words.
--1) p_amount : Only positive and negative values are allowed.
Precision can be entered upto 10 digits and only 2 scales
are allowed e.g 9999999999.99
One glance at my golf clubs would be enough to determine that I'm a terrible golfer. The pitching wedge is dirty. Nine-iron: dirty. Same with the eight, seven and six irons. Five, four and three irons are fairly clean. Woods: pristine. I play percentage golf (actually 110%, if you count penalties); I figure a 5-iron 150 meters down the fairway is a better bet than a 3-wood 200 meters into the trees.
So I've got a golf bag with 2 clubs that I paid for but never use. Madness? Well no, not really; but then I'm not paid to play golf. Can you imagine a professional golfer never using the driver? It wouldn't happen.
Can you picture an Oracle programmer never using the most powerful join method available? No? Get a mirror.
Never complain and never explain.
Disraeli was a lot of things, but Oracle Programmer was not amongst them. To be fair, perhaps he wasn't talking about Explain Plan?
SQL is a goal-oriented language. Unlike procedural languages, we tell the database what we want rather than how to get it. Oracle's Cost Based Optimizer comes up with an execution plan that is hopefully the most efficient way to resolve the query, but for many reasons it will often choose a sub-optimal plan.
A common complaint in SQL is that "it runs in 5 seconds in SQL*Plus, but takes hours in Production. Why?"
The reason is because SQL*Plus and most GUI SQL tools display rows as soon as they are fetched. In this way, you can
SELECT * FROM big_big_table and it will display the first 20 or so rows in the table in a fraction of a second, then go back for more. The SQL is not really finishing in seconds; if you timed how long it took to retrieve every row, you'd see that it takes just as long as in Production.
Where were you in 1990? Nelson Mandela was being freed from Victor Verster Prison after 26 years behind bars, Saddam Hussein was starting the Gulf War by invading Kuwait, and Tim Berners-Lee was inventing the World-Wide-Web at CERN in Geneva. Me? In 1990, I was writing an insurance system in Oracle SQL*Forms v2.3.
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.
A few days ago, while hunting for a bug in PL/SQL code, I stumbled upon the strangest way to write NULL. If the e-mail address parameter was empty, the genius who wrote this PL/SQL procedure set it to , then compared it with ten lines below, in order to log a problem. I really don't know what is it about NULL that scares people so much, but over the years I got used to occasional -1 and 0, or even 'EMPTY'. However, this is the first time I ran across Donald.