ORACLENERD

iPhone AppStore oops?

2008-08-07T22:17:00.003-04:00

My wife has an iPhone (I have the $10 phone I got with my 2 year plan). I love the thing. I steal it whenever I can.

One of my favorite things to do though is to ask her a random question, you know, one that would take 3 seconds to google and find the answer, just to test her. Usually, she just says "I don't know"

Um...you have an iPhone...and you can look it up right? (lots of sarcasm)

Oh yeah.

I probably do it because I have the $10 phone and I'm jealous.

Anyway, she showed me a screen shot yesterday of an app in the AppStore that was selling for $999.99. Funny. What do you get? A pretty picture. What else? Nothing.

So, via Jake then Digg, I found the following picture:

Oracle Jobs in Doral (Miami), FL

2008-08-06T16:49:00.003-04:00

Trying to help someone out. Wife won't leave (permanently) to Miami, so I'm out of the running. Number of positions on the left, short position description on the right.

Contact me either through comments or email (chet.justice at gmail) and I'll give you the contact information of the recruiter.

1 Oracle Fin Apps 11i Financials Functional need (core modules)

6 Oracle PL/SQL Developer needs w/ FinApps experience

2 Lead PL/SQL Developer needs w/FinApps

4 Oracle Reports 6i Developer needs

2 Lead Oracle Reports 6i Developer needs

2 Oracle Discoverer needs

How to Find a Job

2008-08-04T21:50:00.006-04:00

My job search is going. I had an interview last Friday and I have a follow-up tomorrow. I've got a few other leads, but nothing as concrete.

Last time I "lost" my job, I was prepared and was working within a week. I've been able to explore different ways to search for jobs this time as I was taken by surprise.

Here's a rundown of how I'm currently looking for work:
1. Twitter - Jake over at AppsLab suggested creating a feed on the "oracle" keyword. Sure enough, I've seen 5 or 6 job postings.
2. LinkedIn - Trolling my connections and others (again, suggested by Jake)
3. ORACLENERD - Nothing's come of this one, but I'm hopeful that someday in the future it will work for me. At the very least, it gives prospective employers insight into my thinking.
4. craigslist - I've found 2 or 3 jobs postings through here.
5. Friends - I've been fortunate to meet a lot of people in the Tampa Bay area. Despite my firing I've still got a decent reputation around town.
6. CareerBuilder - Goes without saying.
7. Monster - Still works.

One thing I have noticed is that my hits go way up when I lose my job. I'll accept a low hit count if it means I can keep my job!

Special thanks go to Tom, Jake, LewisC, Frank, Dodd, and Ray. If I left you off, it wasn't on purpose...there's just been so much going on.

Oracle Mix: Two Ideas

2008-07-31T23:15:00.003-04:00

I've added two ideas at Oracle Mix. The first one is UTL_FILE has the ability to read the contents of a directory and the second one is Add a pseudo-column that stores the date the record was INSERTed or UPDATEd.

I mentioned the second one before. The first one I've seen on the iloveplsqland.net site. So go and vote (or don't, but leave your comments).

11g New Feature: PIVOT

2008-07-31T17:15:00.003-04:00

I do have an interview tomorrow. Woohoo!

So my go at the PIVOT operator, "new" in 11g.

pivot_clause
Datawarehouse Guide
SQL Reference Examples
Arup Nanda's Example

My example.

Let's create some data first:


CREATE TABLE transaction_types
(
  transactiontypecode VARCHAR2(10)
    CONSTRAINT pk_transactiontypecode PRIMARY KEY
);

INSERT INTO transaction_types( transactiontypecode )
VALUES ( 'DEBIT' );
INSERT INTO transaction_types( transactiontypecode )
VALUES ( 'CREDIT' );

CREATE TABLE transactions
(
  id NUMBER(10)
    CONSTRAINT pk_id PRIMARY KEY,
  transactiontypecode
    CONSTRAINT fk_ttcode_transactions REFERENCES transaction_types( transactiontypecode )
    CONSTRAINT nn_ttcode_transactions NOT NULL,
  amount NUMBER(16,2)
    CONSTRAINT nn_amount_trans NOT NULL
    CONSTRAINT ck_amount_trans CHECK ( amount >= 0 ),
  date_created DATE DEFAULT SYSDATE
    CONSTRAINT nn_datecreated_trans NOT NULL
);

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 1,
    'DEBIT',
    44.44 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 2,
    'DEBIT',
    20.34 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 3,
    'CREDIT',
    5.60 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 4,
    'DEBIT',
    67 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 5,
    'DEBIT',
    234.55 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 6,
    'CREDIT',
    76.55 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 7,
    'DEBIT',
    3.45 );

