Monday, June 30, 2008

Fun With Triggers

I don't care a whole lot for triggers. About the only thing I see of use is for some sort of auditing.

That said, I don't know a whole lot about them either. I don't know the specifics of how they work. I was tested today...

CREATE TABLE t
(
update_date DATE DEFAULT SYSDATE,
update_user VARCHAR2(30)
);

INSERT INTO t ( update_date )
VALUES ( ADD_MONTHS( SYSDATE, 100 ) );

INSERT INTO t ( update_date )
VALUES ( ADD_MONTHS( SYSDATE, -22 ) );

CJUSTICE@XE>SELECT * FROM T;

UPDATE_DA UPDATE_USER
--------- ------------------------------
31-OCT-16
31-AUG-06
Easy enough.

My requirement is that I capture the update_user, specifically in the case where it is not supplied. And this is where I ran into a wall.
CREATE OR REPLACE
TRIGGER biu_t
BEFORE INSERT OR UPDATE
ON t
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :NEW.update_user IS NULL THEN
:NEW.update_user := USER;
END IF;
END;
/
show errors
Easy enough.
UPDATE t 
SET update_date = ADD_MONTHS( SYSDATE, -12 );

CJUSTICE@XE>SELECT * FROM T;

UPDATE_DA UPDATE_USER
--------- ------------------------------
30-JUN-07 CJUSTICE
30-JUN-07 CJUSTICE

2 rows selected.
OK, not too bad. Let's try specifying the UPDATE_USER.
UPDATE t
SET update_date = ADD_MONTHS( SYSDATE, 12 ),
update_user = 'BOLLOCKS';

UPDATE_DA UPDATE_USER
--------- ------------------------------
30-JUN-09 BOLLOCKS
30-JUN-09 BOLLOCKS
Good, everything's working as expected...or is it? Let's connect as another user:
CJUSTICE@XE>conn hr/testing@xe
Connected.

UPDATE cjustice.t SET update_date = SYSDATE - 100;

HR@XE>SELECT * FROM cjustice.t;

UPDATE_DA UPDATE_USER
--------- ------------------------------
22-MAR-08 BOLLOCKS
22-MAR-08 BOLLOCKS
What? Why didn't the user get updated with HR? Let's add some dbms_output statements to the trigger:
CREATE OR REPLACE
TRIGGER biu_t
BEFORE INSERT OR UPDATE
ON t
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
dbms_output.put_line( 'New Value: ' || :NEW.update_user );
dbms_output.put_line( 'Old Value: ' || :OLD.update_user );

IF :NEW.update_user IS NULL THEN
:NEW.update_user := USER;
END IF;
END;
/
show errors

HR@XE>UPDATE cjustice.t SET update_date = SYSDATE + 100;
New Value: BOLLOCKS
Old Value: BOLLOCKS
New Value: BOLLOCKS
Old Value: BOLLOCKS

2 rows updated.

Elapsed: 00:00:00.04
HR@XE>SELECT * FROM cjustice.t;

UPDATE_DA UPDATE_USER
--------- ------------------------------
08-OCT-08 BOLLOCKS
08-OCT-08 BOLLOCKS

2 rows selected.
So UPDATE_USER was not updated with the value of HR, which I would expect. The :NEW and :OLD values are exactly the same...I'm guessing that Oracle makes a copy of the record first (puts it into a collection or something?) so UPDATE_USER would never be NULL.

Ultimately, I scrapped the update of UPDATE_USER in the trigger. I did force the UPDATE_DATE = SYSDATE, and that was it.

Ideally, I would specify that in the package call, but we aren't there yet. In my opinion, no other user would have UPDATE, INSERT or DELETE privileges on the table. But that's what I have.

Can someone with more experience with triggers help me out? Or advise me on what's going on?

Hertz Still Sucks

From Hertz:

Dear Mr. Justice:

We have received a report from USAA regarding your rental in Baltimore. We appreciate the opportunity to address your concerns.

We are always concerned when a customer is disappointed with our service for any reason. When customers accept the Fuel Purchase Option (FPO), they are pre-paying for a full tank of fuel in advance at a price per gallon that is comparable to local gasoline stations. There is no refund given for any fuel left in the tank at return, and this was stated on the rental record you signed; it is noted on the second page of the contract at the top. In addition, the estimated charges at the time of rental were $117.01, and this is the exact amount you were charged. However, we sincerely apologize for any misunderstanding concerning these charges and regret the confusion regarding the FPO.

