PL/SQL APIs

I was once told by a colleague, after sending out a note encouraging them, that they were not convinced of the effectiveness of PL/SQL APIs. I really didn't know what to say. Further, their past experience with them was bad. Huh?

As I went to do a little research on PL/SQL APIs, the first result I got was



What do you do with a response like that? Just submit it to Oracle WTF and be done?

"...not convinced of the effectiveness of PL/SQL APIs..."

Oracle has been selling that for years hasn't it? The Packages and Types Reference manual lists about 218 public packages which obviously doesn't include the internal only ones. Oracle seems to have bought into the PL/SQL API.

Steven Feuerstein wants you to build them. I personally don't like his style, but there is no arguing results. TAPI, or table based API, suggests building a simple API that performs the INSERT, UPDATE and DELETEs for each and every table and then using that TAPI in your APIs up a layer. My friend Daniel McGhan has even built a TAPI generator, tapiGen, which will create all the code for each table.

I'm pretty sure Tom Kyte is a fan as well. Unfortunately his site is down at the moment so I cannot link up to anything in particular. Hasn't he written some of the most predominant books on Oracle, specifically developing applications with Oracle?

I am still stunned that I heard that. I would expect it from Mr. M perhaps, but I think even he would appreciate the advantages of not having to write any SQL.

So how do you respond? What do you say?

The Computer Setup

I get to setup my new computer.

Here's a list of tools I'm installing:

SQLDeveloper, for those moments I wanted to be able to easily download data. Great database navigator as well. I create reports here and in JDeveloper as well.
JDeveloper, though I won't be using Subversion for work, I'm hoping there is a plugin for source control that I can use. Still my favorite editor of choice.
7zip
CubicExplorer, which is a tabbed windows explorer and fairly nice.
WinMerge, always nice to be able to easily compare files.
FastStone Capture - screen grab utility. You can also add arrows and stuff to your captured images.

Firefox was already installed so I imported my JSON file with my standard links (Oracle Documentation, Google Stuff, etc). I needed some FF Add-ons though:
Meebo, which I'm trying out for the first time.
ScribeFire, for the occasional quick post.
Shareaholic, for quickly sending links out.
Delicious, naturally.

Oracle Database was already installed along with BI Publisher, so I didn't have to worry about those. I'm sure I'll have others, but these are the basics.

La Revolución ha Muerto

For me anyway. I am moving along.

I will certainly miss all the friends I made and miss the opportunity to work with some very, very smart people. I wish I could have stayed, especially after the announcements yesterday (here, here, here, here, here, here, and here).

Bradd, this is not an April Fool's Joke. ;)

I guess the good news is that it wasn't involuntary. Will be added to the Tampa Timeline.

I start a new job on Friday.

Poor Man's Data Vault - Update

It's been a little over a month since I've written anything about it and seeing as how I have not posted anything Oracle related in what seems like years.

A quick reminder, Poor Man's Data Vault, PMDV for short, is a very basic tool to allow you lockdown your production environment. In essence, you are required to "submit" a ticket before any DDL is allowed in your specified schemas. When you "submit" or run a procedure you must supply a ticket number or description. Obviously there is no way to verify (yet) that this is an actual ticket, but it is a start. The ideal in the regard would be to query your ticketing/bug tracking database to validate. Currently we have a pretty sweet integration of Subversion, Fisheye, Bamboo and Jira. If we add the ticket number to the Subversion comments, we can then see (via Fisheye) what code is attached and even better do a diff right in the browser.

So, using Jira would be nice, but it's out of scope at this time.

Here's the final model I came up with:



PMDV_WORK is the driving table. It stores the records on the deployment or fix. PMDV_INVALID_OBJECTS takes a snapshot before, during and after the deployment of objects in an INVALID state. PMDV_CHANGED_OBJECTS will capture all the objects that were affected by the deployment (new and altered).

I'm starting with those 3 (because I forgot what I was going to do with PMDV_PRIVILEGES). I have created those 3 tables and checked them into source control (Google Code). You can find the project here. If you would like to join the "project" (yes, it's in quotes because I haven't done a whole lot but would love to actually finish one. Besides, I need all the help I can get.) drop me a line chet at oraclenerd dot com (does that really work? spelling out the email address I mean).