So what's the big deal with PIVOT? I'm not sure yet other than it's something new and new is cool.

Basically, PIVOT allows you to pivot rows into columns. We often do this for reports we generate. Here's the old way:


SELECT
  TRUNC( date_created ) date_created,
  SUM( CASE
        WHEN transactiontypecode = 'DEBIT' THEN
          amount
       END ) debit_amount,
  SUM( CASE
        WHEN transactiontypecode = 'CREDIT' THEN
          amount
       END ) credit_amount
FROM transactions
GROUP BY 
  TRUNC( date_created )
ORDER BY date_created;

DATE_CREA DEBIT_AMOUNT CREDIT_AMOUNT
--------- ------------ -------------
31-JUL-08       369.78         82.15

Really, not that bad. But if I want to do COUNT and AVG, I have to create more CASE statements like the ones above. My query will go from 14 lines to 30 in a hurry.

Here's the new cool way:


SELECT *
FROM
  (
    SELECT 
      transactiontypecode tt,
      TRUNC( date_created ) date_created,
      amount 
    FROM transactions
  )
PIVOT
(
  SUM( amount ) total_amount
  FOR tt IN ( 'DEBIT' AS "DEBIT", 'CREDIT' AS "CREDIT" )
);

DATE_CREA DEBIT_TOTAL_AMOUNT CREDIT_TOTAL_AMOUNT
--------- ------------------ -------------------
31-JUL-08             369.78               82.15

It happens that is 14 rows as well. Now I'll add AVG and COUNT:

SELECT *
FROM
  (
    SELECT 
      transactiontypecode tt,
      TRUNC( date_created ) date_created,
      amount 
    FROM transactions
  )
PIVOT
(
  SUM( amount ) total_amount,
  COUNT( amount ) total_count,
  AVG( amount ) avg_amount
  FOR tt IN ( 'DEBIT' AS "DEBIT", 'CREDIT' AS "CREDIT" )
);

DATE_CREA      D_AMT      D_CNT      D_AVG      C_AMT      C_CNT      C_AVG
--------- ---------- ---------- ---------- ---------- ---------- ----------
31-JUL-08     369.78          5     73.956      82.15          2     41.075

Nice! Sixteen lines of SQL...not bad at all. That should make code a bit more readable (it'll fit on a single page)...I like it!

wordle

2008-07-31T11:35:00.002-04:00

Cool stuff.

My del.ico.us cloud.

Do you think I need a job? ;)

I Found a New Job!

2008-07-31T11:27:00.001-04:00

http://www.foxnews.com/story/0,2933,395181,00.html

My wife thinks I'm perfect for it!

11g New Feature: Virtual Columns

2008-07-30T19:08:00.004-04:00

Not really so new as it's more than a year old...but I've finally had the opportunity to check it out. Well, opportunity at my last job which ended on Monday, but since I have so much free time now, I figured I give it a run.

Virtual Columns

Defined in the New Features doc as:

Virtual columns are defined by evaluating an expression the results of which become the metadata of the columns for tables. Virtual columns can be defined at table creation or modification time.

Virtual columns enable application developers to define computations and transformations as the column (metadata) definition of tables without space consumption. This makes application development easier and less error-prone, as well as enhances query optimization by providing additional statistics to the optimizer for these virtual columns.

So what's it good for?

One thing that I see is that any derived column or expression can be placed at the table level (and indexed too!). This as opposed to putting it in a view and taking the chance that the logic is repeated exactly in every location.

So here goes my example:


CREATE TABLE virtual_columns
(
  id NUMBER
    CONSTRAINT pk_id PRIMARY KEY,
  first_name VARCHAR2(30),
  middle_name VARCHAR2(20),
  last_name VARCHAR2(40),
  full_name AS
  ( CASE
      WHEN middle_name IS NULL THEN
        first_name || ' ' || last_name
      WHEN middle_name IS NOT NULL THEN
        first_name || ' ' || middle_name || ' ' || last_name
    END ) VIRTUAL
);

INSERT INTO virtual_columns
  ( id,
    first_name,
    middle_name,
    last_name )
VALUES
  ( 1,
    'CHET',
    NULL,
    'JUSTICE' );

INSERT INTO virtual_columns
  ( id,
    first_name,
    middle_name,
    last_name )