At the same time, we realize communication problems can occasionally occur. In the interest of customer satisfaction, we have processed a $30.00 credit to your MasterCard account. This is approximately one-half of the FPO charge.

Mr. Justice, we appreciate your business. We hope to serve you again soon with more positive results.

Sincerely,

Redacted
Executive Administrator
OKC Customer Relations
The Hertz Corporation
P.O. Box 26120
14501 Hertz Quail Springs Parkway
Oklahoma City, OK 73134
U.S.A.

No, they didn't call either. Nor did they honestly address any of my complaints.

I never denied that I signed the agreement (thereby responsible for the FPO) and I didn't expect any compensation for it. My problem was with the particular agent who knew I would be in town for one day only and sold it to me anyway without telling me the specifics.

Oh well. Avis I'd use since they called my father and addressed his concerns. Hertz, never again.

Thursday, June 26, 2008

Corporate Life III

See Part I and Part II.

In Part I of Corporate Life, I said:
It feels like I've experienced about every event I could have imagined:
1. In January of 2007, a new CIO/SVP was hired and promptly restructured (replaced the VPs) the IT department.
2. In October of 2007, we had a nifty FBI raid.
3. January of 2008, we appear to be losing our CEO, CFO and General Counsel.
prodlife then mentioned that I hadn't experienced layoffs.

Four months later I did, on May 22, WellCare laid off 208 employees. I blogged it. I was fired.

I'm up to five corporate experiences.

I'm still missing the merger/acquisition and the IPO. Anything I'm missing?

Design - The Entity

I love designing databases. Specifically, data modeling. I love trying to figure out how the data fits together. Is this an attribute of that? Is it shared with something else? What's the relationship? One to many? One to one? Many to many? Hierarchical?

Let's start with people and addresses. A fairly easy mapping. One person can have one or more addresses, or one to many. The basics of that are drawn out below:


Person to Addresses



The primary key (whether natural or surrogate, defined here as a surrogate) for PEOPLE is stored as a Foreign Key in the ADDRESSES table. To guarantee that a PEOPLE key is supplied for each address, you should add a NOT NULL constraint along with the FK constraint.

Not too difficult.

What if down the road you need to add organizations? Do you shove them into the people table or do you create a new table for them? My initial thought is to create a new table called ORGANIZATIONS.


People Address and Organizations



Easy enough right?

Now I want to track addresses for the ORGANIZATION table. Hmmm...how to do that? My first thought was to just create another address table.


People, Address, Organization, Address



Hmmm...now I have addresses in two different tables. I've introduced the chance of having non-standardized addresses (two entry points, or maybe I don't have the same attributes in both tables). Writing a query to get all the addresses (for a mailing perhaps) will be a bit more difficult.

How about just adding another column to the original ADDRESS table?


People, Organization, Address



That works...sort of. I must remove the NOT NULL constraint for PEOPLE and I definitely can't add one for ORGANIZATION. But they're in one table and I can have one entry point (it would have to be conditional though). What if in the future I add another table? Do I just add another column (FKd) to ADDRESS.

It starts to become a bit unwieldy.

So, enter the ENTITY.


The entity

The ENTITY just becomes a placeholder, providing keys for PEOPLE (one to one) and ORGANIZATIONS (one to one). ENTITYID is stored in the ADDRESS table and voila! Perhaps this is called a supertype (not real sure on my terminology here). You now have one entry to the ADDRESS table (well...hopefully). To pull all addresses is easy.

You can use the surrogate key from ENTITY as your primary key in PEOPLE and ORGANIZATIONS, so if you want to bypass the ENTITY table, you can do so easily.

That's my solution anyway. How about you? Same, different? Am I crazy (I'm starting to think so...)?

Wednesday, June 25, 2008

Hertz Sucks, USAA Rules

Yesterday, Hertz sucked. Today, they still suck. My $266 deposit is stilloutstanding, three days later. I got my receipt for $117 and change on Monday and as of 11:11 PM Wednesday night, they're still holding $150.

I did write a short note to USAA last night and pointed them to my blog post. Today, they called and left me a nice little voice mail. They were going to send it along to the Hertz escalation team and she was also going to forward it on to USAA management. USAA has the best customer service, and it shows.

