Thursday, December 04, 2008

Oracle date goodies in SQL/PLSQL

Originally published 03/12/08...

I have been searching through Google, Yahoo! and various forums to create a small library of useful functions and crud for Oracle PL/SQL and SQL. I was either unhappy with the solutions I found or simply thought I could do them better, differently or somewhat the same.

Leap Year

(3-EXTRACT(MONTH FROM TRUNC(date_column,'YEAR')+59)) -- returns 1 if a leap year, 2 if not; from DATE datatype
(3-EXTRACT(MONTH FROM ADD_MONTHS('01-JAN-0001',numeric_year_column*12)-307)) -- returns 1 if a leap year, 2 if not; from NUMBER datatype
DECODE(EXTRACT(MONTH FROM TRUNC(date_column,'YEAR')+59),2,'True','False') -- substitute VARCHAR2 values for the result


v_date DATE;
v_date := TRUNC(i_date,'Y');
v_y := EXTRACT(YEAR FROM i_date);
v_q := TRUNC(MOD(v_y / 38 * 1440,60) / 2);
WHEN v_date THEN 'New Years'
WHEN NEXT_DAY(ADD_MONTHS(v_date,1) - 1,'MON') + 14 THEN 'Presidents Day' --- Third Monday in February
WHEN NEXT_DAY(ADD_MONTHS('01-JAN-0001',v_y * 12 - 8) + v_q + (CASE WHEN v_q < 5 THEN 17 ELSE -12 END),'SAT') - 34 + (CASE v_y WHEN 2079 THEN 7 ELSE 0 END) THEN 'Easter' --- valid 1900-2199 only
WHEN NEXT_DAY(ADD_MONTHS(v_date,5) - 1,'MON') - 7 THEN 'Memorial Day' --- last Monday of May
WHEN ADD_MONTHS(v_date,6) + 3 THEN 'Independance Day'
WHEN NEXT_DAY(ADD_MONTHS(v_date,8) - 1,'MON') THEN 'Labor Day' --- first Monday in September
WHEN ADD_MONTHS(v_date,10) + 10 THEN 'Veterans Day'
WHEN NEXT_DAY(ADD_MONTHS(v_date,10) - 1,'THU') + 21 THEN 'Thanksgiving' --- fourth Thursday in November
WHEN ADD_MONTHS(v_date,11) + 24 THEN 'Christmas'
The above function appears to be fairly accurate. I tested 2000-2008 for all holidays and 1900-2199 for Easter (finding only one glitch for 2079 manually corrected with a CASE statement). The above function could prove handy if you have a time dimension in your data warehouse (who doesn't) and use PL/SQL to generate your data.


Uses the Meeus/Jones/Butcher Gregorian algorithm
v_g PLS_INTEGER; -- the Golden number; sequence in the 19-year lunar cycle
v_c PLS_INTEGER; -- the Christian era i.e. century
v_y PLS_INTEGER; -- the year number in the Christian era
v_e PLS_INTEGER; -- the epact; days in excess of the solar year over the lunar year
v_l PLS_INTEGER; -- the ?
v_g := MOD(i_year,19);
v_c := TRUNC(i_year / 100);
v_y := MOD(i_year,100);
v_e := MOD((19 * v_g + v_c - TRUNC(v_c / 4) - TRUNC((v_c - TRUNC((v_c + 8) / 25) + 1) / 3) + 15),30);
v_l := MOD(32 + 2 * (MOD(v_c,4) + TRUNC(v_y / 4)) - v_e - MOD(v_y,4),7);
RETURN ADD_MONTHS('22-MAR-0001',(i_year - 1) * 12) - TRUNC((v_g + 11 * v_e + 22 * v_l) / 451) * 7 + v_e + v_l;

Format Independant TO_DATE

v_datechar VARCHAR2(32000) := UPPER(TRIM(i_datechar));
RETURN CASE TRANSLATE(NVL(v_datechar,'N/U/L/L'),'0123456789/-ABCDEFGJLMNOPRSTUVY','##########/-^^^^^^^^^^^^^^^^^^^')
WHEN '^/^/^/^' THEN NULL
WHEN '########' THEN TO_DATE(v_datechar,'YYYYMMDD')
WHEN '######' THEN TO_DATE(v_datechar,'MMDDYY')
WHEN '##/##/##' THEN TO_DATE(v_datechar,'MM/DD/YY')
WHEN '##/##/####' THEN TO_DATE(v_datechar,'MM/DD/YYYY')
WHEN '####-##-##' THEN TO_DATE(v_datechar,'YYYY-MM-DD')
WHEN '##-^^^-##' THEN TO_DATE(v_datechar,'DD-MON-YY')
WHEN '##-^^^-####' THEN TO_DATE(v_datechar,'DD-MON-YYYY')

More to come...

Sunday, November 30, 2008

MLS 2008 Champions

Your 2008 champions of Major League Soccer: the Columbus Scruh, ... Crew.

What is more embarrassing? Having the commissioner of the league you just won a championship in come one letter short of calling you the "screw" on national television or having that same commissioner try to present the trophy to captain Guillermo Barros Schelotto when the captain was actually Frankie Hejduk; the guy strategically placed next to the cup for ease of transfer?

Suck a big one, Don.

Classic fail from the league head chimp aside, winning the 2008 MLS championship capped off a wonderful season that was a long time coming. There were a lot more fans that deserved this championship more than I. I can only offer praise to the groups that stayed with the team throughout the worst of the worst to finally get to greatness. I wasn't there for every game nor did I watch every game on television or at bar with a tab full of expensive finger foods and beer.

List of team accomplishments:

  • Took home the Trillium Cup; the league promoted distance rivalry between Columbus and Toronto, by beating them at home, then earning two draws on the road
  • Took control of the Supporter's Shield for having the best record at the end of the season
  • Defender of the Year for Chad Marshall
  • Coach of the Year for Sigi Schmid
  • Most Valuable Player for Guillermo Barros Schelotto
  • Took home the 2008 MLS Cup for defeating Kansas City on aggregate goals, the Chicago Fire 2-1 at home, and then the New York Red Bulls 3-1 in Carson City
  • MLS Cup MVP for Guillermo Barros Schelotto for directly assisting on all three goals
I made sure I picked up a copy of the Columbus Dispatch to tuck away somewhere as a memory for my later years. It would be nice to pick up a photoset, too.

Some consider the celebration short-lived. An expansion draft was held the next Thursday and starter Brad Evans went to Seattle. There is talk that defender of the year Chad Marshall is heading overseas (more cash and higher level of play) and that coach of the year, Sigi Schmid is heading to Seattle to be closer to family on the West Coast and to get a longer-term contract (three years) to make it easy to settle somewhere. A sizable chunk of cash will be required to sign Guillermo Barros Schelotto for another year or so. There are developmental players that will be waived. There will be veterans that will probably be asked to step down.

Regardless of these developments, 2008 was a triumphant year for the Columbus Crew. Nothing can take that away.

Sunday, November 16, 2008

MLS conference finals

I have some catching up to do here?

The Crew went on to defeat the Kansas City Wizards in Columbus which sent them to the finals. Chicago pounded a wounded New England three zip in Bridgeview, sending them to face us in the Eastern Conference finals. It was a match for the ages, everything it was supposed to be and then some.

There were so many back stories.

The biggest one was the return of Brian McBride. McBride was one of the original Crew players and face of the organization. To this day, people still associate McBride with Columbus. Brian left in 2003 to join English Premier League (top level) club Fulham. This season, he returned to America to end his career in the MLS but not for Columbus; he wanted to play for Chicago, his hometown (Arlington Heights). His first encounter with his former MLS team was in Chicago where he had the first and last goals of the two goal tie. Now he would make his return to Columbus with our elimination on his mind.

Another big story was the return of fan favorite Jon Busch. Jon was acquired in the 2002 SuperDraft and was starting for the Crew by 2003. Busch was between the pipes when Columbus won the Lamar Hunt U.S. Open Cup in 2002 and won goalkeeper of the year in 2004. He suffered season ending ACL injuries in both 2005 and 2006. He was waived in 2007, picked up by Toronto, then waived again and then picked up by Chicago. Busch has been, and always will be, a vocal person. The person who once stated that he could not see playing anywhere but Columbus was now displaying public anger towards Coach Sigi Schmid.

What else? The Fire had Kettering, Ohio native Chris Rolfe. Both teams played to two, two to two draws. Chicago was the only team Columbus did not defeat in 2008. The Crew lost to Chicago back in the June Open Cup match.

The first goal of the match was the worst possible scenario. Roughly 30 minutes into the game a defensive brain fart lets McBride get a head on a Mapp cross. One to zip, visitors and the travelling fans were jubilant. The Columbus fans never gave up though. The second half was a completely different story.

Roughly 50 minutes into the match, Crew central defender Chad Marshall out-duals the aerial powerhouse McBride and heads home a Guillermo Barros Schelotto free-kick. It was a perfect strike off the cross-bar; nothing anyone could have done to stop it. Five minutes later, Eddie Gaven buries a low, bouncing shot underneath Busch for the second goal. That one would end up the game winner.

About forty nervous minutes followed. I certain every single Crew fan was thinking the same thing: when was McBride going to tie this in the dying moments of regulation (just like he did in Chicago). Well, it didn’t happen. A few missed heartbeats later the Crew were going to their first MLS Cup with their first conference championship.


The Crew will face the Western Conference champions - the New York Red Bulls??? Don't ask. Just destroy them.

Monday, November 03, 2008

MLS semi-finals, leg 1

The first leg of the home and away aggregate goal series went close to how I thought it would go. The Crew tied the Wizards with a late goal; so they will have to win the series at home. Wizard play maker Hercules Gomez will sit out that game because he was ejected after a violent foul in game one. New England was unable to score goals at home but luckily for them, the Fire didn’t score any either so their series is tied. The Red Bulls tied Houston on their home turf so they will probably get pounded when they travel to Houston. Real Salt Lake took a goal at home and hopefully they will survive their trip to California. Not surprising, there were three ties out of four games and not much scoring.

Sunday, October 26, 2008

Crew vs. DC, playoff thoughts

Today's match against D.C. United couldn't have been scripted any better. The same northwest goal post that denied two United attempts happily tucked a Brad Evans long-range shot into the back of the next for the only goal of the match. The massive supporters groups were able to enjoy the Supporter's Shield trophy and the Columbus Crew sent a hated rival with a long history of knocking us out of the playoffs, out of the playoffs. How sweet it is. Now it is on to the playoffs.

The first round of the MLS semi-finals consists of a home and away aggregate goal system. The lowest seed hosts the first game and the highest seed hosts the second game. In my opinion, this does not give the advantage to the team that did the best during the regular season. At best the two teams are equal or a slight advantage is given to the lower seeded team. Here is why.

The lower seeded team can come out hard; early and usually get away with it. New England did this to us in 2004 by delivering some hard fouls early in the match to our playmakers and midfield anchors (notable was a Shalrie Joseph tackle on Simon Elliot, worthy of ejection but went without even a caution and Simon wasn't the same player after that tackle; Joseph did the same thing to Jaime Moreno when they played DC, btw).

Using home field advantage for the first game, the lower seeded team has more control over the flow of the game as the visiting (higher seeded) team must usually sit back and feel out. The home and away series somewhat turns into a two-half match (one half home and away). So if the home team can squeeze out a victory by a goal (or more) they still control the flow of the game because the second leg forces the higher seeded team to score that many goals plus one (to avoid penalty kicks). The higher seeded team will need to press for goals opening them up for counter attack goals. I think this format benefits the "boring", defensive teams that rely upon the two or three highly skilled players up top typically running a quick counter attack three on three or four.

The first round will feature Columbus vs. Kansas City and Chicago vs. New England in the east, Houston vs. New York and Chivas USA vs. Salk Lake in the west. If I were New York I'd come out full throttle for the full 90 minutes and rack up as many goals as possible on that crap Astroturf surface. I still think Houston will take that one in the long run otherwise. I would like to see Real Salt Lake get by Chivas USA just to give Jason Kreis a pat on the back for his years of service to MLS, give the fans a pat on the back for being patient and getting a new stadium, and to keep California out of the playoffs. In the east I think New England is too volatile and weakened to go up against a surging Chicago team. I also think the Crew will prevail against Kansas City but we might have to do so on home turf.

Wednesday, October 08, 2008

Why SQL generators suck

Assume the following table:

( craps_id NUMBER
, crap_size NUMBER
, crap_color VARCHAR2(16)
, crap_when_taken DATE DEFAULT SYSDATE
, crap_when_flushed DATE
, crap_rating NUMBER
, CONSTRAINT craps_pk PRIMARY KEY ( craps_id )
) storage_clauses, logging_clauses, santa_clauses ....

To get a list of craps and their size and color for a time interval by time it was flushed or if that was null (i.e. it was a proud poop that someone wanted everyone to see) the time it hit the bowl; the efficient SQL would be:

SELECT crap_size
, crap_color
, NVL(crap_when_flushed,crap_when_taken) crap_flushed_taken
FROM craps
WHERE NVL(crap_when_flushed,crap_when_taken) BETWEEN :1 and :2
ORDER BY crap_flushed_taken ASC NULLS FIRST;

Oracle would do a full table scan, efficient blocked I/O, and then poop out the results. Oh, wait. We're using a canned, record-based, RDBMS independant system that generates SQL that selects a list of primary keys and then reads each record by primary key. So we have something more like this:

SELECT craps_id
FROM craps
WHERE NVL(crap_when_flushed,crap_when_taken) BETWEEN :1 and :2
ORDER BY NVL(crap_when_flushed,crap_when_taken);

and for each selected primary key value:

SELECT crap_size
, crap_color
, NVL(crap_when_flushed,crap_when_taken) crap_flushed_taken
FROM craps
WHERE craps_id = :1;

Oh, wait. That NVL on those two fields is business logic that needs separated from the data layer; so we create a function:

crap_flushed_taken (i_crap_when_flushed DATE, i_crap_when_taken DATE)
RETURN NVL(i_crap_when_flushed,i_crap_when_taken);

No, no, no, stupid. That layer shouldn't need to know to pass two dates in it should work off the record/object primary key identifier:

crap_flushed_taken (i_craps_id NUMBER)
v_crap_when_flushed DATE;
v_crap_when_taken DATE;
SELECT crap_when_flushed, crap_when_taken
INTO v_crap_when_flushed, v_crap_when_taken
FROM craps
WHERE craps_id = i_craps_id;
RETURN NVL(v_crap_when_flushed,v_crap_when_taken);

Actually, this is how they did it

CREATE OR REPLACE FUNCTION crap_flushed_taken (i_craps_id NUMBER)
AS as language java name ' return java.lang.Date';

That's a Java class that basically does the exact same thing that little slice of PL/SQL was doing. The final, generated SQL statement went something along the lines of this:

SELECT craps_id
SELECT q1.craps_id
FROM craps q1
WHERE NVL(crap_flushed_taken(q1.craps_id),TO_DATE(1,'YYYY')) >= TO_DATE('????????','YYYYMMDD')
AND NVL(crap_flushed_taken(q1.craps_id),TO_DATE(1,'YYYY')) <= TO_DATE('????????','YYYYMMDD')
) t0
LEFT JOIN craps t1 ON t0.craps_id = t1.craps_id
ORDER BY WHERE crap_flushed_taken(t1.craps_id) ASC NULLS FIRST, t1.craps_id ASC NULLS FIRST;

Then the list program read each one of the selected primary key values and did this:

SELECT crap_size
, crap_color
, crap_when_flushed
, crap_when_taken
FROM craps
WHERE craps_id = :1;

Holy slang for feces! Oracle does an index fast full scan on the ID, which it would see as the most efficient way of going about this because in the optimizer's sane mind it would never think anyone would do a single read of the same table inside of a function. So for each primary key in that table (because we don't know the result of the function so no way to limit the working set) we are doing at least three (select, order by, list program), random (record location likely won't match the maintained order of the index), entire table reads using an index (read index block, then lookup corresponding block). Poop on me and call me a sundae...

I tried to simulate the above with inline select statements but the 10g optimizer was smart enough to figure it all out and come up with a plan five times the cost of the optimized plan for proper, hand-tuned SQL. I could try running the thing to get real statistics but unfortunately this thing runs for nine hours. My efficient SQL above runs in 30 seconds.

There are options available. First would be to rewrite the canned code and do the selection process with the optimized hand-written SQL. Another option would be to create an index on the function (haven't tried it yet) so theoretically Oracle will see the SELECT crap matches an index and use the index instead of reading the whole table one row at a time through the primary key index.

I know this is probably an extreme example (or one of many) but I think the same thoughts can be applied to code generators and to the object oriented purists that believe no business logic whatsoever belongs in the data tier.

Thursday, August 28, 2008

ORA-01008 on PL/SQL MERGE over link

If you get an "ORA-01008: not all variables bound" error when trying to do a MERGE INTO a remote table using local PL/SQL variables and local data from local tables, what do you do besides beat your head against the wall until it is soft and mushy like a sponge?
v_turd VARCHAR2 := 'Stinky';
MERGE INTO table@link m
, poop
, dood
, loaf
FROM local_table
WHERE num = 2
) u
ON (m.loaf = u.loaf)
WHEN MATCHED THEN UPDATE SET m.turd = v_turd, m.poop = u.poop
WHEN NOT MATCHED THEN INSERT VALUES (u.load, v_turd, u.crap, u.poop, u.dood, u.loaf);
Thankfully, it only took a couple of Google searches to find a solution offered by Carsten Herbe at Oracle's forums. The INSERT and UPDATE statements in the MERGE must only use data defined in the USING or literals (or maybe other stuff too). So...
v_turd VARCHAR2 := 'Stinky';
MERGE INTO table@link m
, poop
, dood
, loaf
, v_turd turd
FROM local_table
WHERE num = 2
) u
ON (m.loaf = u.loaf)
WHEN MATCHED THEN UPDATE SET m.turd = u.turd, m.poop = u.poop
WHEN NOT MATCHED THEN INSERT VALUES (u.load, u.turd, u.crap, u.poop, u.dood, u.loaf);
Sometimes I feel sorry for us computer nerds that have to deal with frustrating little things like a damned ORA-01008 that works perfectly in SQL when you are testing but as soon as you try to automate it with a variable you get your geek pee-pee whacked with a ruler. Bad geek, no work. Thankfully the clicker-web is littered with other suffering nerd-souls that have run into these problems on the smallest of free systems to multi-million dollar goliaths. Unlike other nagging problems this one didn't cause much suffering on my part. Hopefully by posting this here it will add to the Internet litter and maybe help others faster.

