Failed Deployment...

236 Days
20 Hours
48 Minutes
30 Seconds

I hate making mistakes but I've made another one. My streak ends almost 237 days from my previous one.

Something so silly too.

In our source system, data was been double loaded somehow. So we decided on a surgical delete. A total of 7 DELETE statements needed to be run; 4 on the source system and 3 on the target system.

The source system went off without a hitch. I babysat the re-load of the source tables and was ready to have our load jobs run in our target system.

What's this? It ran in half the time?! How's that possible?

I pulled up our logs to find that zero rows were loaded into one of our tables. There should have been 45 Million plus.

I started to run down the possible causes:
1. Did the job we have in the scheduler that TRUNCATEs our persistent staging tables run? Nope.
2. Did I fail to instruct the DBAs correctly in the critical CR? Nope. Instructions look good.
3. Next to the logs, it ran fine on Saturday morning but not Sunday morning. What happened yesterday?
4. Ah yes, my CR. Open up the script...nothing out of the ordinary...and then I saw it.

On our target system, we use work tables to pre-generate a keys. It makes things a heck of a lot faster and removes the need for PL/SQL lookups in SQL (no, we don't have incremental builds yet).

So the work table needs to be DELETEd from first based on the keys from the first:

DELETE FROM some_key_table a
WHERE EXISTS ( SELECT NULL
               FROM the_main_table
               WHERE business IN ( 'TTT', 'TTR' )
                 AND dateof = TO_DATE( '24-MAR-08', 'DD-MON-YY' )
                 AND my_key = a.my_key );

OK, no funny business there.

Then I DELETE from the main table:

DELETE FROM the_main_table a
WHERE EXISTS ( SELECT NULL
               FROM the_main_table
               WHERE business IN ( 'TTT', 'TTR' )
                 AND dateof = TO_DATE( '24-MAR-08', 'DD-MON-YY' ) );

As I look at it I wonder WTH I was thinking using an EXISTS clause on the main table. That's the source.

But do you see what I missed?

See it yet?

OK, I left out the "AND my_key = a.my_key" from the inner query. Obviously a stupid approach, but it would have worked. The best way to do it is to just get rid of the EXISTS clause:

DELETE FROM the_main_table a
WHERE business IN ( 'TTT', 'TTR' )
  AND dateof = TO_DATE( '24-MAR-08', 'DD-MON-YY' ) );

Live and learn, live and learn...

How Does Oracle Make Development Easier?

Continuing on the theme of late, what are the basic things that you can do to reduce the amount of code that needs to be written?

In the post linked above, I mentioned Constraints as probably the easiest way to reduce the amount of coding. For example:

CREATE TABLE t
(
  id NUMBER(10)
    CONSTRAINT pk_id_t PRIMARY KEY,
  first_name VARCHAR2(30)
    CONSTRAINT nn_firstname_t NOT NULL,
  middle_name VARCHAR2(30),
  last_name VARCHAR2(40)
    CONSTRAINT nn_lastname_t NOT NULL,
  gender VARCHAR2(1)
    CONSTRAINT nn_gender_t NOT NULL
    CONSTRAINT ck_morf_gender_t CHECK ( gender IN ( 'M', 'F' ) ),
  ssn VARCHAR2(9)
    CONSTRAINT nn_ssn_t NOT NULL
    CONSTRAINT ck_9_ssn_t CHECK ( LENGTH( ssn ) = 9 )
    CONSTRAINT ck_numeric_ssn_t CHECK ( REGEXP_INSTR( ssn, ?, ?, ? ) )
    CONSTRAINT uq_ssn_t UNIQUE  
);

ID - is just a sequence generated key, no big deal there.
FIRST_NAME - is not optional you hence the NOT NULL constraint.
MIDDLE_NAME - is optional (no constraint).
LAST_NAME - is not optional (NOT NULL).
GENDER - is not optional (NOT NULL). Also, you want to exclude everything but 'M' or 'F', thus the CHECK constraint.
SSN - is not optional (NOT NULL). The length of the value must be 9 characters (CHECK). The characters may only be numeric (CHECK). Unfortunately I don't yet know the REGEXP_INSTR function yet to truly demonstrate. Finally, the UNIQUE constraint on SSN since they shouldn't duplicate across people.

This is a simple demonstration of how you can potentially use constraints to reduce the amount of code necessary. Though I would probably check/validate these as well in code because the error that is generated will not be unique so it would difficult to tell. The point is, if you make a mistake in your validation code it will be easily caught by the constraints forcing you to fix it.