VALUES
  ( 2,
    'MICKEY',
    'CARL',
    'MOUSE' );

CJUSTICE@>SELECT * FROM virtual_columns;

        ID FIRST_NAME MIDD LAST_NAME  FULL_NAME
---------- ---------- ---- ---------- ------------------------------
         1 CHET            JUSTICE    CHET JUSTICE
         2 MICKEY     CARL MOUSE      MICKEY CARL MOUSE

2 rows selected.

Very cool stuff indeed! One thing that might be an issue is that if someone does a SELECT * from a table without any filters. If the expression is complex (SQL --> PL/SQL context switching), it might slow it down. Of course I have no evidence to back this up yet...just pondering.

Design: The Entity?

2008-07-29T22:08:00.013-04:00

Last month, I posted my preference
for maintaining addresses (and other entity attributes) via an ENTITY table. I got some good feedback, but I would like more.

Option 1

Option 2

In short, I prefer Option 1 as it requires no further modifications (if adding another entity) and you won't have to write funky SQL to get all the addresses (though you would have to UNION the people and organizations table and any subsequent entity). The one bad thing perhaps is that you would have to keep a close eye on your code to prevent someone from subverting your process...I think anyway.

So please vote and sound off in comments as to the "Why" of your choice.

(If you choose neither, please, please, please explain!)

Don't Go Into the Office

2008-07-29T14:34:00.002-04:00

Whenever my boss asks me to come with him...I will politely tell him I'm not going.

I was laid off yesterday.

I want to see how many jobs I can have in one years time apparently. I didn't undermine myself this time though.

So if you know of anything in the Tampa Bay area, please let me know.

Revolution Money Seeks Senior DBA

2008-07-28T14:55:00.001-04:00

Here's your chance to work with 11g RAC.

Either email me (chet dot justice at gmail) your resume and I will pass it along, or drop me a line and I will provide you with the appropriate contact information.

Here are the details:

JOB SUMMARY

As a Database Administrator with Revolution Money Inc, you will be responsible for all aspects of administration and the deployment of the various database components of our products. Major duties are to include installation, configuration, integration, tuning, troubleshooting and support.

Joining our team means you will have the opportunity to implement and use advanced features such as clustering and replication. We operate in the following environment: Linux; Oracle 11g RAC on ASM with Oracle Data Guard and Oracle Streams; 7 x 24 on-call support.

DUTIES & ESSENTIAL JOB FUNCTIONS
• Responsible for 24X7 production support.
• Install and maintain Oracle servers and service pack deployments.
• Manage security of application access to databases.
• Facilitate root cause analysis on database issues.
• Maintaining database performance by developing tuning methodologies.
• Perform backup and recovery.
• Assist database development environment and life cycle within the organization using best practices to ensure the benefits of standardization, documentation and change management.
• Providing information by collecting, analyzing, and summarizing database performance and trends.
• Research, planning and implementation of database upgrades and patches.
• Maintain technical documentation relating to database management.
• Perform benchmarking analysis to properly size hardware platforms.

OTHER FUNCTIONS AND RESPONSIBILITIES

Assist our internal customers running Oracle Database products including Oracle Applications.

QUALIFICATIONS

Required:

• A Bachelor's Degree in computer science or equivalent is required along with 5+ years experience with Oracle as a DBA.
• Production experience managing and monitoring Oracle 10g RAC databases.
• Oracle OEM/Grid Control, RMAN
• Strong PL/SQL , UNIX Scripting, Perl
• Solid Linux/UNIX and networking experience.
• Must be highly motivated, and a quick learner.
• Strong interpersonal skills and the ability to work well in teams, as well as working within a fast-paced, dynamic environment are requirements.

Desirable Skills:

• Oracle 11g RAC databases
• Oracle Streams
• Oracle BI/Data Warehouse, Oracle Financials, OID, OWB

Foot In Mouth Syndrome

2008-07-27T22:09:00.004-04:00

On Friday of last week we were doing our first code review (well, my first code review at this job). A fun 2600 line package, 42 pages.

As were going through one piece, there's an exception block, with an explicit ROLLBACK, some logging code, and then a COMMIT. Of course the logging code should be an autonomous transactions, but apparently it's a bit sketchy.

Anyway, I blather on about not needing an explicit ROLLBACK after the exception as everything is rolled back prior...

"no it's not."

Me: "Yes it is."

"No, it's not."

Me: "Yes, it is."

We then moved on. I go back to my desk to write up a quick test proving my point.