Tuesday, August 26, 2008

Oracle instant client, Ubuntu, revisited

For those that want quick installation instructions assuming you have the installation files on the root of a mounted CDROM:

sudo -s
mkdir /opt/oracle
cd /opt/oracle
unzip /media/cdrom/instanclient-basic- (etc.version.blah.blah)
unzip /media/cdrom/instanclient-sdk- (etc.version.blah.blah)
unzip /media/cdrom/instanclient-sqlplus- (etc.version.blah.blah) optional
ln -s instantclient_11_1 client
cd client
ln -s
ln -s
ln -s
tr -d '\r' < /media/cdrom/tnsnames.ora > /etc/tnsnames.ora
tr -d '\r' < /media/cdrom/sqlnet.ora > /etc/sqlnet.ora

To use SQLplus from a shell you need to add the ORACLE_HOME=/opt/oracle variable and add /opt/oracle/client to variable LD_LIBRARY_PATH to the /etc/bash.bashrc (for example).

Tuesday, July 15, 2008

Making fudged PII

As I start dealing more with application development that sends data to external sources I think more about the security of personally identifiable data within that data. When testing web services and file transfers there is no reason to have real, personally identifiable information (PII) in that stream of test data. However, the destination for that data still needs values in those elements. There are a few ways to approach this:

  1. Random data
  2. Pre-generated test data, test cases, etc.
  3. Use existing data but generate PII from a real primary key

