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?
  • 2
  • 3
    Another view by Toon Koppelaars. With experimental evidence. youtube.com/watch?v=8jiJDflpw4Y. Highly recommended. Oct 3 '17 at 9:40
  • @JSapkota excellent link. Thanks for that (and makes the slap option the most appealing haha)
    – Feillen
    Oct 3 '17 at 9:49
  • 1
    ‘I would still need to replicate operations like joins, unions, etc.’ — but why? If SELECT statements are allowed, can't you have a SELECT with a join or union (or both if necessary)?
    – Andriy M
    Oct 3 '17 at 11:04
  • 1
    I reread your post - (sound of jaw dropping...) - read my post here and maybe you can convince them to trust your database. MILLIONS of people run, use and test Oracle software - you're going to have to essentially rewrite what you're getting for free with the Oracle engine, (and test and debug it - sheer madness and a colossal waste of the company's MONEY as well as turning you into a gibbering wreck! :-)
    – Vérace
    Oct 3 '17 at 16:33

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.

  • I can understand the analogy, but after having watched the video provided by @BriteSponge I cannot help but think "Why would a carpenter ever use a toothbrush instead of a sanding machine?"
    – Feillen
    Oct 3 '17 at 11:23
  • It depends on the job. He would not use a sanding machine for delicate jobs like restoring antique furniture. Analogy apart, I hope you find my technical commentary useful.
    – sandman
    Oct 3 '17 at 12:10
  • It somewhat helps, but I cannot infer under which circumstances you would make us of a NoPsql approach and use some framework instead :( [sorry for the last edits on this comment]
    – Feillen
    Oct 3 '17 at 14:33
  • And yet you are given the task of DBA and development work in java, which is the job of two people... good luck, you will need to upgrade your lucky charms, knowledge and energy levels...
    – sandman
    Oct 3 '17 at 14:36
  • Untill this monday it was only a DBA job, it struck me like a truck when I saw their intentions on this project...
    – Feillen
    Oct 3 '17 at 14:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.