Some additional thoughts
Stew Ashton, May 31, 2008 - 3:54 am UTC
Based on a few years' experience trying to do just what Tom says here:
2) PL/SQL will only "cache transparently" the cursors it uses, not the cursors java uses to call PL/SQL. So the java code still has to pool connections and cache prepared statements correctly.
3) The java code still has to handle some exceptions (in case of RAC failover for example), still has to translate the result set into usable objects and, as the final "client", has to decide when to commit.
To me, the ideal SQL developer would do exactly what Tom says, and in addition would deliver what J2EE calls the "data accessor". People who "consider the database a nuisance" shouldn't even be allowed to use the JDBC driver.
Also, both the data accessor and the database code should be instrumented. Don't depend on the GUI people for that.
Excellent advise
Sreenivas K, June 02, 2008 - 11:01 am UTC
Thank you Tom for sharing the expertise. This gives us the right direction.
Tom's above answer is for another review
Nico, June 02, 2008 - 12:39 pm UTC
June 02, 2008 - 12:49 pm UTC
thanks, fixed that.
Possible problem
Candy Peck, June 02, 2008 - 2:49 pm UTC
We have our OLTP system like you are describing. There is one problem. When you need to replace a package/procedure or change a table in the database it will make packages/procedures go invalid. This means you are not a 7X24 shop. When they get packages/procedure really intermingled it can take a while to deploy when they need something replaced. Java code not in the database will not go invalid.
June 02, 2008 - 3:02 pm UTC
well... lets see....
if you do not use stored procedures, but only PACKAGES, you break the dependency chain.
so, you can recompile a package BODY without invalidating anything else (eg: you can fix bugs)
And everyone immediately gets the new code (you now know everyone is running the same stuff)
If you do this with java - how do you roll it out to everyone at the same time.
And changes like this would be scheduled for a maintenance window - they would happen so infrequently.
You cannot run code that is being compiled, this is true - true of java (when you deploy, you will have an outage there - at that point), that is true of c, of VB, of any language pretty much.
And if you say "but we redeploy code like every 2 hours", well - then you already have a bigger problem than a second of unavailability.
(and in 11g, you don't need packages to break the dependency chain - it would be OK to use a procedure (but still wrong, use packages), and in 11g you can even compile package specifications without cascading invalidations)
and show me a hard core java developer that also knows their way around the database - transactions, concurrency controls, all about SQL... (really - I'd like to meet them, they are very rare indeed)
It is possible for Java developers to be database savvy...
A friend, June 03, 2008 - 9:30 am UTC
"and show me a hard core java developer that also knows their way around the database - transactions, concurrency controls, all about SQL... (really - I'd like to meet them, they are very rare indeed)"
There are a few of us about...the real trick is finding these chaps who aren't being constantly thwarted by supposed "architects" who are utterly terrified by developers having the temerity to use the database as it's intended to be used.
My favourite excuse from the previous lot is that they want to avoid "vendor lock-in" so they prohibited the use of any Oracle-specific extensions...even though the application scaled well past the capabilities of every other vendor's DBMS and we were paying a king's fortune for the privilege of running the only DBMS that could support our needs. The likelihood the bean counters would support another re-write of a mature application was pretty much nil which made their argument even more specious!
My current lot are perfectly fine with using Oracle features in queries but for the most part don't use bind variables and are pathologically opposed to procedures and packages because a combination of nitwit "DBAs" and a heavily manual release process has consistently ensured that deployment of some procedures would probably be missed and the schema wouldn't be recompiled before opening up the database to the users.
About all one can do is marvel that these "architects" and "DBAs" are still allowed to keep their jobs and keep trying to suggest the optimal use of Oracle...and be guerrilla subversive when we can get away with it!
On "vendor lock-in"
Stew Ashton, June 06, 2008 - 5:15 am UTC
This millenium, I have experienced one case where an application was totally rewritten: all the Java code was replaced by PHP, and the data remained unchanged - in Oracle.
There are all kinds of "lock-ins", not just "vendor lock-ins". Any kind of monopoly is a lock-in. That's why I consider the arrival of PHP in my shop as a wake-up call for the "java lock-ins".
Nothing new here: my first boss was already complaining about "people who have their minds safety-wired in the closed position".
The "vendor lock-in" syndrome highlights the fundamental issue :
-- When a developer accesses data, should he know the database or should he treat it as a "black box"?
Here's what I say to the "black box" proponents :
If you put your SQL in java, you have to know just as much about how Oracle works as if you were writing PL/SQL.
If you refuse all PL/SQL, what about data loading and batches in general? Kind of stupid to massage huge chunks of data on the middle tier, right? So you have to embed some SQL in PL/SQL anyway, which means (obviously) you have to learn how to do it right.
So, now that you're "locked in" anyway, what's the big deal about using stored procedures for OLTP?
Now, suppose a miracle happens and a project actually has an SQL Developer. He can't treat the middle tier as a "black box" either! He should provide an interface to the database from within the middle tier and implement proper access to the database, not just to the data.