The main problem I see with randomly generated data is "re-testing". For example: you send data for ID 1234567 to a service, randomly generating four columns of test data, then that service requests a re-test with the same data.

Pre-generated data would be a set of test cases, known trouble patterns and other data created before testing occurs. This scenario is feasible for new development on new data systems, to test min/max/null value scenarios that would otherwise never appear in live data, or to force a specific set of data. Where this scenario becomes cumbersome is when there is a tightly integrated system with numerous historical pre-cursor processes; triple the complexity if that system is another vendor's package and not your own. For example: create customer, purchase 14 months worth of product, run through aging and re-bill process, reconcile A/R, and then skip a month of purchases. There could be over a hundred tables touched by that set of processes and if one is skipped/missed then the data for another group is no longer valid.

Generating test data from the primary key is feasible when the system has a long history of identified test data handy but simply needs PII altered to protect the identity of the individual or organization. By using a unique, primary key the "fudged" PII data will always match back to the source primary key for instances where "re-testing" is required. For example: ID 1234567 always generates social security number 898-75-5309 (not a real number but will validate in some systems).

The example below was written for Oracle SQL to show how to convert a seven digit primary key identifier into a social security number, date of birth and gender:
||TO_CHAR(MOD(TO_NUMBER(,9999)-9999,'S0000') social_security_number
WHEN '2' THEN 2191.5
WHEN '3' THEN 2191.5
WHEN '4' THEN 4383
WHEN '5' THEN 6574.5
WHEN '6' THEN 8766
WHEN '7' THEN 10957.5
WHEN '8' THEN 13149
WHEN '9' THEN 15340.5
ELSE 0 END),'MM/DD/YYYY') date_of_birth
WHEN SUBSTR(,-1,1) < '5' THEN 'Male'
ELSE 'Female' END gender
, CASE SUBSTR(,-1,1)
WHEN '0' THEN 'AI' --- American Indian/Alaskan
WHEN '1' THEN 'AS' --- Asian/Pacific Islander
WHEN '2' THEN 'BL' --- Black/Non-Hispanic
WHEN '3' THEN 'HS' --- Hispanic
WHEN '4' THEN 'NR' --- Non-Resident Alien
ELSE 'WH' END ethnic_code
FROM table_name t1 WHERE [selection criteria];
The social security number is pretty straight forward. If area numbers (first three digits) higher than 772 do not validate, then use area 267 (237-267 have all groups allocated within them).

