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

Holidays


CREATE OR REPLACE FUNCTION is_holiday(i_date DATE) RETURN VARCHAR2 IS
v_date DATE;
v_y NUMBER;
v_q NUMBER;
BEGIN
v_date := TRUNC(i_date,'Y');
v_y := EXTRACT(YEAR FROM i_date);
v_q := TRUNC(MOD(v_y / 38 * 1440,60) / 2);
RETURN CASE i_date
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'
ELSE NULL
END;
END;
/
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.

Easter



Uses the Meeus/Jones/Butcher Gregorian algorithm
FUNCTION GET_EASTER2_FN(i_year NUMBER) RETURN DATE IS
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 ?
BEGIN
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;
END;

Format Independant TO_DATE

FUNCTION TO_DATE_FN( i_datechar VARCHAR2 ) RETURN DATE IS
v_datechar VARCHAR2(32000) := UPPER(TRIM(i_datechar));
BEGIN
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')
ELSE TO_DATE('01-JAN-2989','DD-MON-YYYY')
END;
END TO_DATE_FN;

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.

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?
DECLARE
v_turd VARCHAR2 := 'Stinky';
BEGIN
MERGE INTO table@link m
USING (
SELECT
crap
, 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);
COMMIT;
END;
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...
DECLARE
v_turd VARCHAR2 := 'Stinky';
BEGIN
MERGE INTO table@link m
USING (
SELECT
crap
, 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);
COMMIT;
END;
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 libclntsh.so.11.1 libclntsh.so
ln -s libocci.so.11.1 libocci.so
ln -s libsqora.so.11.1 libsqora.so
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:
SELECT t1.id  --- VARCHAR2(7)
, TO_CHAR(899-FLOOR(TO_NUMBER(t1.id)/989901),'000')
||TO_CHAR(MOD(FLOOR(TO_NUMBER(t1.id)/9999),99)-99,'S00')
||TO_CHAR(MOD(TO_NUMBER(t1.id),9999)-9999,'S0000') social_security_number
, TO_CHAR(TRUNC(TRUNC(SYSDATE-6574.5,'YEAR')-MOD(FLOOR(TO_NUMBER(t1.id)/10),2191.5)-CASE SUBSTR(t1.id,-1,1)
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
, CASE
WHEN SUBSTR(t1.id,-1,1) < '5' THEN 'Male'
ELSE 'Female' END gender
, CASE SUBSTR(t1.id,-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
WHEN '6' THEN 'AS'
WHEN '7' THEN 'BL'
WHEN '8' THEN 'HS'
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...

HAVE YOU FORGIVEN YOUR ENEMIES?......

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."

HOW TO POOP AT WORK

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.

CROP DUSTING
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.
FLY BY
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.
ESCAPEE
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.
JAILBREAK
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.
COURTESY FLUSH
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.
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.
OUT OF THE CLOSET POOPER
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.
THE POOPING FRIENDS NETWORK (P.F.N)
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.
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.
TURD BURGLAR
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.
CAMO-COUGH
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.
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.
WATERMELON
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.