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.

blog comments powered by Disqus