The date of birth is a little complicated but the attempt was to use the ones digit to generate one of seven date ranges with the lower two in the range getting more hits because they are the primary age group dealt with. Starting with a base age of 18 years of age, subtract one of seven six year blocks, and then subtract zero to six years.

Gender was a simple test of the ones digit to determine male or female.

Ethnic background was a simple translation of the ones digit to a code taking into account there are more numbers in four of the six groups.

This was a very bare example meant only to suggest direction. It would be interesting to build a library (although someone probably already has).

Tuesday, February 26, 2008

A few jokes

Just a few jokes for today...


Toward the end of the church service, the minister asked, "How many of you have forgiven your enemies?" About 80% held up their hands. The minister then repeated the question and all responded by raising their hands except one small, elderly lady.

"Mrs. Jones? Are you not willing to forgive your enemies?", the minister asked.

"I don't have any", she replied, smiling sweetly.

"Mrs. Jones, that is indeed unusual. How old are you?"

"Ninety-eight" she replied.

"Mrs. Jones, would you come down front and tell the congregation how a person can live for ninety-eight years and not have an enemy in the world?"

The little lady tottered down the aisle, faced the congregation, smiled sweetly and said, "I outlived the bitches."


We've all been there but don't like to admit it. We've all kicked back in our cubicles and suddenly felt something brewing down below. As much as we try to convince ourselves otherwise, the WORK POOP is inevitable. For those who hate pooping at work, following is the Survival Guide for taking a dump at work.

