Saturday, April 11, 2009


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?


Boneist said...


I'd come back and say, "Ok; in C# (or other object-oriented language of your choice), you're not allowed to use Classes. And you've got to type with both hands tied behind your back."

*rolls eyes*

John T said...

The whole argument is asinine and displays a fundamental ignorance of software development in general.

I think most would agree hiding the SQL is a good thing, but why?

Well, there are a couple principles we can steel from our OO brethren.

One is Design by Contract. Loosely speaking, a package spec is a programing contract. To the client is says you provide me with a, b, and c; and then I will do blah, and return you x, y, and z.

The second (and even more fundamental) is encapsulation. Wikipedia has definition here. I could almost insert the whole page as a response here! LOL

My statements here defend the concept of using some type of API in general. I could understand a negative experience if the API was poorly designed. Its not that uncommon to see a good programming practice executed badly. But that's not the fault of the PL/SQL language. I'm sure a poorly written API could be created in Java, C#, or C++ just as easily.

oraclenerd said...

@boneist (snicker)

No classes huh? A class defines an object and a set of actions you can perform against said object. Yup. I think that about says it.

oraclenerd said...

@john t

Good points. I realized this morning that I had not really "defined" the API as I saw it in PL/SQL. But then most of the people reading this blog (big assumption) know and understand PL/SQL.

I like the Design by Contract link, I naturally didn't mention that either. This is a design construct (or contract) or overall design pattern. No other database user should have DML (INSERT/UPDATE/DELETE) privileges on any table in the application schema. Ever!

If I remember correctly, what prompted the note originally was the attempt to gain control over our system from a support and maintenance perspective. Any DML of a particular table should be part of a logical transaction and should occur via the API (and supposedly built by a subject matter expert, i.e. the creator of the table or set of tables).