Of course I was wrong though. If you have thing one and thing two and the exception occurs on thing two, the data/record/whatever for thing one exists. And, if you are going to issue a COMMIT and you don't want that orphaned record out there, you must issue an explicit ROLLBACK.

I sent a note out to everyone saying that my colleague is right, but I was still irked by it somehow.

It just didn't feel right.

So I left for lunch for a bit, still thinking about if of course.

Wait, I was saying one thing but thinking about something else. I was thinking of a logical transaction. Thing two depends on thing one therefore you would never, ever want to COMMIT after the exception. The logging code through me off for some reason.

I of course had to send out another email explaining my thought process and my mistake, in essence making me seem the hard loser. Why do I have to do that? Just let it lie...

Oh well, I've done it before and I'll probably do it again. Thankfully I work with some good people so they won't hold it over my head for too long...

Fraud Analysis?

2008-07-16T21:55:00.001-04:00

So we are pondering how to go about fraud analysis real time.

One thing currently holding us back is that all the key fields are encrypted (obviously). How do you go about doing fraud analysis with such seemingly high overhead?

My question to you is, do you know of any fraud related off-the-shelf tools or libraries out there? Preferably written in PL/SQL, but Java or C will do as well.

Commas: Before or After the Line?

2008-07-14T22:36:00.002-04:00

I'm in a new group again which means I have to learn (and accept) other people's style. No one at WellCare put commas before the line (thankfully), but I've found a few here.

I've finally come to accept that this is just style and doesn't really matter, as long as the code does what it's intended to do.

Yes, it's silly, but we all have our little quirks right?

Men Are From Venus?

2008-07-10T21:50:00.005-04:00

So my wife calls me today to share a funny story.

Apparently my son (Little Chet) asked about metorites. He had seen them on Jimmy Neutron - Boy Genius. She starts explaining what they are and the conversation led to talk about the space shuttle and the space station.

LC: What do they do up there?

WIFE: They do all kinds of stuff, experiments, look at planets, etc.

LC: The look at planets? Like Planet Penis?

WIFE: What?!

LC: Planet Penis, the one close to Earth.

WIFE: You mean Venus?

LC: Yeah, that's the one.

He doesn't get "Uranus" yet...

Pseudo Column ROWDATE?

2008-07-09T21:56:00.005-04:00

I'm working a little bit in the datawarehouse again (by the way, is it two words or one?). A technical guy from Oracle showed up today to help us decide the best way to move forward on capturing changes. There were essentially 4 methods:

1. SQL and PL/SQL
2. Streams (CDC)
3. OWB/ODI
4. Logical Standby/Data Guard

As someone on the phone was talking, I started to wander...hmmm...what about some kind of pseudo column for that stored the last update (either INSERT or UPDATE) of a row?

Off the top of my head, I can think of rowid, rownum, and level. I'm sure there are others (feel free to comment).

Oracle, I'm sure, stores that information some where right?

I hadn't really given a thought as to the feasibility or the impact it might have, but that would make capturing changes a whole lot easier...

Count The K's

2008-07-08T21:00:00.002-04:00

I have this really annoying co-worker who happens to be the DBA. Everytime he walks by my desk he pounces on my keyboard. I've learned to Windows Key + L to lock the computer when I hear him approaching, but occasionally I forget.

As I'm standing near my unlocked computer he starts typing furiously in my sql*plus session (shouldn't he know better?). I should know better...

Walking away he asks, "How many K's are in there?"

I ignored him, but then wondered myself...what's the best solution to this problem?

So, here it goes:

DECLARE
  l_count_k NUMBER := 0;
  l_string VARCHAR2(300);
  l_string_length INTEGER;
BEGIN
  l_string := 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
TKELTKER';

  l_string_length := LENGTH( l_string );

  FOR i IN 1..l_string_length LOOP
    IF SUBSTR( l_string, i, 1 ) IN ( 'K', 'k' ) THEN
      l_count_k := l_count_k + 1;
    END IF;
  END LOOP;
  
  dbms_output.put_line( 'Kk Count:  ' || l_count_k );
END;
/

Easy enough, 45.

Then I started thinkinhg...can I do this in pure SQL? Of course!

SELECT 
  SUM( CASE
         WHEN SUBSTR( UPPER( mystring ), rownum, 1 ) = 'K' THEN
           1
       END ) k
FROM
  dual,
  (
    SELECT 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
    LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
    EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
    TKELTKER' mystring
    FROM dual
  ) c