When farting, you walk briskly around the office so the smell is not in your area and everyone else gets a whiff but doesn't know where it came from. Be careful when you do this. Do not stop until the full fart has been expelled. Walk an extra 30 feet to make sure the smell has left your pants.
The act of scouting out a bathroom before pooping. Walk in and check for other poopers. If there are others in the bathroom, leave and come back again. Be careful not to become a FREQUENT FLYER. People may become suspicious if they catch you constantly going into the bathroom.
A fart that slips out while taking a leak at the urinal or forcing a poop in a stall. This is usually accompanied by a sudden wave of embarrassment. If you release an escapee, do not acknowledge it. Pretend it did not happen. If you are standing next to the farter in the urinal, pretend you did not hear it. No one likes an escapee. It is uncomfortable for all involved. Making a joke or laughing makes both parties feel uneasy.
When forcing a poop, several farts slip out at a machine gun pace. This is usually a side effect of diarrhea or a hangover. If this should happen, do not panic. Remain in the stall until everyone has left the bathroom to spare everyone the awkwardness of what just occurred.
The act of flushing the toilet the instant the poop hits the water. This reduces the amount of air time the poop has to stink up the bathroom. This can help you avoid being caught doing the WALK OF SHAME.
Walking from the stall, to the sink, to the door after you have just stunk up the bathroom. This can be a very uncomfortable moment if someone walks in and busts you. As with farts, it is best to pretend that the smell does not exist. Can be avoided with the use of the COURTESY FLUSH.
A colleague who poops at work and is damn proud of it. You will often see an Out Of The Closet Pooper enter the bathroom with a newspaper or magazine under his or her arm. Always look around the office for the Out Of The Closet Pooper before entering the bathroom.
A group of co-workers who band together to ensure emergency pooping goes off without incident. This group can help you to monitor the whereabouts of Out Of The Closet Poopers, and identify SAFE HAVENS.
A seldom used bathroom somewhere in the building where you can least expect visitors. Try floors that are predominantly of the opposite sex. This will reduce the odds of a pooper of your sex entering the bathroom.
Someone who does not realize that you are in the stall and tries to force the door open. This is one of the most shocking and vulnerable moments that can occur when taking a poop at work. If this occurs, remain in the stall until the Turd Burglar leaves. This way you will avoid all uncomfortable eye contact.
A phony cough that alerts all new entrants into the bathroom that you are in a stall. This can be used to cover-up a WATERMELON, or to alert potential Turd Burglars. Very effective when used in conjunction with an ASTAIRE.
A subtle toe-tap that is used to alert potential Turd Burglars that you are occupying a stall. This will remove all doubt that the stall is occupied. If you hear an Astaire, leave the bathroom immediately so the pooper can poop in peace.
A poop that creates a loud splash when hitting the toilet water. This is also an embarrassing incident. If you feel a Watermelon coming on, create a diversion. See CAMO-COUGH.

Thursday, February 07, 2008

ORA-22992 Oracle hell

Welcome to my little corner of hell today. A third party vendor changed their tables structures recently, adding a CLOB field to some of their tables. Like most hard working technical folk we have had to write a lot of new code to provide functionality the third party vendor does not and will not provide. So some perfectly good ANSI standard SQL suddenly puked out a "ORA-22992: cannot use LOB locators selected from remote tables" error.

I'm not using any LOB's in the queries so why in the blue heck does Oracle want to their LOB locators? I did a search and thankfully found where someone on Oracle's forums just tried to use the WHERE clause to join instead of the JOIN statement and it worked. I tried it on and sure enough, it worked. It goes something like this:

crap1 VARCHAR2(10)
crap2 DATE

poop1 VARCHAR2(10)
poop2 DATE
poop3 CLOB

SELECT crap2,poop2
FROM table1@remote JOIN table2@remote ON crap1 = poop1;

ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables

SELECT crap2,poop2
FROM table1@remote, table2@remote WHERE crap1 = poop1;

glorious data

There is nothing we geeks hate more than code that should work but doesn't. Especially when the people we work for are paying five, six, seven or more figures for this software. The problem was originally noticed over two and a half years ago!