Stored procedures and portability
It appears that some people are concerned about portability of the ref cursors I described earlier. I wish this would also trouble me, but the reality is that the project I work on these days uses Oracle and it’s not going to use anything else anytime soon. We are building a java web application on top of Oracle Applications 11i (CRM module). I don’t think Oracle is going to port this to mysql or db2. So for this project I don’t really care about portability.
And the fact is, I want to know all the special features Oracle includes in their
database, as it gives me a better understanding of when to use Oracle or something else, for example PostgreSQL. Some features are really usefull.
At home I use PostgreSQL to try things out. I have a pentium III – 500, and it’s no fun to run Oracle on that. PostgreSQL is a nice replacement. And it supports ref cursors btw. I haven’t tried it, so I don’t know how good the postgresql jdbc drivers support this.
I don’t have any experience with other databases, so I don’t know how well stored procedures and ref cursors are supported on other database. Anybody?
Fred asked whether stored procedures shouldn’t be avoided to get platform/vendor independence. Maybe. But the reality is that databases often live longer than programming languages. Most companies in the netherlands using oracle databases write their applications in Oracle Forms, before that in c and c++. Today they are looking at java, and in the future probably .net. So portability to them means that one database should be able to port to different languages, not that one language should be able to port to multiple databases. I think it improves usability a lot if you put all your business rules as much as possible in the database, by using stored procedures and packages. That way you can easily reuse them when you switch to a different programming language. And in this situation I think stored procedures and ref cursors really do make sense.
March 28th, 2003 at 10:05 am
Hi Andrej,
using Stored Procedures doesn’t pose a portability problem in itself. Every serious database offers them one way or another.
What poses a portability problem is the way Oracle handles ref cursors.
JDBC has a predefined manner of doing this, namely treating a call to stored procedure like a select-statement. i.e. the ResultSet is retrieved as an implicit parameter. Looking at the specs, you will find, that even batch calls with multiple ResultSets are possible.
The obvious Right Thing To Do™ is to write your own CallableStatement, that encapsules Oracle’s aberration. Doing so you don’t have to differentiate between Oracle, DB2, Postgres etc. in the Java part of your application, promoting portability even further by hiding the SQL-dialects from the application.
There is also another major drawback to Oracle’s method of retrieving ResultSets from Stored Procs: It is almost impossible to use them as a means for CMP.
If you have an AppServer, that allows you to write your own select-statements for the finder-methods, you can easily replace them with calls to Stored Procs. The ResultSet would be retrieved as the return value of the statement’s executeQuery-method. Now try to do that with Oracle! You would probably end up writing a proxy to Oracle’s JDBC-driver.
Best regards
Fokko