And Java Justice For All [closed]

Question

Recently, I thought I was gettint into a Oracle Database Administration and Development project, with a heavy load of plsql programming in the form of stored procedures and such.

I was struck when, the other day, I was told I shouldn't rely so much on plsql as it can lead to undesired behavior on Production Systems, and that it would be ideal to perform all the necessary transformations on the data outside of the database, and use the database more as a repository than anything else (only insert intoand select should be used. Just store data, don't operate with it in any other way).

Not only that, but we were asked to do it all in Java (the face I made at that moment...). Since then I have cracked my head trying to figure out an easy way of satisfying these requirements and the best idea I could come of is trying to replicate a relational engine in a Java project. I know there are plenty of frameworks and libraries, like hibernate, that would somehow help me with the first steps of this project, but as you might have assumed, I could only use these to retrieve data from the database, not to pass to the db the queries that should be performed to make all necessary transformations (that would only be adding extra steps to the first, forbidden, scenario). I would still need to replicate operations like joins, unions, etc.

Here are my cries for help:

1. Is it true that plsql can lead to undesired behavior in Production Systems? (Oracle database)
2. Is replicating the work of the relational engine a sensible idea?
3. Are there any tools that can really help in a situation like this?
4. Should I just slap the genius behind this whole idea back to his senses?

Answer 1

For the same reason a carpenter does not use one tool for his job, companies should not rely on one tool for development of systems. There is nothing wrong with PL/SQL when used for the correct jobs. We use it for ETL and reporting on our warehouses, together with other BI tools. We also use C++ if we need to manipulate huge amounts of data in memory (on the server) and then persist changes or write the results as text or html reports.

For OLTP (you say "production") we use java together with frameworks such as Spring and Hibernate (and JDBC) to make communication with Oracle easier. You should then investigate your middle tier because you need efficient connection pooling for production - we find that Weblogic is stable for this. Your architect is correct to mention inserts and simple selects if your OLTP consists of mainly transactional tables, but nowadays "OLTP" systems are complex and hybrid and Oracle has to manage a wide range of DML, concurrency and locking strategies. If you have the luxury of designing OLTP from scratch, keep it simple and clean.

You can find a full list of development tools here.