This will give you much more reliable data, which as we all know, is the most important thing.

I'd like to do more of the posts pointing out the easiest methods to reduce the amount of code you have to write by using Oracle.

What kind of solutions do you have or do you use?

Use the [Oracle] Database dammit!

Dom Brooks recently posted an article about the Dea(r)th of the Oracle RDBMS. It seemed to struck a chord.

I've written about MySQL Friday or Application Developers vs. Database Developerswhich were similar in thought; the database is a bucket.

Ultimately, my take is that application developers don't know and don't want to learn how to use a database. PL/SQL specifically, is a platform in and of itself. You can do so much in the database now that you essentially need an application only for display, to determine the row color if you will.

The usual caveat follows:
If you are building applications that are supposed to be database independent, then the logic belongs in the application. The database is a bucket.

If you are building business applications specific to Oracle though, use the damn thing. Application/web developers are then forced to work on the design and user interface, not application/transaction logic.

Easy steps to actually utilize your database:
1. Use as many constraints as humanly possible - This will reduce the amount of code you have to write and you'll have the security of knowing the data will be what you constrain it to be.
2. DEFAULT columns in table definitions - create_date or load_date can be default to SYSDATE and thus left out of any application code. I've gone so far as to use SYS_CONTEXT( 'MY_CONTEXT', 'USERID' ) as the DEFAULT value for the create_user column. That along with a NOT NULL (or CHECK) constraint, makes life that much easier.
3. Did I mention constraints? Primary Key and Foreign Key constraints are very important to maintain data integrity (ensure you have the data you expect). Don't forget to index those foreign keys.
4. Security - VPD (Virtual Private Database) or Fine Grained Access Control. No longer do you need to maintain two separate schemas (or databases), just add a column and only allow those with the value set see that data. If you are using ApEx, this is incredibly easy to do.
5. Security (Roles and Privileges) - No more table based authorization, let the database do it through roles and privileges. GRANT EXECUTE ON my_package TO some_user

That's my short list for today. Like Dom, this makes me angry. If there were some rational logic behind it, great, convince me. I haven't seen it yet though.

The Return to ApEx

It's been almost a year, but I've finally gotten a chance to dive back into ApEx!

I've been working primarily on our financial reconciliation for our Medicaid business. That's now very stable as we have everything in our fancy new star schema.

One of the support type activities we've been doing for the past 6 months is maintaining their rate tables...manually. They send (and resend) a csv file and we then match and insert those new records into their rate tables. I don't get to recreate the entire thing unfortunately, it's horribly designed, but I do get to do something.

So instead of doing these manually I finally convinced my boss that this could be done relatively easy with ApEx. I've demo'd it for him in the past, so he's aware of it's capabilities (my evangelism of it doesn't hurt though). Of course our VP steps in and says we have to go through the technical review board. Fair enough, I'm all for standards.

Thankfully my manager convinced the architects that we don't have the Java or Ruby resources to do this, plus, it would take weeks!

So, here's to ApEx, and the further infiltration of it at WellCare!

Bowling for IT

Another non-Oracle related post. Just fun at work.

Last Friday we had another one of our IT all-hands meetings. My goal at each one is to make either the rumor list (of the top ten variety) or to be somehow be involved (hopefully good) in other ways.

Two months ago I was promoted to SVP of IT, because I was able to talk my CIO into it. Last month, I was promoted to CEO because I happen to resemble our new CEO. This month, the "light" piece was a "Where are they now?" complete with old/new pictures of IT employees. (Needless to say I am an only child...I crave attention!)

To say I've gained weight since starting a career/marriage/family would be an understatement. I went from a lean and mean 170 to about 250 now. The first pic I had just completed a sprint triathlon in Clermont, Florida. The second was sometime after the birth of my first child.


I take about every opportunity I can to send out the first one to new friends. "I didn't always look like a slob. That got to be my "before" picture and my "after" picture was my mugshot from my ID badge (yikes).

There I was 10 feet tall and looking great! I no longer had to send the picture out to anyone (and risk possible graffiti, though I guess posting it here doesn't help matters).

The important part was that I made all-hands again. I think that's 12 in a row.

And finally to the title of the post. After our all-hands meeting we went bowling. Food and bowling were free. I did notice however that there are quite a few, um..., drinkers among IT. I would certainly say that I fall into that category. At one point, it took so long to get a drink (only one bartender for 100 some odd people) I tried to ban the sale of mixed drinks so it would speed things up. However, my ploy didn't work.

I just bought two beers and waited for everyone else to follow-up with pitchers!

Gotta have fun at work right?