CONNECT BY LEVEL <= LENGTH( mystring )

COUNT_OF_K
----------
        45

1 row selected.

I'm sure many of you can do better than that. So let's see 'em. Prodlife, this isn't a complicators test either. ;)

Process

2008-07-06T23:18:00.003-04:00

I read The Daily WTF, well, daily. On Thursday last week, there was a good one on process. Essentially, the entire process had to be followed when an error occurred at boot. F1 would have solved the problem immediately...

My first job I never really got to put anything into production, so I wasn't real familiar with it. My second job, I was the lone ranger, so I did everything myself (though I did not do development in production). My last job however, was full of "The Process."

Rightfully so, especially in a large environment (i.e. more than 1 developer), though I think it was a bit overdone. And up until one of my failed deployments, the deployment itself was done through the Change Request (CR). What I mean by that, is that the code was attached to the CR itself. Since I attached a newer version, which had not been QA'd, well, you get the picture. We finally moved to a system whereby the DBAs actually deployed from our source control system...thankfully.

Now I'm in an environment that's a mix between the last job and the second to last. Everything is QA'd, but there isn't this whole process surrounding deployments...yet. Fortunately we're small enough to deal with it.

What's the point? I'm not sure.

Perhaps it's that I've learned more what not to do from The Daily WTF...

Compound Triggers

2008-07-02T21:19:00.002-04:00

Seriously, I don't like triggers. But if you have to maintain them, you might as well make the best of it.

While trying to figure out my problem the other day, I ran across Compound Triggers. I hadn't read about it in the 11g New Features guide, but since I don't use triggers, I wasn't sure if it was new or not. Apprently it is...

In essence, you can combine multiple triggers into one. I won't go into the gory details (because I don't know the gory details), but I will provide the example from the docs for your perusal.


CREATE TRIGGER compound_trigger
  FOR UPDATE OF sal ON emp
    COMPOUND TRIGGER

  -- Declaration Section
  -- Variables declared here have firing-statement duration.
  threshold CONSTANT SIMPLE_INTEGER := 200;

  BEFORE STATEMENT IS
  BEGIN
    ...
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    ...
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
    ...
  END AFTER EACH ROW;
END compound_trigger;
/

At the very minimum (if you have to use them), you might as well combine them into one and save a bit on maintenance/debugging!

Editorial Control Over Comments

2008-07-02T20:54:00.002-04:00

I recently commented on a blog that's fed through OraNA. Actually, I left one, received a comment back by the author, and then commented again. The author has editorial review before posting comments publicly.

I have no problem with that, I practice it. Fortunately, I haven't had the tough choice of whether to publish or not. It can't be easy.

That said, my second comment, in response to his response to my initial comment (still with me? ;) was never published. This may have been a simple oversight (it was yesterday), but I have no idea (yet anyway).

I blog because I crave the challenge. I want people to tell me I'm completely full of sh*t. Seriously. As long as it doesn't get personal, I'm all for discussion.

Funny License Plate

2008-07-02T20:33:00.001-04:00


H20UUP2

First to figure out wins absolutely nothing but praise!

Lookup Tables

2008-07-01T21:52:00.004-04:00

Also known as reference, crosswalk and a few other names.

I love 'em.

I'm not afraid to use them. It certainly makes that table count go up, but you know what you're getting.

I have ADDRESS_TYPES, PHONE_TYPES, PERSON_TYPES (in an intersection table of course) and any other kind of TYPE you can imagine.

I could use CHECK constraints I guess, but if it's anything other than Y or N, I typically create a lookup table to go with the table.

Let's take an ADDRESS table. ADDRESSTYPECODE becomes an attribute of an address. It gets a Foreign Key to the ADDRESS_TYPES table and also (many seem to leave this one out), a NOT NULL constraint. Every address has to have an type.

To make it somewhat easier, I use codes (as opposed to IDs which I tend to associate with numbers) so a join isn't absolutely necessary. If 'HOME' is the ADDRESSTYPECODE, you would rarely need to join as it's self evident what that means. If the lookup table is large, I'd typically use ID (or numbers) for the key.

Like I said, it bumps up that table count and makes things look a bit "messy," but you know exactly what belongs in what column. And if you're using ApEx, administrative screens are a snap!

Just don't ask Duke Ganote whether type is a good name or not!

Fun With Triggers

2008-06-30T20:55:00.005-04:00

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

2008-06-30T01:04:00.002-04:00

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.

Corporate Life III

2008-06-26T23:54:00.002-04:00

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?