My father, who rented a car with Avis, had a similar fiasco with the fuel service option. He was under the impression as well that he would receive the difference so he filled up just before returning the car and they still charged him for a full tank of gas. He wrote a note to USAA and within a couple of hours, Avis had responded and refunded him some $70 (and he wasn't even expected money back, just acknowledgement that it was disingenuous).

I still haven't heard from Hertz.

So, in conclusion, Hertz sucks, USAA rocks, Avis is pretty good.

Tuesday, June 24, 2008

Hertz Sucks!

On Sunday I flew, with my five year old son, to Baltimore Washington Internation airport (BWI). We went to support my father and attend the funeral of his father. My son had gotten quite close with Grandpa Jim since we moved down to Tampa so I wanted him to go for obvious reasons.

My grandfather was to be buried at Quantico with his wife who passed away 12 years ago. He passed away on Father's Day.

Initially I wanted to drive. I haven't been on a plane in 6 years (OpenWorld 2002), and I'm a bit claustrophobic. My parents convinced me that flying was the best way to go since my wife's grandparents were in the hospital as well (94 and 95). I reluctantly agreed. I then decided it would be a good time for my son to take his first flight. I did not want him to be scared of flying, so I knew what I had to do.

I decided to rent a car so that my parents would not have to drive 2 hours to pick me up and take us back. We arrived and we got on the bus to the rental car facility (about 10 minutes away). I booked a car with Budget through USAA.

I quickly found out that Budget put a $500 hold on your debit card. Since we're still adjusting to the new payment schedule, we didn't have that much in the bank. I called my parents and they were all set to come get us. Then the woman from Budget suggested Hertz as they don't put as big a hold on your card.

So I walk over and the first thing I say is, "I don't have $500 in my account, what's your hold?" I don't remember exactly what it was, but it surely wasn't $500. OK, give me a car please, cheapest you got. I only need the car for a day (24 hours). Do you want any options? No. Do you want the Fuel Service Option? Sure, I think, I won't have to fill up on the way back since we'll be cutting it close anyway.

I get the car and drive down to Fort Belvoir with my parents. The following day, we go to the service and then the cemetary. It's 2 o'clock now, the flight leaves at 4:54 and the car has to be back by 3:30.

I figure I'll have to pay some sort of small fee for being past 3:30. No biggie, it happens.

I drop the car off, the guy scans the car, enters the mileage and gives me the receipt. $117. WTF?

Are you kidding me?

You bought the Fuel Service Option.

Yeah, and you'll deduct the amount that I didn't use right?

No. They should have explained that to you.

Ummm...no, they didn't.

Ultimately, it's my responsibility. I signed the contract. I don't expect to recoup any money.

But if this isn't disingenuous, I don't know what is. The guy knew I need the car for one day. Did he think I was going to drive 450 miles? He also knew that my reservation with Budget fell through because I didn't have $500 in the bank. Then I have a 5 year old running around the facility...

Again, I signed the contract. I accept full responsibility. I just think it sucks on Hertz part. Sadly, my father had the same thing happen to him. Bought the fuel service option and got screwed.

So, my advice to you (as I'm sure more than one of you have to rent a car every so often), don't get the fuel service option!

I will not rent from Hertz again. Budget, while way too much on the deposit, at least pointed me in the right direction. I appreciate that.

Wednesday, June 18, 2008

Long Live the Revolution!

Revolution Money that is...

I'm no longer afraid to at least say that, though if you had checked out my LinkedIn profile, you would have seen it.

Revolution Money is a startup company described as "PayPal meets MasterCard" without the fees. Among the board members are:

  • Steve Case, founder of AOL

  • Ted Leonsis (here and here), among other things, he is the current owner of the Washington Capitols

  • Larry Summers, former Secretary of the Treasury

There are a couple of other board members, but those are the ones that I know of off-hand.

So far I am extremely happy, though I don't really know how to deal with the distress that is no longer there. I suppose it will wane with time...

Everyone I work with seems to be amongst the best and brightest which is pretty cool. My view (pictures coming soon) is pretty awesome. Relaxed environment (think casual Friday every day). The CEO brings his dog into work...

I should have the opportunity to learn quite a bit here.

Wednesday, June 11, 2008

SQL for Buying a New Car

Two weeks ago I purchased a new car. The main reason behind that was that I drove a 1996 Nissan P.O.S. (not point of sale either). I now had a 30+ mile commute each way and I (well, my wife and mother) needed a reliable car.

A big factor was gas mileage.

Two brands stood out, Honda and Toyota. Honda has the Civic Hybrid and Toyota has the Prius (even though I don't care much for the design). The Corolla and Civic were the top two cars.

So after visiting those two dealers to look at cars, I stopped by the Mazda dealer to see if they had anything with MPG greater than 30. I drove the Mazda3 and it was nice (everything would be nice compared to my POS). Salesman offered to run the numbers and I said OK.

The point of this rambling, is that I want to know what it will cost based on mileage. Of course I didn't have my laptop, so I couldn't run the numbers (and I hadn't previously). I did it in Excel, but wanted to try it out in SQL.

Here's the result:

COLUMN ppg FORMAT $999.00
COLUMN car_1_gpy FORMAT 99,999
COLUMN car_2_gpy FORMAT 99,999
COLUMN car_1_cpy FORMAT $999,999.00
COLUMN car_2_cpy FORMAT $999,999.00
COLUMN car_1_cpm FORMAT 999.00
COLUMN car_2_cpm FORMAT 999.00

SELECT
car_1,
car_2,
ppg,
mpy,
ROUND( ( mpy / car_1 ) ) car_1_gpy,
ROUND( ( mpy / car_2 ) ) car_2_gpy,
ROUND( ( ( mpy / car_1 ) * ppg ), 2 ) car_1_cpy,
ROUND( ( ( mpy / car_2 ) * ppg ), 2 ) car_2_cpy,
ROUND( ( ( mpy / car_1 ) * ppg ) / mpy, 2 ) car_1_cpm,
ROUND( ( ( mpy / car_2 ) * ppg ) / mpy, 2 ) car_2_cpm
FROM
(
SELECT
rownum car_1,
LAG( rownum, 5 ) OVER
( PARTITION BY NULL
ORDER BY rownum ) car_2
FROM dual
CONNECT BY LEVEL <= 50
) car_miles,
(
SELECT
3.99 ppg,
15000 mpy
FROM dual
) constants
/

CAR_1 CAR_2 CAR_1_GPY CAR_2_GPY CAR_1_CPY CAR_2_CPY CAR_1_CPM CAR_2_CPM
---------- ---------- --------- --------- ------------ ------------ --------- ---------
1 15,000 $59,850.00 3.99
2 7,500 $29,925.00 2.00
3 5,000 $19,950.00 1.33
4 3,750 $14,962.50 1.00
5 3,000 $11,970.00 .80
6 1 2,500 15,000 $9,975.00 $59,850.00 .67 3.99
7 2 2,143 7,500 $8,550.00 $29,925.00 .57 2.00
8 3 1,875 5,000 $7,481.25 $19,950.00 .50 1.33
9 4 1,667 3,750 $6,650.00 $14,962.50 .44 1.00
10 5 1,500 3,000 $5,985.00 $11,970.00 .40 .80
11 6 1,364 2,500 $5,440.91 $9,975.00 .36 .67
12 7 1,250 2,143 $4,987.50 $8,550.00 .33 .57
13 8 1,154 1,875 $4,603.85 $7,481.25 .31 .50
14 9 1,071 1,667 $4,275.00 $6,650.00 .28 .44
15 10 1,000 1,500 $3,990.00 $5,985.00 .27 .40
16 11 938 1,364 $3,740.63 $5,440.91 .25 .36
17 12 882 1,250 $3,520.59 $4,987.50 .23 .33
18 13 833 1,154 $3,325.00 $4,603.85 .22 .31
19 14 789 1,071 $3,150.00 $4,275.00 .21 .28
20 15 750 1,000 $2,992.50 $3,990.00 .20 .27
21 16 714 938 $2,850.00 $3,740.63 .19 .25
22 17 682 882 $2,720.45 $3,520.59 .18 .23
23 18 652 833 $2,602.17 $3,325.00 .17 .22
24 19 625 789 $2,493.75 $3,150.00 .17 .21
25 20 600 750 $2,394.00 $2,992.50 .16 .20
26 21 577 714 $2,301.92 $2,850.00 .15 .19
27 22 556 682 $2,216.67 $2,720.45 .15 .18
28 23 536 652 $2,137.50 $2,602.17 .14 .17
29 24 517 625 $2,063.79 $2,493.75 .14 .17
30 25 500 600 $1,995.00 $2,394.00 .13 .16
31 26 484 577 $1,930.65 $2,301.92 .13 .15
32 27 469 556 $1,870.31 $2,216.67 .12 .15
33 28 455 536 $1,813.64 $2,137.50 .12 .14
34 29 441 517 $1,760.29 $2,063.79 .12 .14
35 30 429 500 $1,710.00 $1,995.00 .11 .13
36 31 417 484 $1,662.50 $1,930.65 .11 .13
37 32 405 469 $1,617.57 $1,870.31 .11 .12
38 33 395 455 $1,575.00 $1,813.64 .11 .12
39 34 385 441 $1,534.62 $1,760.29 .10 .12
40 35 375 429 $1,496.25 $1,710.00 .10 .11
41 36 366 417 $1,459.76 $1,662.50 .10 .11
42 37 357 405 $1,425.00 $1,617.57 .09 .11
43 38 349 395 $1,391.86 $1,575.00 .09 .11
44 39 341 385 $1,360.23 $1,534.62 .09 .10
45 40 333 375 $1,330.00 $1,496.25 .09 .10
46 41 326 366 $1,301.09 $1,459.76 .09 .10
47 42 319 357 $1,273.40 $1,425.00 .08 .09
48 43 313 349 $1,246.88 $1,391.86 .08 .09
49 44 306 341 $1,221.43 $1,360.23 .08 .09
50 45 300 333 $1,197.00 $1,330.00 .08 .09
Obviously this isn't terribly difficult. You can do lots with the results as well.

Three or four years ago I would have created a table and a function to do this. Now I can do it "virtually."

I love SQL...

*Key:
car_1 = miles for car 1
car_2 = miles for car 2
car_1_gpy = gallons per year car 1
car_2_gpy = gallons per year car 2
car_1_cpy = cost per year car 1 (gas)
car_2_cpy = cost per year car 2 (gas)
car_1_cpm = cost per mile car 1
car_2_cpm = cost per mile car 2

Thursday, June 5, 2008

BULK COLLECT and FORALL

I have a task to tune a little bit of SQL. It's very ETL like, but the target is not (yet) a star schema, it's pretty much a table flattened out for reporting purposes.

As I reviewed it, I noticed it went row by row, with a COMMIT inside the LOOP. That's gotta go.

Can I do this in one SQL statement? No, there's other processing that needs to be done (UPDATE two other tables before and after). Hmmm...could I just return the appropriate records into a collection? I'll have to look at that to see if it's possible.

For now though, I am going to try and use BULK COLLECT with the LIMIT clause and FORALL for the processes that occur before and after.

Let's create some data:

CREATE TABLE t( x NUMBER, y NUMBER );

INSERT INTO t( x, y )
SELECT
TRUNC( dbms_random.value( 1, 99999999 ) ),
TRUNC( dbms_random.value( 1, 100000 ) )
FROM dual
CONNECT BY level < 1001;
OK, now let's create an anonymous block, BULK COLLECTing the data from T into a PL/SQL table and then populated another table with that data:

DECLARE
TYPE t_record IS TABLE OF T%ROWTYPE;
l_table T_RECORD;
CURSOR c
IS
SELECT x, y
FROM t;
BEGIN
OPEN c;

LOOP
FETCH c
BULK COLLECT INTO l_table
LIMIT 100;

FORALL i IN 1..l_table.COUNT
INSERT INTO s ( x, y )
VALUES ( l_table(i).x, l_table(i).y );

EXIT WHEN C%NOTFOUND;
END LOOP;
CLOSE c;
END;
/
And then I run it and I get the following:

ERROR at line 18:
ORA-06550: line 18, column 16:
PLS-00436: implementation restriction: cannot reference
fields of BULK In-BIND table of records
ORA-06550: line 18, column 16:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 30:
PLS-00436: implementation restriction: cannot reference
fields of BULK In-BIND table of records
ORA-06550: line 18, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 16:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 17, column 7:
PL/SQL: SQL Statement ignored
A quick google search and I end up here .

So I can bulk bind, but I have to INSERT into the table as a whole. I can't be selective.

I updated my code to this:

DECLARE
TYPE t_record IS TABLE OF T%ROWTYPE;
l_table T_RECORD;
CURSOR c
IS
SELECT x, y
FROM t;
BEGIN
OPEN c;

LOOP
FETCH c
BULK COLLECT INTO l_table
LIMIT 100;

FORALL i IN 1..l_table.COUNT
INSERT INTO s
VALUES l_table(i);

EXIT WHEN C%NOTFOUND;
END LOOP;
CLOSE c;
END;
/
I run it and it completes successfully. This is all on XE, so I wonder (hope) that 11g will allow me to do what I want (I'll be working on an 11g RAC system). I scroll down the list of google results and I find this one which then takes me to AskTom. The first post demonstrates that my first attempt will work on 11g.

BULK COLLECT and FORALL are great tools if you can't do it in a single SQL statement and if you want to avoid the row by row processing.