1

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?
Feillen
  • 191
  • 2
  • 4
  • 15
  • 2
    Business Logic - PL/SQL Vs Java:AskTom – atokpas Oct 03 '17 at 09:22
  • 3
    Another view by Toon Koppelaars. With experimental evidence. https://www.youtube.com/watch?v=8jiJDflpw4Y. Highly recommended. – BriteSponge Oct 03 '17 at 09:40
  • @JSapkota excellent link. Thanks for that (and makes the slap option the most appealing haha) – Feillen Oct 03 '17 at 09:49
  • @BriteSponge I will watch the video, but since it's 50 mins long my answer to it will take at least that much time :D – Feillen Oct 03 '17 at 09: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 03 '17 at 11:04
  • @AndriyM I very well could, but I would be delegating some of the Business Logic statements (find matches between records in different tables) onto the DB through the Java Application, and that would be just adding extra steps to the scenario they asked to avoid (running Business Logic outside of the database). Maybe I didn't understand your point? – Feillen Oct 03 '17 at 11:17
  • @BriteSponge the video is certainly enlightening. While I was thinking about the whole thing I considered many of the factors that would make the nopsql approach simply worse, but knowing there is a whole study made and published by Oracle with such an in-depth analysis is absolutely marvelous. Thanks a lot good sir :-) – Feillen Oct 03 '17 at 11:19
  • Sounds like you could save your team/company a lot of money! If all you are doing is using the database as a bitbucket, then why not suggest using SQLite? Or even flat files? Bye-bye Oracle licence fees! :-) – Vérace Oct 03 '17 at 11:43
  • @Vérace unfortunately, this project will cohabitate with some others so SQLite concurrency limitations would pose a problem :( Thanks for the comment anyway! It is always good to refresh one's knowledge :D – Feillen Oct 03 '17 at 12:00
  • If possible, try to get information on internal history (or on the experiences of the person dictating these rules). If an organization ran into a very bad situation in the past, it's not uncommon for them to learn the wrong lessons (e.g., horrible performance issues due to bad PLSQL programming/allowing work that should have been done in dev to be done in production = avoid PLSQL programming). Not necessarily the case, but it's possible. – RDFozz Oct 03 '17 at 15:49
  • @RDFozz I can imagine that would likely have been the case. I'll try to look into that and show them a change for the better :) – Feillen Oct 03 '17 at 16:29
  • 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 03 '17 at 16:33

1 Answers1

0

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.

sandman
  • 489
  • 2
  • 11
  • 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 03 '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 03 '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 03 '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 03 '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 03 '17 at 14:41